Fetch Vulnerabilities Belonging to Specific Site using SQL Query

Hi Folks,

I’m a fresher in SQL queries.

I am using the below SQL query to fetch all the vulnerabilities of the assets belonging to a specific site. I am getting errors though I tried making corrections.

SELECT ds.name AS site, da.ip_address, da.host_name, dv.title AS vulnerability_title, dsite.name AS “Site Name”
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)

Where dsite.name = ‘Servers for Audit’ AND dv.title LIKE ‘%Java%’

ORDER BY da.ip_address ASC, dv.title ASC

I think the main issue with this query is the use of dsite in a couple places. I updated that to ds to reference the dim_site table, and it looks to be running now. :slight_smile:

SELECT ds.name AS site, da.ip_address, da.host_name, dv.title AS vulnerability_title,
       ds.name AS "Site Name"
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
Where ds.name = 'Servers for Audit' AND dv.title LIKE '%Java%'
ORDER BY da.ip_address ASC, dv.title ASC

This will potentially return a lot of vulnerabilities, so if you want, you can also further filter the vulns by something like severity. Adding this statement after your “where” would do that.

AND dv.severity = 'Critical'
1 Like