如何修改Azure Log Analytics查询实现按周几统计成功登录量

SigninLogs
| where TimeGenerated >= ago(90d)
| where ResultType == 0
| extend DayOfWeek = dayofweek(TimeGenerated)
| extend WeekDayName = case(
    DayOfWeek == 0d, "周日",
    DayOfWeek == 1d, "周一",
    DayOfWeek == 2d, "周二",
    DayOfWeek == 3d, "周三",
    DayOfWeek == 4d, "周四",
    DayOfWeek == 5d, "周五",
    DayOfWeek == 6d, "周六",
    "未知"
)
| summarize TotalSuccessfulSigns = count() by WeekDayName, DayOfWeek
| order by DayOfWeek asc
| project WeekDayName, TotalSuccessfulSigns
| render columnchart

查询调整说明 #

• 新增where TimeGenerated >= ago(90d)规则，限定只统计最近90天的登录日志
• 调用dayofweek()函数提取每条日志生成时间对应的星期标识，再通过case()函数将标识转换为中文星期名称，方便图表展示
• 汇总维度从原来的按天分组改为按星期几分组，最后直接渲染为柱状图，可直接在Log Analytics工作区展示结果

内容的提问来源于stack exchange，提问作者Bnd10706