I have the following SQL query below but it takes several hours to complete and with larger groups even longer:
SELECT da.host_name AS hostname,da.ip_address AS "ip address",fa.scan_finished AS "last scanned",fa.critical_vulnerabilities AS "no. of vulnerabilities", dos.name AS os,dv.title AS "vulnerability name",dv.description AS "vulnerability description",dvbs.vulnerability_id AS "vulnerability id",dv.cvss_score AS "CVSS Score",ds.summary AS "Solution",dv.date_added AS "Date Seen" FROM (SELECT * from dim_asset_group_asset WHERE dim_asset_group_asset.asset_group_id=40) as daga join fact_asset as fa on fa.asset_id=daga.asset_id join dim_asset as da on da.asset_id=fa.asset_id join dim_operating_system as dos on dos.operating_system_id=da.operating_system_id join dim_asset_vulnerability_best_solution as dvbs on dvbs.asset_id=da.asset_id join dim_vulnerability as dv on dv.vulnerability_id=dvbs.vulnerability_id join dim_solution as ds on ds.solution_id=dvbs.solution_id WHERE daga.asset_group_id = 40 AND fa.critical_vulnerabilities > 0 AND dv.severity='Critical' ORDER BY da.ip_address
Joins and the various types are not my strong suit, if someone can help me make the query run faster that would be awesome.
I’ve also been struggling adding the first_discovered field from the * fact_asset_vulnerability_age* table to the query, as when I do, it simply fails to even Preview.