KQL Query for Calculation of ErrorDuration within Timespan
I was hoping to get some help writing a kusto query, to summarize errors across a timespan into bins.
I have the following result from a query, that extracts all errors that has occurred within a timeframe. Each error is represented with a StartTime, EndTime, ErrorDuration and ErrorCode:
datatable(StartTime: datetime, EndTime: datetime, ErrorDuration: timespan, ErrorCode: int)
[
datetime(2024-08-05T05:00:00.000Z), datetime(2024-08-05T06:00:00.000Z), timespan(01:00:00), 497,
datetime(2024-08-05T05:10:00.000Z), datetime(2024-08-05T06:10:00.000Z), timespan(01:00:00), 402,
datetime(2024-08-05T08:10:00.000Z), datetime(2024-08-05T08:30:00.000Z), timespan(00:20:00), 402,
datetime(2024-08-05T08:20:00.000Z), datetime(2024-08-05T08:40:00.000Z), timespan(00:20:00), 402,
datetime(2024-08-06T03:20:00.000Z), datetime(2024-08-08T03:20:00.000Z), timespan(2d), 467,
datetime(2024-08-07T01:00:00.000Z), datetime(2024-08-07T06:00:00.000Z), timespan(05:00:00), 455
];
Given a timeRange like this:
let _startTime = datetime(2024-08-05T00:00:00Z);
let _endTime = datetime(2024-08-08T00:00:00Z);
And a Bin timespan:
let _binTimestamp = 1h;
I want to generate a Result with the following Criteria:
- Generate a bin from _startTime to _endTime with bin of timespan _binTimestamp
- Within each bin i want the following:
- StartTime (of Bin)
- ErrorMinutes (How many minutes within the Bin has an error occured)
- ErrorCodes (Array of ErrorCodes that occurred within the Bin)
Here is how the Result should look, I just can't figure out how to write the Query myself:
datatable(Timestamp: datetime, ErrorMinutes: int, ErrorCodes: dynamic)
[
datetime(2024-08-05T00:00:00Z), 0, dynamic([]),
datetime(2024-08-05T01:00:00Z), 0, dynamic([]),
datetime(2024-08-05T02:00:00Z), 0, dynamic([]),
datetime(2024-08-05T03:00:00Z), 0, dynamic([]),
datetime(2024-08-05T04:00:00Z), 0, dynamic([]),
datetime(2024-08-05T05:00:00Z), 60, dynamic([497, 402]),
datetime(2024-08-05T06:00:00Z), 10, dynamic([497, 402]),
datetime(2024-08-05T07:00:00Z), 0, dynamic([]),
datetime(2024-08-05T08:00:00Z), 30, dynamic([402]),
datetime(2024-08-05T09:00:00Z), 0, dynamic([]),
datetime(2024-08-05T10:00:00Z), 0, dynamic([]),
datetime(2024-08-05T11:00:00Z), 0, dynamic([]),
datetime(2024-08-05T12:00:00Z), 0, dynamic([]),
datetime(2024-08-05T13:00:00Z), 0, dynamic([]),
datetime(2024-08-05T14:00:00Z), 0, dynamic([]),
datetime(2024-08-05T15:00:00Z), 0, dynamic([]),
datetime(2024-08-05T16:00:00Z), 0, dynamic([]),
datetime(2024-08-05T17:00:00Z), 0, dynamic([]),
datetime(2024-08-05T18:00:00Z), 0, dynamic([]),
datetime(2024-08-05T19:00:00Z), 0, dynamic([]),
datetime(2024-08-05T20:00:00Z), 0, dynamic([]),
datetime(2024-08-05T21:00:00Z), 0, dynamic([]),
datetime(2024-08-05T22:00:00Z), 0, dynamic([]),
datetime(2024-08-05T23:00:00Z), 0, dynamic([]),
datetime(2024-08-06T00:00:00Z), 0, dynamic([]),
datetime(2024-08-06T01:00:00Z), 0, dynamic([]),
datetime(2024-08-06T02:00:00Z), 0, dynamic([]),
datetime(2024-08-06T03:00:00Z), 40, dynamic([467]),
datetime(2024-08-06T04:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T05:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T06:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T07:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T08:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T09:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T10:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T11:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T12:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T13:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T14:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T15:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T16:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T17:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T18:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T19:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T20:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T21:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T22:00:00Z), 60, dynamic([467]),
datetime(2024-08-06T23:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T00:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T01:00:00Z), 60, dynamic([467, 455]),
datetime(2024-08-07T02:00:00Z), 60, dynamic([467, 455]),
datetime(2024-08-07T03:00:00Z), 60, dynamic([467, 455]),
datetime(2024-08-07T04:00:00Z), 60, dynamic([467, 455]),
datetime(2024-08-07T05:00:00Z), 60, dynamic([467, 455]),
datetime(2024-08-07T06:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T07:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T08:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T09:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T10:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T11:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T12:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T13:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T14:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T15:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T16:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T17:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T18:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T19:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T20:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T21:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T22:00:00Z), 60, dynamic([467]),
datetime(2024-08-07T23:00:00Z), 60, dynamic([467])
];
Solution
Have you tried this:
let Data = datatable(
StartTime: datetime,
EndTime: datetime,
ErrorDuration: timespan,
ErrorCode: int
)
[
datetime(2024-08-05T05:00:00.000Z), datetime(2024-08-05T06:00:00.000Z), timespan(01:00:00), 497,
datetime(2024-08-05T05:10:00.000Z), datetime(2024-08-05T06:10:00.000Z), timespan(01:00:00), 402,
datetime(2024-08-05T08:10:00.000Z), datetime(2024-08-05T08:30:00.000Z), timespan(00:20:00), 402,
datetime(2024-08-05T08:20:00.000Z), datetime(2024-08-05T08:40:00.000Z), timespan(00:20:00), 402,
datetime(2024-08-06T03:20:00.000Z), datetime(2024-08-08T03:20:00.000Z), timespan(2d), 467,
datetime(2024-08-07T01:00:00.000Z), datetime(2024-08-07T06:00:00.000Z), timespan(05:00:00), 455
];
let _startTime = datetime(2024-08-05T00:00:00Z);
let _endTime = datetime(2024-08-08T00:00:00Z);
let _binTimestamp = 1h;
let hours = range Hour from _startTime to _endTime step
hours
| join kind=leftouter (
Data
| extend HourlyRange = range(bin(StartTime, _binTimestamp), EndTime, _binTimestamp)
| mvexpand Hour = HourlyRange
| extend OverlapStart = iff(Hour == bin(StartTime, _binTimestamp), StartTime, todatetime(Hour))
| extend OverlapEnd = iff(Hour == bin(EndTime, _binTimestamp), EndTime, todatetime(Hour + _binTimestamp))
| mvexpand minute = range(OverlapStart, OverlapEnd, 1m)
| summarize ErrorMinutesSet = make_set(minute), ErrorCodes = make_set(ErrorCode) by todatetime(Hour)
| extend ErrorMinutes = iff(array_length(ErrorMinutesSet) >= tolong(_binTimestamp / 1m), tolong(_binTimestamp / 1m), array_length(ErrorMinutesSet) - 1)
| extend ErrorMinutes = iff(ErrorMinutes == 0 and array_length(ErrorMinutesSet) == 1, 1, ErrorMinutes)
) on Hour
| project Timestamp=Hour, ErrorDurationInMinutes=coalesce(ErrorMinutes, 0), ErrorCodes=coalesce(ErrorCodes, dynamic([]))
| order by Timestamp asc
Find working Sample here.
- Kusto make-series by month
- KQL merge rows with the same ID into one row
- How to do a KQL time window join at millisecond granularity?
- Kusto Query replace the timestamp of duplicate rows
- Bin producing unexpected results
- How to create a whitelist with two fields in KQL with a Watchlist?
- KQL Query for Calculation of ErrorDuration within Timespan
- Add text to point(s) in Kusto linechart
- Kusto query with multiple sample values from an aggregate
- How to get data for specific date (startofweek) in a given time range in KQL?
- How can I convert TimeGenerated to TimeIngested in this KQL query for an Azure Workbook?
- multiple if and else within kql azure
- Controlling scatter chart markers in Azure Data Explorer dashboard using KQL
- Monitor HTTP traffic for Azure Web App using Log analitics and KQL query
- Defining external table kind as delta
- Kusto query map through array
- how to use or condition in kql
- KQL: Every time a word is mentioned in a column append it to a new column as a list
- KQL: Create an id column every time a value changes in column x ordered by another column y
- KQL query returns available memory but not Total Memory
- Display lines & values for whole range of y-axis values in Kusto linechart
- Kusto - fetch data from one table where matching records do not exist in another table
- KQL - extract property value from an array of JSON objects, based on the value of another property
- Defender Advance Query
- Calculation of outlier score in series_outlier method
- How to get Azure Advisor scores from azure resource graph explorer?
- KQL: How to reference columns within a let query in the next query
- KQL Query works in advanced hunting but fails when made into a detection rule
- Creating KQL query to get Azure VMs not patched since 30days
- Create a function to calculate power of tens with a loop and reuse them in another query