Group By Host in Filtered XQL Query

[tporritt]

What I want to do is generate a report showing the filtered list of CVEs on each host, multiple CVEs would show up in a single field concatenated with commas or semi-colons

dataset = va_endpoints
|arrayexpand cves
| filter CVES in (
"CVE-2019-2725", "CVE-2018-7600", "CVE-2021-44228", "CVE-2019-1653, <75 more CVEs>
)

So the current output looks like this (shortened):
endpoint_name host_count cve_name
host1 3 CVE-2019-2725
host2 3 CVE-2019-2725
host3 3 CVE-2019-2725
host2 2 CVE-2018-7600
host3 2 CVE-2018-7600
host3 1 CVE-2021-44228

It produces a report that lists each cve/endpoint combination, I was hoping to have the endpoint with the CVEs in a single field.
I see another thread (here) that says to use comp count but when I use it as "comp count(CVES) as Total_CVE_per_host by endpoint_name, CVES" I get one line per host/cve combination
I don't see how I can concatenate all the CVEs into a single field. Would the 'addrawdata' somehow be the answer?

Something like this (I could even give up the counts and just do host/cves):
endpoint_name cve_name
host1 CVE-2019-2725
host2 CVE-2019-2725,CVE-2018-7600
host3 CVE-2019-2725,CVE-2018-7600,CVE-2021-44228

[aspatil · Accepted solution]

Hello @tporritt ,

Thanks for reaching out on LiveCommunity!

You can simply use below command as a sample:

dataset = va_endpoints
| arrayexpand cves
|filter cves in("CVE-2022-3515","CVE-2022-3491","CVE-2022-34903")
|comp list(cves) by endpoint_name

If you feel this has answered your query, please let us know by clicking on "mark this as a Solution". Thank you.