Mar 19 2021 09:06 PM
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 Threat Protection")
| where ProviderName == "MDATP"
| mv-expand parsejson(Entities)
|extend Computer = tostring(Entities.HostName)
|summarize dcount(DisplayName) by Computer
|where dcount_DisplayName >= 2
|where Computer <> ""
But I want a table that lists out the Computer AND all of the unique DisplayNames for each Computer.
eg:
Host1 - DisplayName1
DisplayName2
Host2 - DisplayName1
DisplayName2
In Splunk this would simply be:
| stats values(DisplayName) as DisplayName, dc(DisplayName) by host
Thanks for your thoughts.
Mar 22 2021 04:53 AM
@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.
Mar 22 2021 05:05 AM
Mar 22 2021 06:28 AM
@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.
Mar 23 2021 04:59 AM
SolutionYou 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
Mar 23 2021 05:48 AM
@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?
Mar 23 2021 07:41 AM
@CliveWatson , @Gary Bushey - 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.
Mar 23 2021 10:13 AM
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)
Mar 23 2021 11:52 AM
Mar 23 2021 04:59 AM
SolutionYou 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