I have a problem with my kusto query. this kusto query is running inside the alert call. I tried to send a notification to our client via email.

Scenario :
I am trying to send messages between 7 am and 13 am on Saturday. (only Saturday) But I am getting messages also Sunday. There is nothing here below the query. I think it is related to app insight alert.

requests 
| extend Customer= trim_end('/', tostring(split(customDimensions.source, '//')[1]))
| extend alarmOK=iif(datetime_diff('minute', now(), timestamp) > 20, 1, 0) 
| extend issaturday=iif(dayofweek(timestamp) == 6d, 1, 0) 
| extend workinghour = hourofday(timestamp) 
| extend
    sendnotify1=iif(workinghour >= 7 and workinghour < 13, 1, 0),
    sendnotify2=iif(hourofday(now()) >= 7 and hourofday(now()) < 13, 1, 0)
| extend alarmmessage = "alert message"
| where timestamp > ago(24h) and Customer == "mycustomer"
| where issaturday == 1
| where workinghour >= 7 and workinghour < 13
| top 1 by timestamp desc

CodePudding user response：

All datetimes in Kusto are stored as UTC.
Use datetime_utc_to_local to get the timestamp in your local time zone, e.g.:

let timestamp = now();
print datetime_utc_to_local(timestamp, "Australia/Melbourne") 

Fiddle

P.S.
Your query could be simplified significantly.

• KQL supports the Boolean data type (bool).
• KQL supports datetime & timespan arithmetic.
  Even if for some reason you wanted to add a column named issaturday and then filter by it, it could easily be done like this:
  | extend issaturday = dayofweek(timestamp) == 6d | where issaturday

// Sample data generation. Not part of the solution.
let requests = materialize(
    range i from 1 to 100 step 1 
    | extend timestamp = ago(7d * rand())
);
// Solution starts here
requests 
| where     dayofweek(timestamp) == 6d
        and timestamp % 1d between (7h .. 13h)
        and now() - timestamp > 20m

Fiddle