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 ?
