Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
Azure Sentinel Workbooks 101 (with sample Workbook)
Published Jul 16 2020 02:27 PM 51.8K Views
Microsoft

Within the world of security operations, dashboards and visual representation of data, trends, and anomalies are essential for day to day work. In Azure Sentinel, Workbooks contain a large pool of possibilities for usage, ranging from simple data presentation, to complex graphing and investigative maps for resources. Out of the box, Sentinel already comes with dozens of Workbooks. It also allows for custom workbooks to be created based on the user’s vision and use case. The purpose of this blog is to provide examples and describe some of the more advanced uses for Workbooks in Sentinel. We have also created a sample Workbook that can be accessed here that can be used to follow along.

 

If you would like to watch a presentation on the uses of Workbooks, you can check out our Security Community webinar on this topic here. 

 

Pre-requisites:

  • Azure Sentinel Contributor permissions
  • Azure Workbooks Contributor permissions
  • Available data in your Azure Sentinel/Log Analytics workspace

Before we can dive into the advanced topics, it is important to recap the basics.

  • Text – simple text or comments on the workbook
  • Grids – a row by row view of data
  • Graphs – visual representation of data in comparison
  • Time Charts – visual representation of data over time

 Advanced

  • Power BI – move data to Power BI for dashboarding
  • Tabs – separate data by topic per tab
  • Groups – grouping of tiles by topic
  • Time Brushing – selecting a window of time for logs
  • Hives – visual grouping of data into hive shapes
  • Dynamic Content – enabling tiles to inherit variables based on other tiles
  • Personalization - modify results in the Workbook to standout or be presented differently

Text

Matt_Lowe_0-1594753991897.png

 

Text within a workbook is a simple section where text can be added to describe data, leave comments, instructions, and more. The purpose of this is to allow for user input to be listed on the workbook. Text can be used to help maximize the effectiveness of visuals by noting important areas to check, procedures to follow, or items to keep an eye out for. An example would be adding a note in text near a time chart to watch for over 100 failed login attempts.

 

To deploy text:

  • Go to your Workbook.
  • Hit ‘Add’ and choose 'Text'.
  • It will open a new section for text to be input.
  • The font size can be modified to show titles, notes, and descriptions.
  • Click 'Done' when finished.

 

Parameters

Changing the parameter value will change the range for all items that are configured to use the value.Changing the parameter value will change the range for all items that are configured to use the value.

Parameters allow for the selection of values that will be applicable to the whole Workbook. This can be used for time ranges, subscriptions, workspaces, filtering, and more. The parameters are presented as a drop-down list which can be placed at the top of the Workbook or just above graphs. Each selection can provide impact on which data is presented or how it is queried.

 

To deploy parameters:

  • Click 'Add' and choose 'Parameter'.
  • Give the parameter a name.
  • Click 'Edit' and choose 'Settings'.
  • Within Settings, the parameter type can be chosen and the value options for selection can be set.
  • Click 'Save' when finished.

*Note: If the parameter has a '!' by it, the value has not been set and needs to be done.

Parameter creation.gif

 

Grids

Grid.gif

Grids are where logs and other data items are listed in a rowed fashion. This is where data that is queried is listed. This data is what can be transformed into graphs, time charts, hives, and more. Each grid is made up of a Kusto query that runs when the Workbook is accessed. The queries can range in time, data tables, etc.

 

To deploy grids:

  • Go to ‘Add’ and choose 'Query'.
  • Enter a query for the data that you would like to pull.
  • The results will get capped at 250 so if you do not want too much, make sure to use the ‘take’ operator to limit the amount of rows you want returned.

 

Graphs and Charts

Graph.gif

Graphs are a type of visual representation for data in Workbooks. These can vary between pie graphs to bar graphs. This is how data is visualized to show trends, comparisons, and more. These visuals can assist with finding potential malicious events, unhealthy trends, or outliers in performance.

 

To deploy graphs:

  • Go to ‘Add’ and choose 'Query".
  • Enter a query and make sure that there is a summarize count() or count() that is used within the query. The data cannot be put into a graph format if there are not numeric values for the subjects in the data.
  • Use either the 'render' operator or choose 'Visual' in the query settings within the Workbook to choose the graph that it will be displayed as.

 

 

 

 

 

 

 

*Example query*

SecurityAlert
| where TimeGenerated >= ago(30d)
| summarize count() by ProviderName
| render barchart 

 

 

 

 

 

 

graphing.gif

 

Time Charts

