Search code examples
letkql

Using KQL 'let' to combine two queries in the same table

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!

Solution

  • 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