SQL Help with needing everything counted for Depts Asset groups or Just any Asset group

Hi, I’m looking for a way to include eveything i have for ANY asset groups that have cvss 8 or greater and that are 30 days passed scanning. Here is the SQL: I Have dept_ named but i really need everything that is in departmental groups I have named most of the DEPTs with the workd Dept_ thats why i tried using that.

SELECT dag.name AS “Department”, COUNT (DISTINCT fava.asset_id) AS “Asset Count”

FROM fact_asset_vulnerability_age fava
JOIN dim_asset_group_asset daga ON fava.asset_id = daga.asset_id
JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id
JOIN dim_vulnerability dv ON fava.vulnerability_id = dv.vulnerability_id

WHERE (dag.name LIKE ‘Dept_%’)
AND dv.cvss_v2_score >= 8
AND fava.age_in_days > 30

GROUP BY dag.name

ORDER BY dag.name ASC

Hi Vanessa,

The logic for finding the assets looks sound. However, fava.age_in_days might not be the right way to find assets that haven’t been scanned in 30 days. That field reports on when the vulnerability was first discovered on an asset. You might want to swap that part of your criteria out for dim_asset.last_assessed_for_vulnerabilities.

I hope this helps!

I would try this…without limiting the asset group results. You can always filter those out in the CSV output.

SELECT dag.name AS "Department", COUNT (DISTINCT da.asset_id) AS "Asset Count"
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_asset_group_asset daga USING (asset_id)
JOIN dim_asset_group dag USING (asset_group_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
WHERE dv.cvss_v2_score >= 8
AND da.last_assessed_for_vulnerabilities > (NOW() - INTERVAL '30 days')
GROUP BY dag.name
ORDER BY dag.name ASC
1 Like

Thank you this helped!!