How to Include AWS Account ID in Vulnerability Findings Reports Using XQL

You want to include the AWS account ID for accounts affected by vulnerabilities in the "All Vulnerability Findings" report. However, when checking the console, the AWS account ID field does not appear among the selectable fields, and default XQL queries on the findings dataset do not return this information.

Environment / Scope

**Product_versions**
• Product: Cortex XDR

• XQL Search
• Vulnerability Management

• Version: Cortex CLOUD V2.1, UI Version: xsiam--master-platform-v5.1.0-9261536-gad8f4486

The AWS account ID information (stored as xdm.asset.realm) is located within the asset_inventory dataset. It is not natively present in the uvm_findings dataset where the vulnerability data resides. Therefore, a join operation is required to correlate the findings with their corresponding AWS account IDs.

To include the AWS account ID in your vulnerability reports, you can run a custom XQL query that joins the uvm_findings dataset with both the asset_inventory and va_cves datasets.

First, verify your schema by running the following query:

dataset = uvm_findings 
| fields * 
| limit 1

Once verified, run the following XQL query to generate the comprehensive report including the AWS account ID (aws_account_id):

dataset = uvm_findings 
| join type = left ( 
    dataset = asset_inventory 
    | fields xdm.asset.id as inv_asset_id, xdm.asset.realm as aws_account_id 
  ) as assets assets.inv_asset_id = asset_id 
| join type = left ( 
    dataset = va_cves 
    | fields Cve_id, Name as cve_title, Type, Severity_score, Severity, Publication_date, Os_type, Modification_date, Is_excluded, Integrity, Impact_score, Exploitability_score, Description, Confidentiality, Availability, Affected_products, Affected_hosts_count, Affected_hosts 
  ) as va va.Cve_id = vulnerability_id 
| fields 
    // Working fields from Joins 
    aws_account_id, cve_title, 
    // Fields from uvm_findings 
    cortex_vulnerability_risk_score, has_issue, vulnerability_id, cvss_severity, cvss_score, epss_score, last_observed, asset_name, asset_type, asset_group_ids, operating_system, affected_software, package_version, file_path, fix_available, fix_date, published_date, cve_publish_date, has_kev, fix_versions, package_in_use, exploitable, exploit_level, cve_risk_factors, location, provider, derived_from_base_image, finding_sources, internet_exposed, cve_description, asset_id, first_observed, asset_category, origin_package_name, package_type, package_purl, package_file_creation_time, package_licenses, package_symbols, package_author, os_family, os_distribution, os_distro_release, ipv4_addresses, ipv6_addresses, source_tags, layer_id, image, is_derived, image_name, volume_asset_ids, volume_path, platform_id, type_id, application_version, asset_type_class, remediation, issue_id, vulnerability_source_asset_id, vulnerability_source_asset

Validation Steps

1. Navigate to the XQL Search interface in your console.

2. Execute the provided XQL query.

3. Review the results to confirm that the aws_account_id column is present and successfully populated for the vulnerability findings.

4. Export or save the query results as a report if needed.

Query tested

dataset = uvm_findings
| join type = left (
dataset = asset_inventory
| fields xdm.asset.id as inv_asset_id, xdm.asset.realm as aws_account_id
) as assets assets.inv_asset_id = asset_id
| join type = left (
dataset = va_cves
| fields Cve_id, Name as cve_title, Type, Severity_score, Severity, Publication_date, Os_type, Modification_date, Is_excluded, Integrity, Impact_score, Exploitability_score, Description, Confidentiality, Availability, Affected_products, Affected_hosts_count, Affected_hosts
) as va va.Cve_id = vulnerability_id
| filter vulnerability_id = "CVE-2005-0406"
| fields
// Working fields from Joins
aws_account_id,
cve_title,

// Massive List of Fields from uvm_findings
cortex_vulnerability_risk_score,
has_issue,
vulnerability_id,
cvss_severity,
cvss_score,
epss_score,
last_observed,
asset_name,
asset_type,
asset_group_ids,
operating_system,
affected_software,
package_version,
file_path,
fix_available,
fix_date,
published_date,
cve_publish_date,
has_kev,
fix_versions,
package_in_use,
exploitable,
exploit_level,
cve_risk_factors,
location,
provider,
derived_from_base_image,
finding_sources,
internet_exposed,
cve_description,
asset_id,
first_observed,
asset_category,
origin_package_name,
package_type,
package_purl,
package_file_creation_time,
package_licenses,
package_symbols,
package_author,
os_family,
os_distribution,
os_distro_release,
ipv4_addresses,
ipv6_addresses,
source_tags,
layer_id,
image,
is_derived,
image_name,
volume_asset_ids,
volume_path,
platform_id,
type_id,
application_version,
asset_type_class,
remediation,
issue_id,
vulnerability_source_asset_id,
vulnerability_source_asset