azure - 如何解决 appinsight 警报中的日问题？

Question

我的 kusto 查询有问题。此 kusto 查询在警报调用内运行。我尝试通过电子邮件向我们的客户发送通知。

场景:
我尝试在周六上午 7 点到 13 点之间发送消息。 (仅限周六)但周日我也会收到消息。查询下面没有任何内容。我认为这与应用程序洞察警报有关。

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

Answer 1

Kusto 中的所有日期时间均存储为 UTC。
使用datetime_utc_to_local获取您本地时区的时间戳，例如:

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

Fiddle

<表类=“s-表”><标题>print_0 <正文>2023-01-10T21:10:08.0645922Z

附注
您的查询可以大大简化。

• KQL 支持 bool 数据类型 (bool)。
• KQL 支持日期时间和时间跨度算术。
  即使出于某种原因您想要添加名为 issaturday 的列，然后按它进行过滤，也可以轻松完成，如下所示:
  | 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

<表类=“s-表”><标题>我时间戳 <正文>52023-01-07T08:37:39.3449345Z802023-01-07T09:07:36.4794478Z832023-01-07T10:51:19.4051319Z

Fiddle