Sql Query to fetch data from Postgresql directly

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%’

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

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.

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!


1 Like

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!