Daniel's Tech Blog

Cloud Computing, Cloud Native & Kubernetes

Ingesting Azure Diagnostic Logs into Azure Data Explorer

In today’s blog post, we look at the Azure Diagnostic Logs and how to ingest them into Azure Data Explorer. Besides the Diagnostic Logs, we cover Activity Logs and Diagnostic Metrics as well.

All three log and monitor data can be easily exported to an Azure Storage Account, an Event Hub, or a Log Analytics workspace.

Azure portal - Diagnostic Logs export options

Unfortunately, there is no direct export integration for Azure Data Explorer available.

Azure Data Explorer – Ingestion Method

Looking at the export options for the log and monitor data we can choose between an Azure Storage Account or an Event Hub. Both solutions act as a middleware component to store data till it is ingested into Azure Data Explorer.

The Storage Account option might be the one that will get expensive if a lot of files are written and read from the Storage Account due to transaction costs. Furthermore, the Storage Account option requires an Event Grid and Event Hub to get the log and monitor data into Azure Data Explorer. On the other hand, you can store the exported data in the Storage Account for a longer period.

In our case, we choose the Azure Event Hub export option. I created the Event Hub Namespace upfront with two dedicated Event Hubs each of them with four partitions and enabled the auto-inflate functionality for the Event Hub throughput units.

Azure portal - Event Hubs

Configure ingestion to Azure Data Explorer

Before we start to prepare everything on the Azure Data Explorer side, we configure the export of the Activity Logs of the subscription to the Event Hub activity_logs. Followed by the Diagnostic Logs and Metrics for the Azure Data Explorer cluster to the Event Hub diagnostic_logs.

Azure portal - Activity Logs export options Azure portal - Diagnostic Logs export options

Now, we begin with the Azure Data Explorer configuration. Microsoft has excellent documentation, which I used as a base for the following configuration.

-> https://learn.microsoft.com/en-us/azure/data-explorer/ingest-data-no-code?WT.mc_id=AZ-MVP-5000119&tabs=diagnostic-logs

We now walk in detail through the Diagnostic Logs case. The link to the code examples for the Activity Logs and Diagnostic Metrics is at the end of this blog post.

Our first step is the provisioning of a new database in our Azure Data Explorer cluster, which is called AzureMonitor with the default settings.

-> https://learn.microsoft.com/en-us/azure/data-explorer/create-cluster-and-database?WT.mc_id=AZ-MVP-5000119&tabs=full#create-a-database

Once created, we open the query editor to prepare the tables for the log and monitor data.

The initial table for the Diagnostic Logs only contained one column called RawRecords to understand the structure of the Diagnostic Logs. After understanding the structure, I used the alter command to have the final structure of the table and also updated the function we will see later on. When we look at our first KQL command, you recognize that I kept the column RawRecords. You do not need to do the same.

