In my previous blog post, I covered getting started with Azure Sentinel, including how to configure and connect it to a data source. This week I want to talk about the Kusto Query Language (KQL), Azure Workbooks and Playbooks. Let’s get started with KQL.
Kusto Query Language
The Kusto Query Language (KQL) is a read-only language similar to SQL that’s used to query large datasets in Azure. These datasets typically reside in the following services:
- Azure Application Insights
- Azure Log Analytics
- Azure Monitor Logs
- Azure Data Explorer
For comparison, below is an example of the same query written in SQL vs. KQL:
SELECT COUNT(*) FROM ApplicationLog WHERE WarningLevel = “Critical”
ApplicationLog | where WarningLevel == “Critical” | count
KQL ‘pipes’ data from one command to another (similar to PowerShell) making it easier to structure and read queries. The above query takes all the rows from
ApplicationLog and pipes them through a where statement which filters the rows where
WarningLevel == “Critical” then counts the number of rows.
To help users transition from SQL to KQL, Microsoft has created a SQL to Kusto Cheat Sheet.
Microsoft has created free demo platforms that already have data and queries saved to help users understand how queries and formed and built.
If you’re interested in learning more about KQL, here are a couple resources to help you get started.
Azure Workbooks combine text, log queries, metrics, and parameters into interactive reports within the Azure Portal. Workbooks can query data from multiple sources within Azure and can be edited by individuals with access to those resources.
- To get started creating a workbook, browse to the Azure Sentinel Workspace in the Azure Portal and click ‘Workbooks’
- Select a Workbook template. In this example, I’ll use Azure Activity since that was the data connector I used in my example last week.
- Click the ‘View template’ button.
There are over 80 templates available and you can create a custom workbook if the existing templates don’t meet your specific requirements.
When the Workbook renders, you get a visual representation of activities based on the template selected in the previous step. This specific workbook can be filtered by time range, caller (subscription) and resource group. Other workbooks may offer different filtering options.
- To create your own workbook, click the ‘+ Add workbook’ button.
- Click the ‘Edit’ button. This page displays a basic query based on the data connector selected.
- The page will be updated with two ‘Edit’ and one ‘Add’ buttons.
- The edit button on the top right, allows you to edit the markdown text for the workbook.
- After making changes, click the Preview tab to preview the changes, then click the ‘Done Editing’ button.
- The edit button on the bottom rights allows you to make changes to the existing sections. For example, if you wanted to change the bar graph to a pie chart, click the ‘Edit’ button below the graph section and change the KQL query to render a
- Click ‘Done Editing’.
- This change will render the workbook with a pie chart.
- To add sections to the workbook, click the ‘Add’ button and select the item you want to add. For this example, I selected ‘Add query’. This will allow you to use KQL to query the log data.
- In the new section enter a KQL query or for sample queries, click the ‘Samples’ button.
- When you’ve completed editing your workbook, click the ‘Done Editing’ button.
- This workbook can now be saved and shared with individuals who have appropriate permissions.
Azure playbooks are a collection of procedures that can be run from Azure Sentinel in response to an alert or event. These playbooks help automate and orchestrate responses and can be run manually or automatically. Playbooks are based on Azure Logic Apps which offer both built in templates and customizable workflows.
- To get started creating a Playbook, browse to the Azure Sentinel Workspace in the Azure Portal and click ‘Playbooks’.
- Click ‘Add Playbook’.
- A new browser tab will open on the Azure Logic Apps page. If you’re not familiar with how to create a logic app, I have linked a couple tutorials below:
- In the example below I created a playbook to send an email when a response to an Azure Sentinel alert is created.
Note: If you are connecting a Google account, please review the following documentation on how to authorize it with Azure: https://docs.microsoft.com/en-us/azure/connectors/connectors-google-data-security-privacy-policy#update-affected-workflows
- To trigger this workflow, browse to ‘Azure Active Directory’ and add or delete users. If you’re not familiar with how to do this, I have linked a tutorial from Microsoft to get you started.
- Run the trigger in the logic app that sends the email notification.
- Open the email account you associated with the workflow and you should see a notification email.
Microsoft has a GitHub repository with prebuilt playbooks you can use. These can be deployed to Azure by following the steps below:
- Browse to https://github.com/Azure/Azure-Sentinel/tree/master/Playbooks and select the playbook that best fits your use case.
- Click the ‘Deploy to Azure’ Button.
- Select the appropriate account.
- Select the appropriate Subscription, Resource group and enter a User Name. Click the ‘Review and create’ button.
- Browse to the Block-AADUser Logic app.
- Click ‘Logic app designer’ in the Development tools menu and fix any authorization or activation issues.
- Manually run the logic app by clicking the run Trigger button on overview blade.
In this blog post I discussed the KQL query language and demonstrated Azure Workbooks and Playbooks. If you’re interested in learning more about Azure Sentinel, Microsoft offers a free training course on becoming an Azure Sentinel Ninja. You can find it here.
Thank you for reading!