kqlazure-data-explorer

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:

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
    

    enter image description here

    Find working Sample here.