Search code examples
kqlazure-alerts

Azure AlertRule queries sometimes changes query

I have an Azure AlertRule what validates a query, where to tables are joining on a timestamp.
Is seems like Azure is changing the query, replacing the statement bin(..) with bin_at(..).

The Original query: before

The query, opened after the alert triggered. after

In my case this change is enough to alter the result of the query, becuase the extra added parameter (yellow arrow).

Is there any way around the issue?

EDIT:

as David pointed out in the comments - This behaviour can be reproduced by running this kql as the query of an alart rule:
print bin(now(), 1h)

Original:

For completness, i've added the entier query here. I was not able to shorten it more that this. (sorry)

let frame_size = 1h;
let messages = datatable (timestamp: datetime ) 
[
    datetime(2022-11-09T23:01:00Z),datetime(2022-11-09T23:02:00Z),datetime(2022-11-09T23:03:00Z),datetime(2022-11-09T23:04:00Z),
    datetime(2022-11-09T22:01:00Z),datetime(2022-11-09T22:02:00Z),datetime(2022-11-09T22:03:00Z),datetime(2022-11-09T22:04:00Z),
    datetime(2022-11-09T21:01:00Z),datetime(2022-11-09T21:02:00Z),datetime(2022-11-09T21:03:00Z),datetime(2022-11-09T21:04:00Z),
    datetime(2022-11-09T20:01:00Z),datetime(2022-11-09T20:02:00Z),datetime(2022-11-09T20:03:00Z),datetime(2022-11-09T20:04:00Z),
    datetime(2022-11-09T19:01:00Z),datetime(2022-11-09T19:02:00Z),datetime(2022-11-09T19:03:00Z),datetime(2022-11-09T19:04:00Z),
    datetime(2022-11-09T18:01:00Z),datetime(2022-11-09T18:02:00Z),datetime(2022-11-09T18:03:00Z),datetime(2022-11-09T18:04:00Z),
    datetime(2022-11-09T17:01:00Z),datetime(2022-11-09T17:02:00Z),datetime(2022-11-09T17:03:00Z),datetime(2022-11-09T17:04:00Z),
    datetime(2022-11-09T16:01:00Z),datetime(2022-11-09T16:02:00Z),datetime(2022-11-09T16:03:00Z),datetime(2022-11-09T16:04:00Z),
    datetime(2022-11-09T15:01:00Z),datetime(2022-11-09T15:02:00Z),datetime(2022-11-09T15:03:00Z),datetime(2022-11-09T15:04:00Z),
    datetime(2022-11-09T14:01:00Z),datetime(2022-11-09T14:02:00Z),
    datetime(2022-11-09T13:01:00Z),datetime(2022-11-09T13:02:00Z),
    datetime(2022-11-09T12:01:00Z),datetime(2022-11-09T12:02:00Z),
    datetime(2022-11-09T11:01:00Z),datetime(2022-11-09T11:02:00Z),
    datetime(2022-11-09T10:01:00Z),datetime(2022-11-09T10:02:00Z),
    datetime(2022-11-09T09:01:00Z),datetime(2022-11-09T09:02:00Z),
    datetime(2022-11-09T08:01:00Z),datetime(2022-11-09T08:02:00Z),
    datetime(2022-11-09T07:01:00Z),datetime(2022-11-09T07:02:00Z),
    datetime(2022-11-09T06:01:00Z),datetime(2022-11-09T06:02:00Z),
    datetime(2022-11-09T06:01:00Z),datetime(2022-11-09T06:02:00Z),
    datetime(2022-11-09T05:01:00Z),datetime(2022-11-09T05:02:00Z),
    datetime(2022-11-09T04:01:00Z),datetime(2022-11-09T04:02:00Z),
    datetime(2022-11-09T03:01:00Z),datetime(2022-11-09T03:02:00Z),
    datetime(2022-11-09T02:01:00Z),datetime(2022-11-09T02:02:00Z),
    datetime(2022-11-09T01:01:00Z),datetime(2022-11-09T01:02:00Z),
    datetime(2022-11-09T00:01:00Z),datetime(2022-11-09T00:02:00Z),
]; 
let create_time_intervals = (start_datetime: datetime, end_datetime: datetime, frame_size: timespan) 
{
    let hourly_distribution_as_dk_time = datatable (hour: int, expected_documents_count: int)
    [0,2, 1,2, 2,2, 3,2, 4,2, 5,2, 6,2, 7,2, 8,2, 9,2, 10,2, 11,2, 12,2, 13,2, 14,2, 15,4, 16,4, 17,4, 18,4, 19,4, 20,4, 21,4, 22,4, 23,4 ]
    ; 
    let start_datetime_dk = datetime_utc_to_local(start_datetime, "Europe/Copenhagen");
    let end_datetime_dk = datetime_utc_to_local(end_datetime, "Europe/Copenhagen");
    // Generate all time frames for given interval excluding the first incomplete frame.
    range frame_start from bin(start_datetime + frame_size, frame_size) to end_datetime step frame_size 
    | project bin(frame_start, frame_size)
    | extend date_dk = datetime_utc_to_local(frame_start, "Europe/Copenhagen")
    | extend hour = toint(datetime_part("Hour", date_dk))
    | join kind=leftouter hourly_distribution_as_dk_time on hour
    | project frame_start, expected_documents_count
    | order by frame_start asc
};
let msg = messages
| where timestamp >= ago(1h)
|summarize cnt = count() 
    by frame_start = bin(timestamp,frame_size); // *** <==== this bin(..) changes when running ***
let frame_results = create_time_intervals(ago(1h), now(), frame_size)
| join kind=leftouter msg on frame_start
| extend ok = cnt;
frame_results
| summarize 
    sum_expected = sum(expected_documents_count),
    sum_ok = sum(ok)
| extend ok = sum_expected == sum_ok
| extend ok_int = toint(ok) //for alerting purposes
Solution

  • Well, an obvious work-around would be to replace bin with bin_at, e.g. -

    let timestamp = datetime(2000-03-04 11:22:33);
let frame_size = 1h;
print bin_at(timestamp, frame_size, datetime(2000))
    print_0
    2000-03-04T11:00:00Z

    Fiddle