Help with SQL Query

InsightVM SQL Queries
,
1

I am looking to get a list of the assets along with the count of vulnerabilities associated with them. I have the following query:

SELECT da.host_name,da.ip_address,COUNT(fa.vulnerabilities)
FROM dim_asset da
JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id
JOIN dim_site ds ON ds.site_id = dsa.site_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = favi.vulnerability_id
JOIN fact_asset fa ON da.asset_id = fa.asset_id
WHERE (ds.name = ‘External Assets >= 4’)
GROUP BY da.host_name,da.ip_address

I have tried added in a LEFT JOIN on the dim_vulnerability table and this did not work

This will show assets and the count, however, we have some assets that have 0 vulnerabilities. I need to also account for those assets as well

2

@chris_woolard Try modifying this line to use the LEFT JOIN

LEFT JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id

3

That did not work. It is not showing the assets with zero vulnerabilities. Below is the search just to make sure I got your comment right.

SELECT da.host_name,da.ip_address,COUNT(fa.vulnerabilities)
FROM dim_asset da
JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id
JOIN dim_site ds ON ds.site_id = dsa.site_id
LEFT JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = favi.vulnerability_id
JOIN fact_asset fa ON da.asset_id = fa.asset_id
WHERE (ds.name = ‘External Assets >= 4’)
GROUP BY da.host_name,da.ip_address

4

Hi @chris_woolard,

Could you see if the following works for you? I rearranged the joins so that the LEFT JOINs are at the end and added an additional on the dim_vulnerability table since we’ll want assets where that join doesn’t have matches as well.

SELECT da.host_name,da.ip_address, fa.vulnerabilities
FROM dim_asset da
JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id
JOIN dim_site ds ON ds.site_id = dsa.site_id
JOIN fact_asset fa ON da.asset_id = fa.asset_id
LEFT JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
LEFT JOIN dim_vulnerability dv ON dv.vulnerability_id = favi.vulnerability_id
WHERE (ds.name = ‘External Assets >= 4’)
GROUP BY da.host_name,da.ip_address
5

That is more accurate in the fact, that now it comes back with all the assets as well as a count. However, for the assets listed in the site with “0” vulnerabilities it shows as a 1 on this report after I run this query.

6

Ahh yes, I should have caught this. I think that you want to remove the COUNT call from fa.vulnerabilities since the fa.vulnerabilities is a count of vulnerabilities on the asset itself, so in this case the COUNT call is just counting the number of rows returned for that asset (which would be 1). If you’re just looking to get the counts of vulnerabilities, you can remove the LEFT JOINs entirely since the other tables have everything that you need. If you’re going to expand on this by adding vulnerability details, then it makes sense to keep those.

I’ve updated the above query to remove COUNT.

7

This was perfect. Thank you!

1 Like