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 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.You could maybe add some anomaly detection as well?
// https://docs.microsoft.com/en-us/azure/data-explorer/anomaly-detection#time-series-anomaly-detection // Anomaly scores above 1.5 or below -1.5 indicate a mild anomaly rise or decline respectively. // Anomaly scores above 3.0 or below -3.0 indicate a strong anomaly. SecurityAlert | where ProductName in("Microsoft Defender Advanced Threat Protection") | where ProviderName == "MDATP" | make-series Trend = count() on TimeGenerated from startofday(ago(90d)) to startofday(ago(0d)) step 1d by DisplayName | extend (anomalies, score, baseline) = series_decompose_anomalies(Trend, 1.5, -1, 'linefit', 1, 'ctukey', 0.6) | extend expectedEventCounts=baseline, actualEventCount=Trend, Score = score[-1] | where Score > 1.5 or Score < -1.5Just comment out the last line or alter it to show what ever anomaly level your are happy with - this will probably needs some tweaking for your use.
These type of queries, display very nicely in a Azure Workbook (taken from my Workspace Usage report, in the Azure Sentinel Workbooks blade and Github)- I like it very much, thanks @clive!
I wish we had a channel just for showing hundreds of kql -> viz/output
Very educational.
CliveWatson Much better looking code than mine. How would you do the part where the author only wants those DisplayNames that show up at least twice? Is it just a matter of setting the dcount(DisplayName) to a variable and then checking that there is at least 2 after that?
