如何将两条相同结构的KQL查询合并为一条？

需求：将两条基于StorageBlobLogs表的统计查询合并为一条，在同一行展示RequesterObjectId分别为"gisa"和"BIS"的SftpWrite操作次数。

原始查询：

StorageBlobLogs 
| where Uri startswith "sftp://mystrgsftp.blob.core.windows.net/gisa/" 
and OperationName == "SftpWrite"
and RequesterObjectId  == "gisa"
and TimeGenerated between (startofday(now(),0) .. datetime(now))
| count as Count_Gisa;

StorageBlobLogs 
| where Uri startswith "sftp://mystrgsftp.blob.core.windows.net/gisa/" 
and OperationName == "SftpWrite"
and RequesterObjectId  == "BIS"
and TimeGenerated between (startofday(now(),0) .. datetime(now))
| count as Count_BIS;

可行方案 #

方案一：使用summarize结合case函数 #

先过滤所有共同条件，再通过case函数分别统计两个目标ID的操作次数：

StorageBlobLogs 
| where Uri startswith "sftp://mystrgsftp.blob.core.windows.net/gisa/" 
  and OperationName == "SftpWrite"
  and RequesterObjectId in ("gisa", "BIS")
  and TimeGenerated between (startofday(now()) .. now())
| summarize 
    Count_Gisa = sum(case(RequesterObjectId == "gisa", 1, 0)),
    Count_BIS = sum(case(RequesterObjectId == "BIS", 1, 0))

方案二：使用pivot转置结果 #

先按RequesterObjectId分组统计数量，再通过pivot将行转列为指定字段：

StorageBlobLogs 
| where Uri startswith "sftp://mystrgsftp.blob.core.windows.net/gisa/" 
  and OperationName == "SftpWrite"
  and RequesterObjectId in ("gisa", "BIS")
  and TimeGenerated between (startofday(now()) .. now())
| summarize count() by RequesterObjectId
| pivot RequesterObjectId, sum(count_)
| project Count_Gisa = gisa, Count_BIS = BIS

两种方案均会输出如下格式的结果：

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