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])
];
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.