I am trying to learn KQL and had a query where I wanted to take 2 values from Windows Event codes 4624 (login) and 4634 (logout) and return them for different scenarios I'm still trying to build.
But primarily I would just like to be able to return the values in a table (print or project?)
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
The error I am getting is "'project' operator: Failed to resolve scalar expression named 'login'"
What I would have hoped to see is:
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
Would a join be better? It is in the same table (SecurityEvent), so I thought it would be possible to do it this way?
The dataset is from the MS provided Azure portal: https://portal.azure.com/#blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade
Thanks for the help!
The problem is that "login" is a table type but print is expecting a scalar type.
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)
As to the result you are trying to get, I think this might be what you need:
Updated to improve clarity/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