Hi all,
I built a query to search for critical vulnerabilities where the number of affected assets is either 1 or 2. Even after verifying and testing the query, it seems that it’s taking a very long time to run, it several hours and still didn’t complete.
As well as stating the Site ID in the query, I also set the Site and Vulnerability filters in the Scope section of the report. The site has just under 3.5K of assets.
Can I make this anymore efficient in the hopes it runs faster?
SELECT DISTINCT
dv.title AS "Title", dv.malware_kits AS "Malware", dv.exploits AS "Exploits", dv.cvss_score AS "CVSS", dv.riskscore AS "Risk", dv.severity_score AS "Severity", fv.affected_assets AS "Instances", da.ip_address AS "IP Address", da.host_name AS "Hostname"
FROM
dim_vulnerability dv
JOIN fact_vulnerability AS fv ON fv.vulnerability_id=dv.vulnerability_id
JOIN fact_asset_scan_vulnerability_finding AS fasvf ON fasvf.vulnerability_id=dv.vulnerability_id
JOIN dim_asset AS da ON da.asset_id=fasvf.asset_id
JOIN dim_site_asset AS dsa ON dsa.asset_id=da.asset_id
WHERE
dsa.site_id=90 AND dv.severity='Critical' AND fv.affected_assets BETWEEN 1 and 2
ORDER BY
dv.title ASC
LIMIT
250;