Forum Discussion
kql query for distinct values
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 While you can write the code to display the information like you want it using some trick IF commands, are you sure you would want the output that way. If you need to do any sorting the 2nd line would not sort with the 1st line as it doesn't have the computer name in it.
If this data is being shown somewhere else, maybe that system could handle the removing of the host name as needed.
I'm not sure from your reply what you don't understand.
I just want to group all values from field 2 based on field 1.
As I've shown this is a no brainer in splunk.
If you have some kql examples of this it would be much appreciated.
Thank you.
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 ComputerCliveWatson , GaryBushey - THANK YOU!
This is incredibly helpful to me for detecting attackers who have used a variety of exploits on a single host.
I see this pattern all the time on waf, ids, endpoint and it's almost always something interesting.
I just have to change the threshold of dcount(DisplayName) to whatever number I like (usually 3 or higher).
If you have more 'threat' detection type queries I'd LOVE to see them.