Can I filter on hostnames in an array?

[edvardgooijenga]

I'm running the following script, it should display the critical vulnerabilities on MacOS systems.

//List critical vulnerabilities on all MacOS endpoints
config case_sensitive = false
| dataset = va_cves
| filter os_type = ENUM.MACOS and severity = ENUM.CRITICAL
| fields severity,name,description,affected_products,type,severity_score,os_type,affected_hosts_count,affected_hosts,modification_date,publication_date,exploitability_score
| sort desc severity_score

The problem is that the affected_hosts array also contains Windows systems that are affected by the same CVE's.
How can I filter so it only reports MacOS systems?
All our Macbook names start with "MBP-" but I was not able to filter on that , so far.

[edvardgooijenga]

 was able to sort it out a bit more.

dataset = va_cves
| filter os_type contains "*MAC*" and severity = ENUM.CRITICAL
| arrayexpand affected_hosts
| filter affected_hosts contains "MBP-*"
| arrayexpand os_type
| filter os_type contains "*MAC*"
//| alter abc = json_extract(affected_hosts ,"$.version")
| fields severity,name,description,affected_products,type,severity_score,os_type,affected_hosts_count,affected_hosts,modification_date,publication_date,exploitability_score
| sort desc severity_score, desc name

This returns Macbooks only but hostname_count field needs work and affected_products also.
Would be even nicer to have one row with all the Macbook name for each CVE instead of each Macbook having its own row.

[edvardgooijenga]

No, Palo Alto.
My own reply did not fix the problem.

[susekar]

Hello @edvardgooijenga ,

Greetings for the day.

To filter the affected_hosts array to only include MacOS systems (specifically those starting with "MBP-"), you must use array-processing functions. In the va_cves dataset, a single row representing a CVE may include hostnames from multiple operating systems within its array fields, even if a dataset-level filter is applied to the row itself.

There are two primary ways to achieve this in Cortex Query Language (XQL).

Option 1: Clean the Array (Keep One Row per CVE)

Use the array_filter function to remove non-matching hostnames from the array before displaying the results. This is ideal if you want a summary report where each CVE appears once, but the list of hosts only shows your Macbooks.

For prefix matching, use the regex match operator (~=) with the start anchor (^).

// List critical vulnerabilities on MacOS endpoints starting with "MBP-"
config case_sensitive = false
| dataset = va_cves
| filter os_type = ENUM.MACOS and severity = ENUM.CRITICAL

// Re-calculate the array to only include hostnames starting with "MBP-"
| alter affected_hosts = array_filter(affected_hosts, (h) => h ~= "^MBP-")

// Update the count to reflect the filtered array
| alter affected_hosts_count = array_length(affected_hosts)

// Optional: Filter out any CVEs that have no remaining hosts after the filter
| filter affected_hosts_count > 0

| fields severity,name,description,affected_products,type,severity_score,os_type,affected_hosts_count,affected_hosts,modification_date,publication_date,exploitability_score
| sort desc severity_score

Option 2: Expand the Array (One Row per Macbook):

Use the arrayexpand function to transform each element of the affected_hosts array into its own individual record. This is the best approach if you need a detailed list of every vulnerable Macbook.

// List one row per Macbook for critical vulnerabilities
config case_sensitive = false
| dataset = va_cves
| filter os_type = ENUM.MACOS and severity = ENUM.CRITICAL

// Flattens the array so each host is a separate row
| arrayexpand affected_hosts

// Filter hostnames using triple double quotes for prefix wildcard search
| filter affected_hosts = """MBP-*"""

| fields severity,name,description,affected_products,type,severity_score,os_type,affected_hosts,modification_date,publication_date,exploitability_score
| sort desc severity_score

Supported String Matching for Prefixes:

When filtering for hostnames starting with a specific string like "MBP-", XQL supports the following syntax:

• Regex Match (~=) :  Use ~= "^MBP-" to match the start of the string.
• Wildcard Prefix Search: Use triple double quotes with an asterisk, such as = """MBP-*""".
• Containment: The CONTAINS operator can be used, but it matches the string anywhere (for example, "OLD-MBP-01" would also match).

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

Thanks & Regards,
S. Subashkar Sekar