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:
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]
You must log in to post a comment.