Sql Query to fetch data from Postgresql directly

InsightVM SQL Queries
1

I wrote this query to collect Nexpose data from the postrgresql database. Can someone look it over for errors. Appreciate any feedback.
SELECT DISTINCT dim_vulnerability.title, dim_solution.summary, dim_solution.Fix, dim_asset.asset_id, dim_asset.ip_address, dim_asset.host_name, dim_asset.mac_address, dim_site.name, dim_asset.os_name, dim_asset_service.service, dim_asset_service.port, dim_vulnerability.severity, dim_vulnerability.cvss_score, dim_vulnerability.date_published, dim_vulnerability.description, fact_asset_vulnerability_instance.proof, dim_vulnerability.vulnerability_id, dim_vulnerability.cvss_vector, dim_vulnerability.cvss_v3_vector, dim_vulnerability_reference.reference
FROM dim_asset_service
JOIN dim_site
JOIN dim_site_asset
JOIN dim_vulnerability_solution
JOIN dim_asset
JOIN fact_asset_vulnerability_instance ON dim_asset.asset_id = fact_asset_vulnerability_instance.asset_id
JOIN dim_vulnerability ON fact_asset_vulnerability_instance.vulnerability_id = dim_vulnerability.vulnerability_id ON dim_vulnerability_solution.vulnerability_id = fact_asset_vulnerability_instance.vulnerability_id
JOIN dim_solution ON dim_vulnerability_solution.solution_id = dim_solution.solution_id ON dim_site_asset.asset_id = dim_asset.asset_id ON dim_site.site_id = dim_site_asset.site_id ON dim_asset_service.asset_id = dim_asset.asset_id
JOIN dim_vulnerability_reference ON dim_vulnerability.vulnerability_id = dim_vulnerability_reference.vulnerability_id
WHERE dim_vulnerability_reference.source Like ‘%CVE%’

2

@cherren - What errors are you getting and/or concerns do you have?

3

I’m not getting any errors. I was just wondering if someone has any suggestions about the query or can QA the logic for me.

4

Hmm. Okay. Looking at the logic, I’m not confident this will return any information within your environment. Can you confirm?

Here is some feedback:

  • Depending on the results needed, it’s typically best practice to start with a fact table. In your case, replace [dim_asset_service] with [fact_asset_vulnerability_instance] as your FROM table. From there, I would reference the warehouse schema and ensure your joins make sense. Maybe it’s the way your query pasted, but you have some interesting joins on your [dim_vulnerability] and [dim_solution]

  • Is your goal with the [dim_vulnerability_reference] table and the WHERE clause WHERE dim_vulnerability_reference.source Like '%CVE%' to only return vulns with a known CVE? If yes, then I would remove the WHERE clause and just make your join this JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id AND dvr.source = 'CVE' If no, then use the same logic but change to a LEFT JOIN.

  • Bonus Tip: I’ve notice there are CVEs within the nexpose_id title, but not in the reference table. If you would like a more complete list you can replace your reference column with the following logic. ,CASE WHEN dvr.reference IS NULL AND dv.nexpose_id LIKE '%cve-%' THEN UPPER(RIGHT(dv.nexpose_id,LENGTH(dv.nexpose_id) - (POSITION('cve-' IN dv.nexpose_id) -1))) ELSE dvr.reference END AS cve

Feel free to share if you make any changes. Hope this helps!

Bob

2 Likes
5

Thanks Bob for reviewing the query. It was my first pass at it, and there were results, although questionable. I will try out your suggestions to see what I get. Thanks!