.create table DiagnosticLogs (
    TimeGenerated: datetime, ResourceId: string, OperationName: string,
    OperationVersion: string, Category: string, CorrelationId: string,
    Result: string, Properties: dynamic, RawRecord: dynamic

After running the command, we have our table called DiagnosticLogs. The next table we create is called DiagnosticRawRecords and is used for the data ingestion from the diagnostic_logs Event Hub.

.create table DiagnosticRawRecords (Records: dynamic)

As we do not want to store data in this table, we set the retention policy to 0.

.alter-merge table DiagnosticRawRecords policy retention softdelete = 0d

Our next step is the ingestion mapping to ensure a correct ingestion into the table.

.create table DiagnosticRawRecords ingestion json mapping 'DiagnosticRawRecordsMapping' '[{"column":"Records","Properties":{"path":"$.records"}}]'

Getting the ingested log data and monitor data into the target table DiagnosticLogs requires a KQL function and an update policy on the table.

.create function DiagnosticLogsExpand() {
        | mv-expand events = Records
        | where isnotempty(events.operationName)
        | project
            TimeGenerated = todatetime(events['time']),
            ResourceId = tostring(events.resourceId),
            OperationName = tostring(events.operationName),
            OperationVersion = tostring(events.operationVersion),
            Category = tostring(events.category),
            CorrelationId = tostring(events.correlationId),
            Result = tostring(events.resultType),
            Properties = events.properties,
            RawRecord = events

The KQL function above uses the mv-expand operator to extract the different JSON values from the Records column into a new output called events. With the project operator, we map those values onto our target table structure.

.alter table DiagnosticLogs policy update @'[{"Source": "DiagnosticRawRecords", "Query": "DiagnosticLogsExpand()", "IsEnabled": "True", "IsTransactional": true}]'

By running the above KQL command, we update the policy on our target table whenever a new record arrives in the source table DiagnosticRawRecords Azure Data Explorer executes the previously defined function and ingests the result into our target table DiagnosticLogs.

-> https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/update-policy?WT.mc_id=AZ-MVP-5000119

Before we proceed, we create the other tables for the Activity Logs and Diagnostic Metrics. As mentioned earlier, here are the links to the KQL commands.

-> https://github.com/neumanndaniel/scripts/tree/main/Azure_Data_Explorer/Diagnostic_Logs

One thing you might already notice is that we only have one RawRecord table for the Diagnostic Logs and Metrics. This is possible as both data types can be easily distinguished from each other. Diagnostic Logs have the operationName value in their records and Diagnostic Metrics have the metricName value. Those values are used in the different KQL functions.

Azure Data Explorer - Query Editor

Connect Event Hubs with Azure Data Explorer

After provisioning the table for the data ingestion, we create the necessary data connections between the Event Hubs and the Azure Data Explorer database.

Azure Data Explorer - Data Connections

-> https://learn.microsoft.com/en-us/azure/data-explorer/create-event-hubs-connection?WT.mc_id=AZ-MVP-5000119&tabs=portalADX%2Cget-data-2

As seen in the screenshot, we provide a name for the data connection and select the appropriate Event Hub. The compression setting is kept with its default setting None. Furthermore, we provide the table name of the raw records table with the corresponding ingestion mapping. Last but not least, we select the managed identity type for the data connection. In our case, from type system-assigned.

Once the data connection has been created, we can monitor the connection and see how many events have been received and processed.

Azure Data Explorer - Data Connections Monitor

Reducing ingestion latency

Per default, our tables with the Event Hub connections use the queued ingestion method. When data is finally ingested is defined by three configuration parameters time, item, and size for the ingestion batches. The default values for those parameters are 5 minutes, 1000 items, and 1 GB. Whatever threshold is reached first triggers the final ingestion.

In the worst case, we have an ingestion latency of 5 minutes. It might be fast enough, but when we want near real-time ingestion, we either customize the batch ingestion policy or enable the streaming ingestion policy.

-> https://learn.microsoft.com/en-us/azure/data-explorer/ingest-data-overview?WT.mc_id=AZ-MVP-5000119

For the latter, streaming ingestion must be enabled on the Azure Data Explorer cluster.

We decide on the streaming ingestion policy and enable the policy on the whole Azure Data Explorer database instead of specific tables.

.alter database AzureMonitor policy streamingingestion enable

Querying log and monitor data

Finally, we run our first queries against the log and monitor data.

| where TimeGenerated > ago(1d)

Azure Data Explorer - DiagnosticLogs table

In the screenshot above, we see the Diagnostic Logs of the Azure Data Explorer cluster.

One of the more interesting data points is the Diagnostic Metric about the ingestion latency.

| where TimeGenerated > ago(1d)
| where MetricName == "IngestionLatencyInSeconds"
| extend IngestionLatency=Total
| project TimeGenerated, ["Ingestion Latency (sec)"]=IngestionLatency
| render timechart

Azure Data Explorer - DiagnosticMetrics table Azure Data Explorer - DiagnosticMetrics table

As seen in the screenshots, you see the near real-time ingestion when using the streaming ingestion policy and when it was enabled.


Activity Logs, Diagnostic Logs, and Diagnostic Metrics can be ingested via Event Hubs into an Azure Data Explorer. It is indeed more effort than directly using an Azure Log Analytics workspace but depending on your needs and requirements be the better solution.

For instance, from a pure infrastructure cost perspective the solution with Azure Data Explorer and Event Hub is less expensive than Log Analytics when you ingest a lot of data. If data retention is a topic for you then Azure Data Explorer is again an advantage as you can define flexible retention periods. Even an infinite retention period is possible. As of writing this blog post, Log Analytics is limited to an interactive retention period of 730 days max and an archive retention period of up to 12 years.

Ultimately, it depends on your needs and requirements if you choose Azure Data Explorer or Log Analytics to store your log and monitor data.

The example KQL files for the Activity Logs, Diagnostic Logs, and Diagnostic Metrics can be found on my GitHub repository.

-> https://github.com/neumanndaniel/scripts/tree/main/Azure_Data_Explorer/Diagnostic_Logs

WordPress Cookie Notice by Real Cookie Banner