Forum Discussion

SocInABox's avatar
SocInABox
Iron Contributor
Mar 20, 2021
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 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.

 

 

  • CliveWatson's avatar
    CliveWatson
    Mar 23, 2021

    GaryBushey 

    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
  • GaryBushey's avatar
    GaryBushey
    Bronze Contributor
    Mar 22, 2021

    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.

    • SocInABox's avatar
      SocInABox
      Iron Contributor
      Mar 22, 2021
      Hi Gary,
      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.
      • GaryBushey's avatar
        GaryBushey
        Bronze Contributor
        Mar 22, 2021

        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.

Resources