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
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.5 Just 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.
I wish we had a channel just for showing hundreds of kql -> viz/output
Very educational.