使用 KQL 'let'将两个查询合并到同一个表中

Question

我正在尝试学习KQL，并有一个查询，我想从Windows事件代码4624(登录)和4634(注销)中获取2个值，并为我仍在尝试构建的不同场景返回它们。

但主要我只是想能够返回表中的值(打印或项目?)

let login = SecurityEvent 
| where TimeGenerated > ago(1h)
| where EventID == '4624'
| project loginTime = TimeGenerated;
let logout = SecurityEvent 
| where TimeGenerated > ago(1h)
| where EventID == '4634'
| project logoutTime = TimeGenerated;
print login

我得到的错误是"项目"操作符:无法解析名为"login"的标量表达式

我希望看到的是:

loginTime           | logoutTime
----------------------------------------------
01/02/2021 18:46:30 | 01/02/2021 18:45:45
01/02/2021 18:47:30 | 01/02/2021 18:47:45
01/02/2021 18:48:30 | 01/02/2021 18:48:45

join会更好吗?它是在同一个表(SecurityEvent)，所以我认为有可能这样做吗?

数据集来自MS提供的Azure门户:https://portal.azure.com/#blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade

谢谢你的帮助!

Answer 1

问题是"login"是一个表类型，但是print期望一个标量类型。

let login = SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == '4624'
| project loginTime = TimeGenerated;
let logout = SecurityEvent 
| where TimeGenerated > ago(1h)
| where EventID == '4634'
| project logoutTime = TimeGenerated;
print toscalar (login)

至于你想要得到的结果，我认为这可能是你需要的:

更新以提高清晰度/perf

let login = SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == '4624'
| project TargetLogonId, loginTime = TimeGenerated;
let logout = SecurityEvent 
| where TimeGenerated > ago(1h)
| where EventID == '4634'
| project TargetLogonId, logoutTime = TimeGenerated;
login
| join kind=leftouter logout on TargetLogonId
| project loginTime, logoutTime

Answer 2

我添加了一些包含@GenericUser和@Slavik-N建议的更改，并带来了我想要计算的信息:

let login = SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == '4624'
| where AccountType == 'User'
| project Computer,Account ,TargetLogonId, loginTime = TimeGenerated;
let logout = SecurityEvent 
| where TimeGenerated > ago(1h)
| where EventID == '4634'
| where AccountType == 'User'
| project Computer,Account,TargetLogonId, logoutTime = TimeGenerated;
login
| join kind=inner logout on TargetLogonId
| project Computer,Account,loginTime, logoutTime, minute = datetime_diff('minute',logoutTime,loginTime)
| where minute >0
| sort by minute desc