XQL Help - Count array values

[MatthieuFouet]

Hello,

XQL beginner here, after 1 hour of tryhard with the cheap xql palo alto documentation. Finally got what I want but I feel like I've done it the wrong wa

Context 

• I have a BIOC rule monitoring for read on multiple folders that can contains identical files.

Target

• The list of the top file that have been read

Example 

• Monitored file path 
  • C:\users\jhon\
  • C:\users\doe\
• Example file opened
  • C:\users\jhon\test.txt
  • C:\users\doe\test.txt
  • C:\users\doe\also_test.txt

Desired output

File	Hits
test.txt	2
also_test.txt	1

My take 

dataset = alerts 
| filter rule_id = "whatever"
| fields file_name
| alter file = arraystring(arraydistinct(file_name),",")
| comp count(file_name) as hits by file

Any suggestions ?

[zarnous · Accepted solution]

hi @MatthieuFouet 

I hope i got the use correctly, and based on my understadning, you may try the below query 


dataset = alerts
| fields rule_id , file_name , file_sha256
| arrayexpand file_name
| comp count(file_name ) as Hits by file_name
| sort desc Hits

Sample output should look like the below 

You may also add more fields, and filters as you wish if you need so!

If this answers your question feel free to mark it as a solution so other can benefit from!

Thanks
Z