Using stats count by to query the number of policies?

[soulmaker24]

Hello,

I am trying to figured out how I could list a report by showing the total number of policies in my query. 

I have the sample Event below:

{ [-]
  auth : { [-]
    display_name: sample-name
    policies: [ [-]
      default
      admin
    ]
  }
  type: request
}

So, when I am using a search query below, I got a result of number of display_name.

type="request" | stats count by auth.display_name

However,  what I need is to show me the result count of the policies which in this case the default and admin. I am using the query below but it does not give me any result.

type="request" | stats count by auth.policies

Would someone be able to guide me what is the correct syntax to use to get the result I want?

[yeahnah · Accepted solution]

Hi @soulmaker24 

The auth.policies{} field is array, so in this case, results in a multi value field.  For stats command to group by that field it needs to be a single value which can be done use the mvexpand command, like this...

type="request"
| mvexpand auth.policies{}
| stats count BY auth.policies{}

 Also, for next time, showing the event is really useful, it is more useful if you add it with syntax highlighting turned off - basically the _raw event.

Hope this helps

[soulmaker24]

Thank you, I did realise I am missing the {} at the end. Appreciate your help on this one.