XQL Question - Aggregation by field of type array is unsupported

[jessecleary]

I'm trying to run a query to get a count of how many times combinations of the values in "initiated_by" and "initiator_path" occur for an alert in the alerts dataset.

For example:

binary1.exe  c:\temp\blah 6
binary2.exe c:\temp\blahblah 12

I'm trying to use this search, but I get the error in the title "Aggregation by initiated_by field of type array is unsupported."

dataset = alerts
| filter alert_name = "Execution From a Restricted Location"
| fields + alert_name, initiated_by, initiator_path
| comp count_distinct(initiator_path) as initiator_path_count by initiated_by


Is there a way I can get a count of the initiated_by and intitiator_path values, or can I only do that in things that are not an "array" in the schema? 

[nsinghvirk]

Hello @jessecleary 

Thanks for reaching out on LiveCommunity!

Yes, the aggregation of data by field type array is not supported. In order to see the unique value count of an array you can use arraydistinct() function. I have a created an example query below. Please modify it according to your use case.

dataset = alerts
| filter alert_name = "Execution From a Restricted Location"
| fields alert_name, initiated_by, initiator_path
| alter xyz= arraydistinct(initiator_path )
| fields alert_name, xyz
| comp count(xyz) by alert_name