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
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
CliveWatson , 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.