Advanced Workbook Concepts with Workbooks 202

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

With Workbooks 202, a sample workbook is provided in the same manner as Workbooks 101. This new workbook will contain examples and samples of each item covered in this document, as well as instructions on how to use the components. Workbooks 202 will cover:

1. Additional data sources
2. Interactive Graph Visualization
3. Linking
4. Additional advanced concepts

Please note, this blog is very long. If looking to break up the content, please use the workbook as the same content will exist in the workbook for each section. The workbook template can be found here and will be available soon in Microsoft Sentinel.

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

 

Oh, you are still here. Let's begin.

 

Additional Sources

While Azure Log Analytics is the main source of data in a workbook, there are several other options available.

 

Azure Resource Graph

Azure Resource Graph (ARG) is one valuable source of data for a workbook. ARG presents current information about resources and configurations within the Azure environment that are not recorded as logs within Azure Log Analytics. ARG uses KQL and can be used to grab information such as:

• Policy compliance
• Resource configurations
• Resource posture
• And more.

When it comes to utilizing ARG, it is most commonly used for fetching existing resources within the environment. This is used in combination with parameters in order to create items like drop down selectors of resources.

 

To use ARG in a workbook:

1. Click ‘add’ then choose ‘query’.
2. Under source, select ‘Azure Resource Graph’.
3. Begin entering a query that will yield results.
4. Modify the results to satisfaction.

An example query is:

 

resources | where type has 'microsoft.insights/workbooks' | extend DisplayName = properties.displayName

 

For more information on ARG, please refer to: Azure Workbooks data sources - Azure Monitor | Microsoft Learn.

 

Azure Resource Manager

 

Azure Resource Manager (ARM) operates like an API but does not require an authorization token. The source still uses a GET call and the URI is just the regular URI but without ‘https://management.azure.com/’. ARM is great to use when looking to gather information that is not available in a log but can be brought in via API. Some examples may be:

• Analytic rules in Microsoft Sentinel
• Roles assigned to users
• And other information that can be leveraged from Azure API.

The main differences between ARM and a custom endpoint are:

• ARM does not require an auth token to be generated as it uses the current user's token
• ARM does not require a full URI

The response when using ARM will be in JSON format, so the results will need to be parsed via JSONPath to appear in a log format. To format the response:

1. Click add and choose ‘query’.
2. Click on source and set it to Azure Resource Manager.
3. Go to the ‘results’ tab within the ARM query.
4. Enter the ARM API URI and make sure that the API version is included.
5. Go into ‘result settings’.
6. Set the option to ‘JSON Path’.
7. Set the format value to be ‘$.value’.
8. Within the lines below, specify the name of the column and the JSON path to the value.
   • Ex. Name -> Title, Path -> $.titleValue
   • Once configured, rerun the ARM call and the results will appear in a more log format.

For more information on parsing, please refer to https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-jsonpath. For more information on ARM as a source, please refer to https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-data-sources#azure-resource-manager.

 

Azure Data Explorer

 

Azure Data Explorer (ADX) is another option for data. This source type will take in the database and table names in order to route to the data. From there, KQL can be used to query the data. An example database is https://dataexplorer.azure.com/clusters/help/databases/Samples.

 

To use ADX as a source:

1. Click on ‘add’ and select query.
2. Under source, select Azure Data Explorer.
3. Enter the cluster name and database name.
4. Enter the KQL query to retrieve the data.

An example query is:

 

StormEvents | project column_ifexists("Capital", State) | summarize by State | order by State asc

 

For more information on ADX as a source, please refer to https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-data-sources#azure-data-explorer.

 

Merge

 

Merge queries allow for two or more queries to be merged together in a similar fashion as a join. The queries will be joined together via a shared column key. There are several types of merges:

• inner unique
• full inner/outer
• left inner/outer
• left semi/anti
• right inner/outer
• right semi/anti
• union

To use a merge query:

