Forum Discussion
Solved
KQl leftanti join query
I need to verify if my devices are having the security tools installed. One way of doing it I am thinking of is running KQL query on BehaviourAnalytics logs to extract user list who signed in last 24...
- You'd need to tell the join which columns to compare, like this:
BehaviorAnalytics
| where TimeGenerated >= ago(1d)
| where DevicesInsights !has "zscaler" and ActionType == 'Sign-in'
| summarize count() by UserName
| join kind=leftanti
(
CommonSecurityLog
| where TimeGenerated >= ago(1d)
| summarize count() by UserName_CS
) on $left.UserName == $right.UserName_CS
You'd need to tell the join which columns to compare, like this:
BehaviorAnalytics
| where TimeGenerated >= ago(1d)
| where DevicesInsights !has "zscaler" and ActionType == 'Sign-in'
| summarize count() by UserName
| join kind=leftanti
(
CommonSecurityLog
| where TimeGenerated >= ago(1d)
| summarize count() by UserName_CS
) on $left.UserName == $right.UserName_CS
BehaviorAnalytics
| where TimeGenerated >= ago(1d)
| where DevicesInsights !has "zscaler" and ActionType == 'Sign-in'
| summarize count() by UserName
| join kind=leftanti
(
CommonSecurityLog
| where TimeGenerated >= ago(1d)
| summarize count() by UserName_CS
) on $left.UserName == $right.UserName_CS