如何用Application Insights KQL筛选不在另一查询结果中的数据？

需求说明 #

需要从失败交易的查询结果里，找出那些没有出现在成功交易结果中的交易编号，具体示例如下：

失败交易查询 #

customEvents
| extend Product = tostring(customDimensions.["Is Successful"])
| extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
| extend StoreCode = tostring(customDimensions.["StoreCode"])
| where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
| where customDimensions.["Is Successful"] == "False"
| project TransactionNumber

失败交易返回示例 #

145
167
100
94
56

成功交易查询 #

customEvents
| extend Product = tostring(customDimensions.["Is Successful"])
| extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
| extend StoreCode = tostring(customDimensions.["StoreCode"])
| where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
| where customDimensions.["Is Successful"] == "True"
| project TransactionNumber

成功交易返回示例 #

94
95
96
97
167
100

期望结果 #

只保留失败交易里没在成功交易中出现的编号：

145
56

问题：尝试的查询未得到预期结果 #

你写的查询用了fullouter join，但判断条件有误，导致无法得到正确结果：

let failures = (customEvents
    | extend Product = tostring(customDimensions.["Is Successful"])
    | extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
    | extend StoreCode = tostring(customDimensions.["StoreCode"])
    | where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
    | where customDimensions.["Is Successful"] == "False"
    | project TransactionNumber);
let successes = (customEvents
    | extend Product = tostring(customDimensions.["Is Successful"])
    | extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
    | extend StoreCode = tostring(customDimensions.["StoreCode"])
    | where name contains 'Transaction Posted' and customDimensions.['Transaction Number'] != ''
    | where customDimensions.["Is Successful"] == "True"
    | project TransactionNumber);
failures
| join kind=fullouter (successes) on TransactionNumber
| where isnull(TransactionNumber)
| project TransactionNumber

正确解决方案 #

在Kusto里，筛选左表存在但右表不存在的记录，最优方式是用leftanti连接，直接一步到位，而且效率更高。同时可以简化查询里多余的extend（比如没用到的Product、StoreCode可以删掉）：

let failures = customEvents
    | extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
    | where name contains 'Transaction Posted' and TransactionNumber != ''
    | where tostring(customDimensions.["Is Successful"]) == "False"
    | project TransactionNumber;
let successes = customEvents
    | extend TransactionNumber = tostring(customDimensions.["Transaction Number"])
    | where name contains 'Transaction Posted' and TransactionNumber != ''
    | where tostring(customDimensions.["Is Successful"]) == "True"
    | project TransactionNumber;
// 左反连接：只保留failures中不在successes里的交易编号
failures
| join kind=leftanti successes on TransactionNumber

错误原因解析 #

你之前用fullouter join后，where isnull(TransactionNumber)的判断完全错误：因为TransactionNumber来自failures表，即使没有匹配到successes里的记录，这个字段也不会为空。就算要修复fullouter join的写法，也应该判断successes那边的关联字段是否为空，但这种方式既麻烦又低效，不如直接用leftanti join。

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