1. Create two queries that will be used in the merge.
2. Click ‘add’ and choose query for a third query.
3. Under sources, select merge.
4. Click ‘new merge’.
5. Select the first query and the column that will be used to join the second query.
6. Click ‘run query’.
7. The two queries will then be joined on the key. The returning columns can then be modified to be presented as desired.

For more information on merges, please refer to Azure Workbooks data sources - Azure Monitor | Microsoft Learn.

 

External

 

External locations can be pulled as a data source. The most common source will be from a website that is hosting data. The most common example is GitHub. Two options are available for pulling the data are via custom endpoint as a source or the externaldata operator.

• By leveraging the externaldata operator in KQL, websites like GitHub can be transformed into a data set. This requires that the schema is built within the query before calling the endpoint.
• By leveraging the custom endpoint as a source, the data is returned in JSON format. If using this method, the data will need to be parsed. This can be done either through JSONPath or through KQL.

As an example, we will reference a publicly hosted GitHub JSON file at https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/CEF/SymantecDLP.json and https://raw.githubusercontent.com/nsacyber/Event-Forwarding-Guidance/master/Events/RecommendedEvents.json.

 

For more information on the externaldata operator, please refer to https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/externaldata-operator?pivots=azuremonitor.

 

Custom endpoint as a source is highly valuable as it means the data being brought in does not need to be ingested into Azure Log Analytics and the data can be dynamic over time.

To configure an external source:

1. Click on ‘add’ and choose query.
2. Leave the source as Log Analytics.
3. Enter the externaldata query and define the expected schema from the data that will be pulled in.
4. Enter the data URL as the source.
5. Run the query and confirm that all of the data appears as needed.

An example of an externaldata query is:

 

externaldata (SourceSystem:string, TimeGenerated: datetime, ReceiptTime: datetime, DeviceVendor:string, DeviceProduct: string, DeviceEventClassID: string, LogSeverity: string, OriginalSeverity: string, DeviceAction:string, SimplifiedDeviceAction: string, Computer: string, CommunicationDirection: string, DeviceFacility:string, DestinationPort:string, Activity:string, AdditionalExtensions:string) [ @'https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/CEF/SymantecDLP.json' ] with (format="multijson")

 

Additionally, external sources can be brought in via API. This will utilize the full URI and require an auth token, unlike with ARM. The API call can be performed like any other API call where the version and token can be placed in the header of the call. If calling a URL that is just a file, no auth token will be needed. To perform this:

1. Click ‘add’ and choose ‘query’.
2. Under source, select ‘custom endpoint’.
3. Enter the URI, token (if needed), and API version.
4. Run the call.
5. If parsing via another query, follow the process and parse the response via KQL in the second query.

 

An example of parsing a JSON response is:

 

print events=todynamic({CECall:parsejson}) | extend events = events['Recommended Events to Collect']// First we pivot the data | mv-apply events on ( extend Category = tostring(bag_keys(events)[0]) | extend Category = tostring(bag_keys(events)[0]), description = events[Category].description, events = events[Category].events ) | mv-apply events on ( extend eventName = tostring(bag_keys(events)[0]) | extend eventId = tostring(bag_keys(events[eventName])[0]) | extend properties = events[eventName][eventId] | project-away events ) | order by eventId asc

 

For more information about custom endpoints, please refer to Azure Workbooks data sources - Azure Monitor | Microsoft Learn.

 

Visualizations

 

Graphs

Graphs are an advanced visualization option within workbooks that depict relations between items and directions of actions if built correctly. This visualization is challenging to create as it requires the user to build out the data, nodes, and relations within the KQL itself before configuring how to the graph will look.

 

Structure

The structure of the KQL can be summarized as:

• The data: represents which logs will be used and referred to when building the nodes and links. This can be a single table, or multiple tables that have been joined together.
• The nodes: represent the items of interest in the graph. These will be the items presented within the graph. These may be items such as accounts, devices, processes, or other trackable items.
• The links: represent a relation between the nodes. This is manually defined in the query by the user and reflected in the results. Relations can be scenarios such as authentications, processes started by a user, files uploaded by a user, communication between devices, and more.

 

