KQL query for non-matching entries

Brass Contributor

Hi,

 

I am trying to write a query to find out the devices that does not have MDE installed by comparing it with those devices found in CommonSecurity Table. The actual no. of devices is approx. 50, however I want to write a query to do this

 

When tried  leftanti/leftantisemi the no. of devices it shows in the result is incorrect. (approx. 1000)

 

let mde_devices= materialize (
DeviceInfo
| where TimeGenerated >= ago(7d)
| where OSPlatform !has 'server' 
| summarize arg_max(TimeGenerated, PublicIP, LoggedOnUsers) by DeviceName
| parse-kv LoggedOnUsers as ( ['UserName']:string) with (  pair_delimiter=",", kv_delimiter=":", quote='"')
| where isnotempty(UserName)
| extend HostName = toupper(trim_end(char_trim,DeviceName))
| distinct HostName
);
let zscaler_devices = (
CommonSecurityLog
| where TimeGenerated >= ago(7d)
| where DeviceProduct == 'NSSWeblog'
| where AdditionalExtensions !in~ (mde_devices)
| parse AdditionalExtensions with * 'SourceTranslatedAddress=' IPAddress ';UserAgent=' * ';User=' UserName_CS ';Department=' * 'DeviceHostname=' Hostname_CS ';DeviceModel=' *
| summarize arg_max(TimeGenerated,UserName_CS, IPAddress) by Hostname_CS
| distinct Hostname_CS
| extend HostName = tolower(Hostname_CS)
| project-away Hostname_CS);
zscaler_devices
//| where HostName in (mde_devices)
| join kind=leftanti mde_devices on HostName// == $right.HostName

 

So, I switched to using not() (refer line 14) and it is the closest match it's output still shows devices that has MDE.  It lists 80 devices as the output and the extra 30 its showing is having MDE

 

let mde_devices= materialize (
DeviceInfo
| where TimeGenerated >= ago(7d)
| where OSPlatform !has 'server'
| summarize arg_max(TimeGenerated, PublicIP, LoggedOnUsers) by DeviceName
| parse-kv LoggedOnUsers as ( ['UserName']:string) with (  pair_delimiter=",", kv_delimiter=":", quote='"')
| where isnotempty(UserName)
| extend HostName = toupper(trim_end(char_trim,DeviceName))
| distinct HostName
);
CommonSecurityLog
| where TimeGenerated >= ago(7d)
| where DeviceProduct == 'NSSWeblog'
| where not(AdditionalExtensions has_any (mde_devices))
| parse AdditionalExtensions with * 'SourceTranslatedAddress=' IPAddress ';UserAgent=' * ';User=' UserName_CS ';Department=' * 'DeviceHostname=' Hostname_CS ';DeviceModel=' *
| summarize arg_max(TimeGenerated,UserName_CS, IPAddress) by Hostname_CS
| extend HostName = tolower(Hostname_CS)
| project-away Hostname_CS

 

 

My question here are:

1. Although anti/semianti is supposed to give me the intended results, what is wrong with my query ?

2. While using the not() operator, what additional step I should carry out to make sure my results do not consist of devices having MDE ?

 

 

2 Replies

@abon13 

 

Have you tried to simplify the query?   Something based on this?

 

let mde_devices= 
DeviceInfo
| where TimeGenerated >= ago(7d)
| where OSPlatform has 'server'
| distinct DeviceName
;
Syslog
| where TimeGenerated >= ago(7d)
| where Computer !in (mde_devices)
// Only display devices that are not MDE 
| distinct Computer

 

The leftanti is working now after the query was simplified