Time chart.gif

Time charts are similar to line graphs but lay out more information and focus more on a time frame of information. This ties into tracking anomalies, unhealthy trends, and more. This also ties into time brushing in the advanced section. Similar to regular graphs, the query option must be chosen. This time around, the query will need a ‘bin’ operator. The bin operator will take a variable and a time scale value and create a series based on the data.

 

An example would be ‘summarize count() by ProviderName, bin(TimeGenerated, 1d)'. This is taking a count of ProviderName from the query results and generating a time series that will show the amount of results per day.

 

 

 

 

 

 

 

SecurityAlert
| where TimeGenerated >= ago(30d)
| summarize count() by ProviderName, bin(TimeGenerated, 1d)
| render timechart 

 

 

 

 

 

 

Tabs

tabs.gif

Tabs are headers within the Workbook that can be selected in order to change what is being presented on the page. This is very useful when making a Workbook that might cover several topics or if there is a large amount of information to present.

 

To deploy a tab:

  • At the top of the page click 'Add' and choose 'Tabs'. Each title will need to be made.
  • Give the new item a title and choose actions - 'Set a parameter value'.
  • Set the value to 'Tab' and give the tab a value that identifies what it is for.
  • If you would like certain tiles to be mapped to specific tabs, you must go to ‘Advanced Settings’ on the item and enable 'Make this item conditionally visible'.
  • Set the value to be 'Tab equals (the value of the tab you set)'. The item will no longer show on other tabs until the proper tab has been chosen.

 

Groups

Groups allow users to set tiles, graphs, and other data into collections based on topic, format, and more. The best use for groups is distinguishing data types or topics from each other and separating them. This can be maximized by using tabs to separate each group into different tabs.

 

To deploy a group:

  • Go to 'Add' and choose 'Groups'.
  • Groups can have countless tiles and items added to it. If you would like to add existing items to a group, choose 'Move' and choose the group you want to move the items to.
  • If you would like the group of items to show up under certain tabs, add a condition stating that it will only show if a certain value is chosen.

 

Time Brushing

time brush.gif

Time brushing is the ability to click and drag on a time chart to set a time window that should be investigated. By using time brushing, tiles and logs that follow the time chart can inherit the time range chosen to narrow down associated information. 

 

To set up time brushing:

  • 'Enable time range brushing' must be enabled under the advanced settings of the time chart.
  • From there, the time range will need to be changed to the new time variable you created in the previous step.
  • Once set, click and drag on the time chart to change the range.
  • For items within the Workbook to inherit the new time range value, change the time to be the value that you created in the time chart.

 

Hives

hives.gif

Hives utilize a new visual feature that is in preview within Workbooks. Hives allow you to use a graphical interface that can be moved or modified while presenting data in a compact, hive layout. This new graphing feature, outside of hives, allows for a more interactive graphing/mapping functionality.

 

To deploy hives: 

  • Click 'Add'.
  • Choose 'Query'.
  • Enter your query.
  • Under visualize, choose 'Graph'.
  • Choose 'Graph Settings'.
  • Under layout settings, choose 'Hive Clusters'.
  • Set your remaining settings for size and color.
  • Click 'Save and Close'.

 

Dynamic Content

The content will not appear until a resource is chosen.The content will not appear until a resource is chosen.

Dynamic content allows you to export a selected variable to other parts of the Workbook. An example of this is selecting one machine from a list of machines and the other logs and charts throughout the Workbook now pertain to data for only that one machine. This is useful for narrowing down potentially compromised machines or machines of interest for anomalies.

 

To configure dynamic content:

  • Set up a grid that contains results for which you would like to focus on.
  • In the advanced settings for the grid, select the option 'When items are selected, export parameters'.
  • Give the item a name.
  • Make sure to establish the item in the query that you are running so that it has a value for exporting.
  • Set up a second grid or object that you would like to inherit the value from the selected resource in the first grid.
  • Establish a variable to inherit the value from the item.
  • Use the 'dynamic' operator to call the item you established in the first grid as this is how the second grid will see the exported value of the item.
  • Establish a clause in the query in the second grid to limit the results to the information that is tied to the variable with the inherited value.

 

 

 

 

 

 

 

*Set up the variable to take on a value*

SecurityAlert
| extend Resource = ResourceId
| summarize count() by Resource
| sort by count_ desc

*Set up a variable to inherit the exported value of the selected object*