Building the query utilizes several let statements in order to store results or components. First, it will need the data portion. This can be as simple as:

 

let data = SigninLogs | Account = split(UserDisplayName, '@')[0] | summarize by Account, UserDisplayName, IPAddress | join kind = leftouter SecurityEvent on Account;

 

Once the data is configured, it's time to set the links. This will involve defining what the link relations are, notated with a '->' to signal the direction of the relation. Links are essential for dictating the links between nodes. An example would look like:

 

let links = data | summarize by Source = Account, Target = Computer, Kind = 'Account -> Machine';

 

Once the links are made, it's time for nodes. This will involve defining what the nodes will be. Think of nodes as the plots on a chart in that they will be interconnected via a common data piece. This may look like:

 

let nodes = data | summarize by Id = Account, Name = Account, Kind = 'Account' | union (data | summarize by Id = Computer, Name = Computer, Kind = 'Machine');

 

 

Building

Once the query is done and the results are in, it’s time to configure the graph. To do so:

1. Set the visualization to ‘graph’.
2. Click on ‘graph settings’.
3. If data should be presented in the nodes, set which values and their location at the top of the settings menu.
4. Under layout settings, apply the following mapping:
   1. Node ID = Id
   2. Source ID = Source
   3. Target ID = Target

 

For more information on graphs, please refer to Azure Workbooks graph visualizations - Azure Monitor | Microsoft Learn.

 

Linking

 

Linking other blades and views within Azure is a highly valuable feature within workbooks. This allows users to open other blades and items within the Azure portal without having to leave the workbook. This allows users to do things such as:

• Pivot to their workspace to run queries directly
• Open resources to review settings
• Open blades of Microsoft Sentinel to configure items
• Open Defender for Cloud to review information
• And much more.

The resource can be set dynamically or statically. If desired, resource paths can be inserted manually to make the value static. If looking to switch between different resources, values can be set dynamically via Azure Resource Graph.

 

Workbooks: Workbooks can be linked within another workbook. This allows users to tie two or more workbooks together if they are related. There are two ways to do so:

• Opening the template within the workbook
• Opening the workbook in a context pane

Azure Blades: Blades within the Azure Portal can be linked. This item is a bit more advanced as it involves using developer tools in the web browser. This method allows for blades within Azure to be linked, allowing users to pivot to another part of the portal without having to leave the workbook. This also allows for key blades from several Azure services to be linked in one location for ease of use.

 

External Locations: This method allows for external links to be tied to a button. This allows for key web locations or items to be statically linked within the workbook. While a URL is static, values of the link can be set dynamically via parameters in the workbook.

Ex. https://www.virustotal.com/gui/file/ENTERDYNAMICPARAMETERHERE

 

For more information on linking, please see https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-link-actions.

 

Azure Resources

If looking to link an Azure resource, the best way to do so is by leveraging parameters with ARG. The purpose of using these two items is to locate the resource via ARG, then save the resource path via a parameter. This allows for the button linking to the resource to be dynamic. To make this:

1. Click ‘add’ and choose ‘parameter’.
2. Set the type to ‘drop down’.
3. Set the value to ‘query’.
4. Under source, select ‘Azure Resource Graph’.
5. Enter the ARG query and project the id and name of the resources.
6. Click ‘add’ and choose links/tabs.
7. Give the link a name and set the action to resource overview.
8. For value, set the value to the parameter name that contains the ARG results.
9. If opening a submenu of the resource, enter the menu name under settings.

 

Workbooks

Linking workbooks is powerful in that it combines one or more workbooks with the current one that is being used. This can create a chain of functionality and use cases in one convenient location.

 

Methods

There are two main methods:

• Linking as an Azure Resource
• Linking a template if hosted elsewhere

Both options are viable options for linking a workbook. Linking workbooks dynamically via the Azure resource method is a little easier while also allowing users to avoid having to pass parameters in the configuration. The workbook (template) action is great to use when statically linking workbooks while providing better performance.

 

Azure Resource Based

