How to add vulnerability proof to SQL query

InsightVM SQL Queries
,
1

I’m wanting to add a column for vulnerability proof which I know comes from the table fact_asset_vulnerability_instance, however I’m not very good at SQL and don’t know what I’m doing. Could someone please offer some assistance?

SELECT DISTINCT da.ip_address, da.host_name, nexpose_id, title, proofAsText(dv.description) AS description, date_published, cvss_vector, severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, dos.asset_type,dos.vendor,dos.name
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da using (asset_id)
JOIN dim_vulnerability dv using (vulnerability_id)
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
LEFT OUTER JOIN dim_tag_asset dta ON dta.asset_id = favf.asset_id
LEFT OUTER JOIN dim_tag dt ON dt.tag_id = dta.tag_id
WHERE dt.tag_name <> ‘RBC’
ORDER BY title ASC
–LIMIT 10

2

Off the top of my head without going into testing this should do it

SELECT DISTINCT da.ip_address, da.host_name, nexpose_id, title, proofAsText(dv.description) AS description, date_published, cvss_vector, severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, dos.asset_type, dos.vendor, dos.name, favi.proof
FROM fact_asset_vulnerability_finding favf
JOIN fact_asset_vulnerability_instance favi using (asset_id)
JOIN dim_asset da using (asset_id)
JOIN dim_vulnerability dv using (vulnerability_id)
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
LEFT OUTER JOIN dim_tag_asset dta ON dta.asset_id = favf.asset_id
LEFT OUTER JOIN dim_tag dt ON dt.tag_id = dta.tag_id
WHERE dt.tag_name <> ‘RBC’
ORDER BY title ASC
3

I ran it and am getting this error: “common column name “vulnerability_id” appears more than once in left table”

Not sure what that’s about.

4

basically both of those fact tables have a vulnerability_id column so we can’t join with a USING clause.

SELECT DISTINCT da.ip_address, da.host_name, nexpose_id, title, proofAsText(dv.description) AS description, date_published, cvss_vector, severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, dos.asset_type, dos.vendor, dos.name, favi.proof
FROM fact_asset_vulnerability_finding favf
JOIN fact_asset_vulnerability_instance favi ON favf.asset_id=favi.asset_id AND favf.vulnerability_id=favi.vulnerability_id
JOIN dim_asset da ON favf.asset_id=da.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id=dv.vulnerability_id
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
LEFT OUTER JOIN dim_tag_asset dta ON dta.asset_id = favf.asset_id
LEFT OUTER JOIN dim_tag dt ON dt.tag_id = dta.tag_id
WHERE dt.tag_name <> ‘RBC’
ORDER BY title ASC
5

Thanks for your help on this. I was able to tweak this to get it to work.

1 Like