Forum Discussion
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 hours and compare with userlist of CommonSecurity table.
In the comparison output I need to list those usernames which are not found in CommonSecurity table. This will tell me which users do not have the tool installed on their systems.
From my understanding leftanti join query is helpful, but stuck on it.
In the below query, I want the comparison check to be done between Username from BehaviourAnalytics table and UserName_CS from CommonSecurity table, and give the non-matching entries from UserName table only.
Looking for suggestions on how to proceed further
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))
- 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
1 Reply
- 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