To link via an Azure resource:

1. Follow the steps listed above for dynamic values (parameters, Azure Resource Graph, button.)
2. Make sure the value is set to the dynamic value for the workbook.
3. Under settings, make sure the value is set to 'workbook'.

 

 

 

Workbook Templates

The Workbook (Template) action allows for workbooks to be linked in a workbook by directing the current workbook to where the template can be found for the other workbook of interest. This action contains several values:

• Owner: Value is the resource ID of the Azure resource that 'owns' the workbook. This is will most commonly be 'Sentinel' or 'Azure Monitor'.
• Workbook resources: Array of Azure resource IDs that specify the default resource used in the workbook. An example would be if the workbook being opened shows data on virtual machines, the value entered would be the virtual machine resource IDs.
• Template ID: Specifies the ID of the template to be opened. Can be a value that indicates the source, such as 'Community-Workbooks/Performance/Apdex' if using a workbook from the community gallery. If using a link to a saved workbook, use the full Azure resource ID.
• Workbook type: Specifies the kind of workbook template to open. Default or workbook is most commonly used values.
• Gallery type: Specifies the gallery type that is displayed in the gallery view. Default or workbook is the most commonly used values.
• Location: Specifies if a specfic workbook resource is being opened. If location is not specified, finding the workbook will be slower. If the location is not known, leave the value as default.
• Specifc Parameters: Allows specific parameters to be passed to the workbook to be opened. Parameter names must be the same between the workbooks.
• Workbook template parameters: Specifies the parameters that are passed to the target template. Name and value must not be empty to pass the parameter to the target template.

To link via the Workbook (Template) action:

1. Click add and choose 'link/tabs'.
2. Give the button a name and set the action to 'workbook(template)'.
3. Open the settings.
4. Provide the values as specified above in order to map the template.
5. Once done, click 'save and close'.
6. Click 'update links'.
7. Click on the new button to confirm that the workbook opens properly.

 

For more information on the Workbook (Template) action, please refer to https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-link-actions#workbook-template-link-settings.

 

Template Based

Externally hosted templates will be a JSON file hosted on another platform, such as GitHub or even from Microsoft Sentinel. Workbooks are able to call upon external template files if directed to the proper location and file name. For this to work, two items are needed:

• URL path to the folder in which the file is hosted
• A mapping of the workbook name and the file name in order to make a library

Examples would be:

• https://securityinsights.hosting.portal.azure.net/securityinsights/Content/Workbooks/WORKBOOKTEMPLATEFILENAME.json'.
• https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Workbooks/WORKBOOKTEMPLATEFILENAME.json.

An example of mapping the file names to a workbook name would appear as so:

 

[ {"value": "WorkspaceUsage.json", "label": "Workspace Usage Report", "tags": ["usage"]}, {"value": "AMAmigrationTracker.json", "label": "AMA Agent - Migration Tracker", "tags" : ["migration", "Agents", "deployment"]}, {"value": "MicrosoftSentinelDeploymentandMigrationTracker.json", "label": "Sentinel Deployments and Migration Tracker", "tags": ["migration","deployment"]}, {"value": "ArchivingBasicLogsRetention.json", "label": "Archiving and Basic Logs Retention", "tags": ["retention","archiving","basic logs"]} ]

 

 

To link:

1. Make a text parameter to take on the value of the library and one for the URL via JSON.
2. If desired, different methods can be used to display the template.
   1. Context pane: Opens the workbook template in a context pane within this screen. This utilizes links.
   2. Tab: Opens the workbook template within this screen. Utilizes groups in order to leverage the templates.
   3. Full screen: Opens the template in a full screen that will leave this view.
3. Make new links that will be buttons.
4. Set the action to Workbook(Template).
5. Under value, enter the parameter that contains the library URL and the parameter for the template file name. This would appear as {templateURLParam}/{workbookTemplateParam}.
6. Click done editing when finished and click on the button to test if the correct template has opened.

 

 

For more information on linking workbooks, please refer to Azure Workbooks link actions - Azure Monitor | Microsoft Learn.

 

