我正在编写一个查询，该查询计算了用户在过去30天内登录多少计算机。在过去的30天中，我需要每天将每个用户的登录器计算到另一台机器上，以从日志中获得准确的每日平均值，稍后我将其与检测异常的阈值进行比较。
我的问题是查询不包括我表中用户的所有结果，如果用户的30天中的30天中只有1个返回日志，而是其他29个。我想能够说出类似的话，如果没有结果(isnull？iff？(，然后跳过这一天，继续或能够将表值设置为空白/0，那么当我执行AVG时，它只会添加0平均。
最终表格应返回TargetUserName，Avg(每天/30的总和不包括当前日(。
此处的代码全天显示进行测试，而仅显示10天，而不是30天缩短了测试。
现在，它将正确显示在过去30天内具有日志的用户，但是如果用户甚至没有日志，则将其排除在最终结果之外。
let Event=(){SecurityEvent | where EventID == 4624 or EventID==528};
let d1=(){Event | where TimeGenerated between(ago(2d) .. ago(1d))| summarize DT1=dcount(WorkstationName) by TargetUserName};
let d2=(){Event | where TimeGenerated between(ago(3d) .. ago(2d)) | summarize DT2=dcount(WorkstationName) by TargetUserName};
let d3=(){Event | where TimeGenerated between(ago(4d) .. ago(3d)) | summarize DT3=dcount(WorkstationName) by TargetUserName};
let d4=(){Event | where TimeGenerated between(ago(5d) .. ago(4d)) | summarize DT4=dcount(WorkstationName) by TargetUserName};
let d5=(){Event | where TimeGenerated between(ago(6d) .. ago(5d)) | summarize DT5=dcount(WorkstationName) by TargetUserName};
let d6=(){Event | where TimeGenerated between(ago(7d) .. ago(6d)) | summarize DT6=dcount(WorkstationName) by TargetUserName};
let d7=(){Event | where TimeGenerated between(ago(8d) .. ago(7d)) | summarize DT7=dcount(WorkstationName) by TargetUserName};
let d8=(){Event | where TimeGenerated between(ago(9d) .. ago(8d)) | summarize DT8=dcount(WorkstationName) by TargetUserName};
let d9=(){Event | where TimeGenerated between(ago(10d) .. ago(9d)) | summarize DT9=dcount(WorkstationName) by TargetUserName};
let d10=(){Event | where TimeGenerated between(ago(11d) .. ago(10d)) | summarize DT10=dcount(WorkstationName) by TargetUserName};
d1 | join (d2) on TargetUserName | join (d3) on TargetUserName | join (d4) on TargetUserName | join (d5) on TargetUserName | join (d6) on TargetUserName | join (d7) on TargetUserName | join (d8) on TargetUserName | join (d9) on TargetUserName | join (d10) on TargetUserName | extend Avg = ((DT1+DT2+DT3+DT4+DT5+DT6+DT7+DT8+DT9+DT10)/10) | summarize by TargetUserName, Avg, DT1, DT2, DT3, DT4, DT5, DT6, DT7, DT8, DT9, DT10
尝试使用join -kint = ofter。
当没有匹配的情况下，那样，您仍然会从其他日子那里得到行。