let Resource_ = dynamic({Resource});
SecurityAlert
| where ResourceId contains tostring(Resource_)
| project TimeGenerated, Resource_, AlertName, AlertSeverity, ProductName

 

 

 

 

 

 

 

DC setup.gif

 

Personalization:

Personalization allows users to modify the results and look of grids and charts to suit their use cases, as well as improve the Workbook experience. An example of a Workbook personalization would be to add color coding for severity of alerts in grids or charts (i.e. red for high severity, green for low severity), or changing a URL link from text to being a clickable URL.

 

To personalize a Workbook:

  • Find a grid or chart that you would like to modify.
  • Click 'Edit'.
  • Go to 'Column Settings'.
  • Look over all of the settings to see what there is to change and test how it will look.
  • Click 'Save'.

personalization.gif

 

Power BI

An alternative to using Azure Sentinel workbooks is to use Power BI. This is Microsoft service that allows you to export queries and results from Log Analytics to Power BI for reporting purposes. You may already be using Power BI for reporting in other parts of your business, as it supports reporting from a wide number of sources.

 

To use Power BI, it must be done from the Log Analytics workspace:

  • Choose a query that would like to export and run it.
  • In the top right, choose 'Export' and choose 'Export to Power BI (m query)'.
  • A file will be generated for Power BI, use the query in the file in Power BI for reporting in the Power BI portal.
  • Within the Power BI portal, choose 'Get Data' and select 'Blank Query'.
  • Select 'Advanced Editor'.
  • Paste the query from the text file in the editor.
  • Publish to Power BI.

image.png

 

image.png

image.png

image.png

 

What’s next?

We have prepared a sample Workbook that displays each item that was covered in this blog. The purpose of this Workbook is to assist users in seeing examples of each item, how they are configured, and how they operate. The goal is for users to use this Workbook to learn and practice advanced topics with Workbooks that will contribute to new custom Workbooks.

 

To deploy the template:

  • Access the template in GitHub.
  • Go to the Azure Portal.
  • Go to Azure Sentinel.
  • Go to Workbooks.
  • Click 'Add new'.
  • Click 'Edit'.
  • Go to the advanced editor.
  • Paste the template code.
  • Click 'Apply'.

 

15 Comments
Brass Contributor

@Matt_Lowe 

Thanks for the article.

I've tried to deploy parameters, but the query always fails to identify the values passed in the parameter to the query, with error 

"Failed to resolve column or scalar expression named 'testvalue' ..

where testvalue is the value passed via parameter

Are you aware of such issue? Is there a limitation or special consideration to how a parameter can be invoked within the workbook KQL query ?

Microsoft

@majo01 

 

Hi there,

 

Can you provide a screenshot so that I can see what is going on? This is the first time the issue has been brought up.

Copper Contributor

@Matt_Lowe 
Wondering if you might be able to give me a few pointers here as I'me new to workbooks and struggling with something seemingly simple.
Why when I copy a working query from editor into a Sentinel workbook does it utterly refuse to display one of my series?

In Grid view you have the New series but in any rendered form there is no visibility of that New series 

Col_Sanders_2-1600273411936.png

 

Col_Sanders_1-1600273286335.png

 

 

 

Microsoft

@Col_Sanders 

 

If I am understanding this correctly, you are attempting to take a query that presents results in grid and make it into a visualization? What is the end result that you are expecting it to be? 

 

Once I have that info I should be able to assist more.

Copper Contributor

@Matt_Lowe Sorry for the slow response, just been focused elsewhere due to deadlines.
In my post above, you see in the grid there are three columns, "New" "Fixed" and "ReOpened" for each IP address.