Mapping Blade Options

 

Linking blades allows other blades within Azure to be opened from the workbook without having to leave. There are two main ways to link blades:

• Via URL: Using the URL of the blade to point the workbook on where to navigate
• Using Azure Portal metadata to point the workbook on where to navigate

Both methods leverage the custom view option, which is made up of 3 components:

• Exetension: The name of the blade.
• View: The name of the view on the blade.
• Inputs: Parameters or JSON values that are taken in as inputs when opening the blade.

URL

To use this method:

1. Create a new link that will be a button.
2. Set the action to custom view.
3. Click on the configure button.
4. In another tab, go to the blade that should be linked.
5. Copy the URL.
6. Within the configuration settings for the button, click on the URL tab and paste the URL.
   1. Note: If there is an error, change '#view' to '#blade'
7. Validate that the extension, view, and inputs are filled out.
8. Click save and close. Validate that the button properly opens the blade of interest.
   • Note: It is recommended that input values are as dynamic as possible in order to allow for the button to open multiple specified destinations. This is best done via parameters.

 

Metadata

 

This method uses metadata from the Azure Portal via developer tools within the web browser. This method is more tedious but proves to be more accurate when linking blades. Additionally, this method allows for sub-blades to be linked. To use this method:

1. Create a new link that will be a button.
2. Set the action to custom view.
3. Click on the configure button.
4. In another tab, go to the blade that should be linked.
5. Press control+alt+d. A set of yellow boxes should open.
6. Open up the developer tools within the web browser.
7. Go to the console tool.
8. Click on the link to the blade in the upper left yellow box. This should generate a response in the console tool. An example of the link will look like 'AppInsights > UsageNotebookBlade'.
9. Within the response inside the console tool, expand the input. It will list the inputs that are taken for the blade. Right click on the input and choose 'copy object'.
10. Go back to the button configuration settings.
11. Click on the JSON tab and paste the copied object.
12. Make sure that the extension and view are populated. This can be fetched from the upper left yellow box.
13. Hit control+alt+d again to close the yellow boxes.
14. Click save and close.
15. Confirm that the button opens the blade as desired.
    • Note: It is recommended to make the JSON objects in the button settings as dynamic as possible.

Settings generated from the metadata

 

 

 

Sub-menu linking done via metadata

For more information on linking, please refer to Azure Workbooks link actions - Azure Monitor | Microsoft Learn.

 

Advanced Concepts

 

Creating Tables from Parameters

 

Tables can be created from API responses and used to query and modify. Normally, the response will be in a nested JSON format. This can be parsed via KQL and made into a table format that allows for querying. An example of this would be running an ARM call to list all of the deployed Microsoft Sentinel analytic rules or all deployed data collection rules from Azure Monitor.

 

For parsing, the query will leverage different operators, such as mv-expand, bag_unpack, or pack_all.

 

To do so:

1. Create a parameter that performs the API or ARM call to get and store the response.
2. Create a new query.
3. Call on the parameter by using {PARAMETERNAME}. A common example used is print rule=dynamic({‘PARAMETERNAME’})
4. Fill out the body of the query as needed. An example would look like:

 

 

Creating ARM Templates

 

ARM templates can be both created and deployed within workbooks. This will involve a combination of parameters, links, and making an editor in the workbook. There are two main ways to go about this:

1. Create an editor for the values to be manually and statically entered by the user.
2. Store the template in the API button that will be dynamically updated by parameters.

Parameters
This portion will leverage storing values in parameters in order to make the template generation dynamic. Storing values in parameters allows for the values stored to be changed, which will update the template dynamically.

 

Links
This portion will leverage creating buttons that will contain the API URI call. If dynamically updating the template, this will also store the main portions of the template.

 

Editor
This portion will create a parameter that allows for user input and will behave like a barebones JSON editor. The value of this parameter is passed to the API button for deployment.

 

To make it dynamically:

