SQL Report Efficiency

InsightVM SQL Queries
1

Hi,

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.

2

So one piece of advice would be the way you’re filtering this. Instead of having your initial FROM line be a filtered asset group do the filtering outside of the query but in the actual report builder.

When you filter INSIDE the query, it loads the entire database and THEN filters down to the info you’re looking for. Whereas if you filter in the report builder to that specific asset group first it ONLY loads the assets that you want.

SO essentially start by changing the report scope to only target the asset group you’re looking for and then change that line to simply say

FROM dim_asset_group_asset daga

You can also remove that from the final where statement as well so your where statement would just be

WHERE fa.critical_vulnerabilities > 0 AND dv.severity='Critical'