How to Use serialize to Add Line Numbers to KQL Results for Azure Sentinel Workbooks

Azure Sentinel 1 Minute

A customer asked recently if they could add line numbers to query results in an Azure Sentinel Workbook. They wanted to show the number of rows returned from the query in one Workbook module and then show total records for a value side-by-side in another module. This would allow them to identify if the query was missing records.

This is where the serialize operator for KQL comes into play.

Shown below, the example query pulls successful and unsuccessful logins for logon types within a specific time range. However, as per the highlighted line, also adds a numbered column.

let timeframe = 1d;
SecurityEvent
| where TimeGenerated >= ago(timeframe)
| where EventID in (4624, 4625)
| where AccountType == 'User' 
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), Amount = count() by LogonTypeName
| extend timestamp = StartTimeUtc
| serialize Num = row_number()
| project Num, timestamp, LogonTypeName, StartTimeUtc, EndTimeUtc, Amount

What it looks like:

Numbered lines in the query results
In the Workbook

The KQL query that produces the pie chart for total users (the right-hand module), is the following:

let timeframe = 1d;
SecurityEvent
| where TimeGenerated >= ago(timeframe)
| where EventID in (4624, 4625)
| where AccountType == 'User' 
| summarize count() by AccountType
| render piechart

Read more about the serialize operator for KQL: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/serializeoperator

=========================

[Want to discuss this further? Hit me up on Twitter or LinkedIn]

Author

Published