1. Review the ARM template or PUT API documentation for the item that should be deployed.
2. Create a parameter for items such as: resource group, resource group location, workspace resource path, etc.
3. Create a link button.
4. Set the action to ARM action (if only deploying the body of the ARM template).
5. Set the ARM URI with the API version.
6. Enter the ARM template body into the request body section. Make sure that any value that should be dynamic is represented with the parameter name in curly brackets that is storing the item value.
7. Make sure that all of the values in the parameters are set.
8. When ready, click the button to deploy. A side menu will appear. If validation passes, click deploy again.

 

 

To make it manually:

1. Review the ARM template or PUT API documentation for the item that should be deployed.
2. Make a parameter that is of type text. Set the value to multiline and of type JSON.
3. Set the size to 40 (this makes the editor taller).
4. Make a link button.
5. Set the action to ARM action.
6. Modify the template and finalize the details in the editor.
7. Go to the link button for the manual template and edit it.
8. Enter the parameter name for the ARM Template from the editor in curly brackets. This does not need to be in quotes.
9. When ready, click on the link button. A side menu will appear, if validation passes, click deploy again.

 

Running Automation from a Workbook

 

There are two main ways to run automation from a workbook:

1. Linking the automation blade for an incident
2. Calling the trigger for a playbook via API

Linking
This method will leverage the steps highlighted in the linking section. There are a few items that will be needed:

• List of incidents with the name and the GUID dedicated to incident
• A URL to the link (this can be built via building a string)

To do this:

1. Create a query that lists all current open incidents. The details must contain the column IncidentName.
2. In the settings of the query, set it to export IncidentName when a row is selected.
3. Create a link button and set the action to custom view.
4. Edit the settings for the button.
5. Enter the extension name and the view name. Under JSON value, paste the expected value that the view is looking for as input. (This is covered under the linking section).
6. Once the values are set and validated, click on an incident.
7. Click on the button to open the blade for running automation.
8. A side menu will appear containing a list of available playbooks that can be run on the selected incident.

 

API
This method will leverage the steps highlighted in the linking section. There are a few items that will be needed:

• Playbook resource group, used for filtering and will be done via the steps covered for Azure Resource Graph
• Resource path to the playbook, this will be done via the steps covered for Azure Resource Graph
• Trigger type of the playbook, this will also be done via the steps for Azure Resource Graph
• Tenant ID, used by the API to identify where it should be looking for running the action

To do this:

1. Create a query that lists all current open incidents. The details must include the column IncidentName.
2. In the settings of the query, set it to export IncidentName when a row is selected.
3. Create a link button and set the action to ARM action.
4. Create parameters for resource groups with playbooks, playbooks within the selected resource group, the trigger for the playbook, and the tenant id.
5. Resource groups containing playbooks can be found by querying ARG for resources with type 'microsoft.logic/workflows' then projecting the resource group.
6. Playbooks within the selected resource group can be found by performing the same ARG query but filtering the results with 'where resourceGroup == '{RESOURCEGROUPPARAMETER}'
7. The trigger can be found by running an ARM GET call and parsing out the trigger name. The URI would be "/subscriptions/SUBSCRIPTIONID/resourceGroups/RESOURCEGROUP/providers/Microsoft.Logic/workflows/LOGICAPPNAME/triggers?api-version=2016-06-01"
8. Go back into the ARM action button. Set the path to be {Workspace}/providers/Microsoft.SecurityInsights/incidents/{IncidentName}/runPlaybook?api-version=2019-01-01-preview.
9. Set the body of the call to be:

 

{ "LogicAppsResourceId":"{Playbook}", "TenantId":"{TenantId}" }

 

10. Once each of the items are populated, make sure that an incident is clicked on.

11. When ready, click the run playbook button.

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

Aaaaaaand exhale. This was a lot of content to cover but hopefully this sparks some inspiration for new, advanced workbooks for your environment or clients. It is recommended to go through the content and review how everything was built so that the magic behind the curtain is fully shown. Again, the workbook template can be found here and will be provided in Microsoft Sentinel soon. Best of luck.