When I try to get all three columns to display in the chart visualisation, "New" will never display :(

If I try and project "New" by itself it tells me "Could not find appropriate columns for Bar chart."
Ideally what I set out to achieve was to have as many vertically aligned IP addresses as was readable along the bottom (x) axis each with stacked bars above showing "New", "Fixed", "Active" & "ReOpened" for each IP address for the 'nn' top-hitters and the total number of tickets on the vertical (y) axis

QualysHostDetection_CL
| mv-expand todynamic(Detections_s)
| extend Status = tostring(Detections_s.Status)
| top 1500 by IPAddress 
| where isnotempty(IPAddress) 
| summarize  ['New'] = countif(Status == "New"), ['Fixed'] = countif(Status == "Fixed"), ['ReOpened'] = countif(Status == "Re-Opened"), count() by bin(TimeGenerated,30d), IPAddress
| project IPAddress, New, Fixed, ReOpened
Copper Contributor

Can we increase the limit of the items? I am unable to select more than 100. 

kalmanje_0-1612602719355.png

 

Iron Contributor

Is there a way to create a chart like this (from security.microsoft.com) in workbooks?

 

bobsyouruncle_1-1619035800587.png

 

Microsoft

@SocInABox 

 

The closest we can get to that view would be to create a grid that contains a column that contains a count of a topic you would like, so in this case the tactics. We can then make the column show a colored line for the count. 

 

It would appear similar to this:

 

Matt_Lowe_0-1619038295123.png

 

Take care

Iron Contributor

Thanks Matt! I'll give that shot.

Is that done in the chart settings or is there something special in the query for that?

 

Microsoft

@SocInABox 

 

You would need to do a summarize count() by Tactic in the query basically then go into the column settings to personalize the column with the count in it. The visualization is just a grid.

 

Let me know if that helps.

Iron Contributor

Thanks @Matt_Lowe ! hadn't used the personalize settings in that way that's what I needed.

MUCH appreciated!!!!!!!

 

Iron Contributor

Well I keep coming up with more questions...
The chart I showed above consistently will show the 10 'tactic' categories, regardless of whether the count for each is 0 or higher.

What does the kql look like for that?

I'm looking at this string: 

|extend Category = tostring(parse_json(ExtendedProperties).["MicrosoftDefenderAtp.Category"])
And I'm trying to give a count for each unique category, whether it has been seen zero or more times.
Discovery =10, Execution=0, Initial Access = 5, etc etc.
Iron Contributor

Hi kql gods,

 

I have a seemingly simple question please, and yet it has stumped at least 2 of my colleagues so I've come to you.

 

What's the best way to deal with this sort of logic:

| NOT where (key1==val1 and key2==val2 and key3==val3)

 

I appreciate you can say NOT in front of where but you get the point.

 

I tried:

|where (key1 != val1 and key2 !=val2 and key3 != val3)

 

But the result did not perform the expected AND logic.

 

I expected it to filter out only when all 3 keys were matched but instead if filtered on EACH key like an OR condition?

 

Your help is much appreciated.

Copper Contributor

Hi All, @Matt_Lowe 

I am wondering if anybody can help out?

Goal/Objective:

  • I want the USER of a Microsoft Workbook to be able to easily SELECT which columns in a workbook table they want to display
  • Ideally this will be achieved by the user SELECTING options from a drop down list (as opposed to modifying any underlying KQL).

Proposed Method:

  • In order to try and implement this- I have defined a workbook global parameter called [columns_to_project]. 
  • This parameter contains a drop-down list of ALL of the available columns in my final table.
  • I am then hoping to only PROJECT the columns SELECTED by the user in this parameter

 

Screenshot 1: The new parameter I have created- in order to allow the user to SELECT which columns they want to display.

 

ryan_oleary_0-1663862321685.png

 

I have then created the following test table:

Screenshot 2: What the table should look like if all columns are displayed

 

ryan_oleary_1-1663863738372.png

 

 

Issues Encountered


Error 1: Attempted SYNTAX: | project {PARAMETER NAME}

 


Note: The Column names are included as strings in the final data- as opposed to the actual data itself

ryan_oleary_3-1663864126601.png

 

Error 2: Attempted SYNTAX: | project {PARAMATERNAME:label}
Result: The query fails

 

ryan_oleary_4-1663864243941.png

 

Error 3: Attempted SYNTAX: let variable_name=dynamic({PARAMATER NAME}); .. | Project variable_name

Result: Query fails

 

ryan_oleary_5-1663864406460.png

 

---------------------------------------------

 

Any advice on how to fix my existing solution? Or is there any alternative way to allow the user of a workbook to choose which columns to display in a table (...without them having to modify the KQL directly?)

Thanks in advance,

Ryan

 

Copper Contributor

There is a change on this:

*Set up the variable to take on a value*
SecurityAlert
| extend Resource = ResourceId
| summarize count() by Resource
| sort by count_ desc
*Set up a variable to inherit the exported value of the selected object*
let Resource_ = dynamic('{Resource}');
SecurityAlert
| where ResourceId contains tostring(Resource_)
| project TimeGenerated, Resource_, AlertName, AlertSeverity, ProductName

The curly brackets { } will need to be within quotation ' '

let Resource_ = dynamic('{Resource}');

 

Version history
Last update:
‎Nov 02 2021 06:06 PM
Updated by: