Author avatar

Benney Au

Query Azure Monitor Data with the Kusto Query Language

Benney Au

  • Oct 20, 2020
  • 5 Min read
  • 504 Views
  • Oct 20, 2020
  • 5 Min read
  • 504 Views
Data
Microsoft Azure
Data Analytics
Cloud Platforms

Introduction

Azure Monitor data is queried using the Kusto Query Language (KQL). KQL is designed to be easy to author, read, and automate. With KQL, you can analyze large volumes of data for your diagnostics, monitoring, and reporting needs. In this guide, you will learn how to perform some common queries in KQL, including how to query telemetry in structured and semi-structured data.

What Data is Queryable?

Broadly, Azure Monitor captures three types of data from your Azure Resources. These include:

  • Azure Diagnostics Logs provide a history of the operations performed on specific resources. Not all Azure resources support diagnostic logs and they need to be turned on separately for each resource. If configured, you can capture failed task scheduler events for Azure Virtual Machines or authenticated requests for Azure Storage accounts.
  • Azure Activity stores Azure administrative logs about changes to your Azure resources by who and when. You can use this to see a history of what resources were created or all the resources that were created or deleted by a specific user.
  • Azure Metrics provides aggregated time series data for the health, usage, and capacity of Azure resources.

To start querying this data from the Azure Portal, navigate to the Azure Monitor resource and click on the Logs blade. Once open, select a scope for your query. You can choose between querying the Azure Monitor logs for your entire subscription, a resource group, or a specific resource.

Querying Azure Activity Data

Azure Activity provides details on management activities and includes information on who, what, and when resources have changed. With the Logs blade open, you can start querying data.

Access the data through the AzureActivity table. Filter the results by appending | where.

The following code snippets show some examples of how to query this table and filter the results:

1
2
3
4
5
6
7
8
9
10
11
12
// filter azure activity by a specific user
AzureActivity
| where Caller == "[email protected]"

// filter azure activity by resource type
AzureActivity
| where ResourceProviderValue == "Microsoft.Web"

// check if any app service plan scaled to 5 instances or more
AzureActivity
| where OperationName == "Autoscale scale up initiated"
| where parse_json(Properties).NewInstancesCount >= 5
kusto

Querying Diagnostic Log Telemetry

Some Azure services can log diagnostic telemetry that is unique to the service. Microsoft's documentation on What is monitored by Azure Monitor? provides details on which services are supported. These diagnostic logs also need to be enabled separately.

This section will use an Azure SQL database to demonstrate some of the information you can query from Azure Monitor.

You can run the following query to get an overview of the diagnostic logs available in your own workspace.

1
2
AzureDiagnostics
| summarize count() by ResourceType, OperationName
kusto

Output:

1
2
3
4
5
6
| ResourceType      | OperationName                        | Count |
| SERVERS/DATABASES | QueryStoreWaitStatisticsEvent        | 100   |
| SERVERS/DATABASES | DatabaseWaitStatistcsEvent           | 110   |
| SERVERS/DATABASES | QueryStoreRuntimeStatisticsEvent     | 1000  |
| SERVERS/DATABASES | AutomaticTuningSettingsSnapshotEvent | 100   |
| SERVERS/DATABASES | ErrorEvent                           | 100   |

Microsoft's documentation on Azure SQL Database Diagnostic telemetry for export provides more details on what each of these events represent.

From here, you can narrow to a specific operation type and service.

1
2
3
AzureDiagnostics
| where OperationName == 'ErrorEvent'
| summarize count() by Message, DatabaseName_s, LogicalServerName_s
kusto

This query summarizes the number of errors by database. You can also change the time range to drill down to a specific event.

Note that some properties end with _s, _d, and _t. This tells you the type of data stored in that column where _s is string, _d is double, and _t is timestamp.

Conclusion

Azure Monitor provides a rich set of capabilities for capturing, querying, alerting, and monitoring your Azure resources. The queries you write will depend on what Azure resources you are currently using. You can read more in the Microsoft documentation about What is Monitored by Azure Monitor and the Kusto Query Language to get more insights into how your Azure subscription is operating.

5