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.
- Using KQL and externaldata() operator to pull infromation from json file
- ADF Copy activity is making double column in adx as "infinity" for 1.7976931348623157E308 value
- KQL: how to exclude first and last incomplete bin?
- KQL query to extend new column with other row values
- How to create an empty bag in KQL?
- Column contains text contains other column
- Kql function to append columns
- Applying a KQL query to the transform_kql parameter through terraform
- KQL query to list the secrets and keys in keyvault that are expired or going to expire in 7 days
- Monitor HTTP traffic for Azure Web App using Log analytics and KQL query
- How to hide or change legend in "Run query and visualize results" logic app function?
- How to calculate time difference between the latest entries of two indirectly tables in Kusto (KQL)?
- Calculate the number of overlapping days for each device using KQL
- How good is performance of "has" operator in Kusto when RHS is not exactly one term?
- how to retrieve all resources under given subnet using Azure Resource Graph Explorer query
- Count how many elements are in an array created by make_set in kusto language
- Inserting data in KQL
- Stuck at PIM Diagnostics via KQL
- union with all fields
- How to create an Alert for Data Collection Rule deletion across all VMs in a subscription?
- Are joins within the same cluster consistent and isolated in Azure Data Explore?
- How to run Get Kusto query using Rest API
- use range with custom field
- Azure Application Insights: How to make a nice time graph from a custom KQL query?
- KQL: bag unpack json into single row
- Is customDimension column name 'first' restricted?
- How can I get sub value in nested json via KQL?
- ADX - KQL: Conditionally Extending Property Columns from Dynamic Column
- Kusto make-series by month
- Need suggestion to implement restrictive permissions to Kusto Tables and Functions