Forum Discussion
Solved
kql query for distinct values
Hi there, I'm trying to query all computers that match 2 or more DISTINCT DisplayName fields. I can get the distinct count: SecurityAlert | where ProductName in("Microsoft Defender Advanced Threa...
You might also try?
SecurityAlert | where ProductName in("Microsoft Defender Advanced Threat Protection") | where ProviderName == "MDATP" | mv-expand parsejson(Entities) | extend Computer = tostring(Entities.HostName) | where isnotempty(Computer) | summarize dcount(DisplayName), make_set(DisplayName) by Computer
SocInABox So do you care if Hist shows in Rows 1 and 2? If that is not an issue then after you get your host and your displayName, you can concatenate (using the strcat command) and then perform another distinct on the concatenated string.
SecurityAlert
| where ProductName in("Microsoft Defender Advanced Threat Protection")
| where ProviderName == "MDATP"
| mv-expand parsejson(Entities)
|extend Computer = tostring(Entities.HostName)
|where Computer <> ""
|summarize dcount(DisplayName) by Computer
|where dcount_DisplayName >= 2
| extend hostdisplay = strcat(Computer," - ",DisplayName)
| distinct hostdisplay
Hope this is what you are looking for.
You might also try?
SecurityAlert
| where ProductName in("Microsoft Defender Advanced Threat Protection")
| where ProviderName == "MDATP"
| mv-expand parsejson(Entities)
| extend Computer = tostring(Entities.HostName)
| where isnotempty(Computer)
| summarize dcount(DisplayName), make_set(DisplayName) by Computer