Data warehouse sql query check

[henryaj]

Can someone check the SQL query that I wrote for my data warehouse? I’m trying to match it to a report that is ran in the console.

Here is the query:

SELECT
da.host_name AS “Asset Names”,
da.ip_address AS “Asset IP Address”,
da.os_family AS " Asset OS Family",
da.os_name AS “Asset OS Name”,
da.os_version AS “Asset OS Version”,
to_char(round(fa.risk_score::numeric,0),‘999G999G999’) AS “Asset Risk Score”,
da.sites AS “Site Name”,
da.last_assessed_for_vulnerabilities AS “Vulnerability Test Date”,
dv.nexpose_id AS “Vulnerability ID”,
dv.title AS “Vulnerability Title”,
dv.description AS “Vulnerability Description”,
dv.date_published AS “Vulnerability Publish Date”,
favi.proof AS “Proof”,
dv.cvss_score AS “Vulnerability CVSS Score”,
dv.severity_score AS “Vulnerability Severity Level”,
dv.exploits AS “Exploit Count”,
dvr.reference AS “Reference ID”,
ds.summary AS “Vulnerability Solution”,
favi.service AS “Service Name”,
favi.port AS “Service Protocol”,
favi.protocol AS “Service Port”,
dv.exploit_skill_level AS “Exploit Minimum Skill”

FROM
dim_asset da
JOIN fact_asset fa USING (asset_id)
JOIN fact_asset_vulnerability_instance favi USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_reference dvr USING (vulnerability_id)
JOIN dim_solution ds USING (nexpose_id)

Here are the columns I need (these are all based off of Rapid7’s template as shown here Report templates and sections | Nexpose Documentation):

Asset Names
Asset IP Address
Asset OS Family
Asset OS Name
Asset OS Version
Asset Risk Score
Site Name
Vulnerability Test Date (same as last scan date)
Vulnerability CVE IDs
Vulnerability ID
Vulnerability Title
Vulnerability Description
Vulnerability Tags
Vulnerability Age
Vulnerable Since
Vulnerability Publish Date
Vulnerability Proof
Vulnerability Risk Score
Vulnerability CVSS Score
Vulnerability Severity Level
Exploit Count
Vulnerability Reference IDs
Vulnerability Solution
Service Name
Service Protocol
Service Port
Exploit Minimum Skill
Vulnerability Test Result Code
Vulnerability Test Result Description

[henryaj]

This is an updated version:

SELECT
da.host_name AS “Asset Names”,
da.ip_address AS “Asset IP Address”,
da.os_family AS " Asset OS Family",
da.os_name AS “Asset OS Name”,
da.os_version AS “Asset OS Version”,
to_char(round(fa.risk_score::numeric,0),‘999G999G999’) AS “Asset Risk Score”,
da.sites AS “Site Name”,
to_char(da.last_assessed_for_vulnerabilities, ‘MM/DD/YYYY’) AS “Vulnerability Test Date”,
dv.nexpose_id AS “Vulnerability ID”,
dv.title AS “Vulnerability Title”,
dv.description AS “Vulnerability Description”,
to_char(dv.date_published, ‘MM/DD/YYYY’) AS “Vulnerability Publish Date”,
favi.proof AS “Proof”,
dv.cvss_score AS “Vulnerability CVSS Score”,
dv.severity_score AS “Vulnerability Severity Level”,
dv.exploits AS “Exploit Count”,
dvr.reference AS “Reference”,
dvr.source AS “CVE ID”,
ds.summary AS “Vulnerability Solution”,
favi.service AS “Service Name”,
favi.port AS “Service Protocol”,
favi.protocol AS “Service Port”,
dv.exploit_skill_level AS “Exploit Minimum Skill”,
to_char(favi.date, ‘YYYY-MM-DD’) AS “Vulnerable Since”,
to_char(round(dv.risk_score::numeric,0),‘999G999G999’) AS “Vulnerability Risk Score”,
date_part(‘day’::text, ‘now’::text::timestamp without time zone - favi.date) AS “Vulnerability Age”

FROM
dim_asset da
JOIN fact_asset fa USING (asset_id)
JOIN fact_asset_vulnerability_instance favi USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_reference dvr USING (vulnerability_id)
LEFT JOIN dim_solution ds USING (nexpose_id)

WHERE
dvr.source = ‘CVE’::text OR dvr.source = ‘NVD’::text

[bradpjaxx]

@henryaj

SELECT
da.host_name AS “Asset Names”,
da.ip_address AS “Asset IP Address”,
da.os_family AS " Asset OS Family",
da.os_name AS “Asset OS Name”,
da.os_version AS “Asset OS Version”,
to_char(round(fa.risk_score::numeric,0),‘999G999G999’) AS “Asset Risk Score”,
da.sites AS “Site Name”,
to_char(da.last_assessed_for_vulnerabilities, ‘MM/DD/YYYY’) AS “Vulnerability Test Date”,
dv.nexpose_id AS “Vulnerability ID”,
dv.title AS “Vulnerability Title”,
dv.description AS “Vulnerability Description”,
to_char(dv.date_published, ‘MM/DD/YYYY’) AS “Vulnerability Publish Date”,
favi.proof AS “Proof”,
dv.cvss_score AS “Vulnerability CVSS Score”,
dv.severity_score AS “Vulnerability Severity Level”,
dv.exploits AS “Exploit Count”,
dvr.reference AS “Reference”,
dvr.source AS “CVE ID”,
ds.summary AS “Vulnerability Solution”,
favi.service AS “Service Name”,
favi.port AS “Service Protocol”,
favi.protocol AS “Service Port”,
dv.exploit_skill_level AS “Exploit Minimum Skill”,
to_char(favi.date, ‘YYYY-MM-DD’) AS “Vulnerable Since”,
to_char(round(dv.risk_score::numeric,0),‘999G999G999’) AS “Vulnerability Risk Score”,
date_part(‘day’::text, ‘now’::text::timestamp without time zone - favi.date) AS “Vulnerability Age”

FROM
dim_asset da
JOIN fact_asset fa USING (asset_id)
JOIN fact_asset_vulnerability_instance favi USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_reference dvr USING (vulnerability_id)
LEFT JOIN dim_solution ds USING (nexpose_id)

WHERE
dvr.source = ‘CVE’::text OR dvr.source = ‘NVD’::text

Here is a cleaned up version, fixing a few things.
Replace all curly quotes with straight ones, Missing Join Key, dim_vulnerability_reference Join Multiplication can cause dupe rows,

Try this and see if this fires off and grabs what you are needing.

SELECT
  da.host_name AS "Asset Names",
  da.ip_address AS "Asset IP Address",
  da.os_family AS "Asset OS Family",
  da.os_name AS "Asset OS Name",
  da.os_version AS "Asset OS Version",
  TO_CHAR(ROUND(fa.risk_score::numeric, 0), '999G999G999') AS "Asset Risk Score",
  da.sites AS "Site Name",
  TO_CHAR(da.last_assessed_for_vulnerabilities, 'MM/DD/YYYY') AS "Vulnerability Test Date",
  dv.nexpose_id AS "Vulnerability ID",
  dv.title AS "Vulnerability Title",
  dv.description AS "Vulnerability Description",
  TO_CHAR(dv.date_published, 'MM/DD/YYYY') AS "Vulnerability Publish Date",
  favi.proof AS "Proof",
  dv.cvss_score AS "Vulnerability CVSS Score",
  dv.severity_score AS "Vulnerability Severity Level",
  dv.exploits AS "Exploit Count",
  dvr.reference AS "Reference",
  dvr.source AS "CVE ID",
  ds.summary AS "Vulnerability Solution",
  favi.service AS "Service Name",
  favi.port AS "Service Protocol",
  favi.protocol AS "Service Port",
  dv.exploit_skill_level AS "Exploit Minimum Skill",
  TO_CHAR(favi.date, 'YYYY-MM-DD') AS "Vulnerable Since",
  TO_CHAR(ROUND(dv.risk_score::numeric, 0), '999G999G999') AS "Vulnerability Risk Score",
  DATE_PART('day', CURRENT_DATE - favi.date) AS "Vulnerability Age"

FROM
  dim_asset da
  JOIN fact_asset fa ON da.asset_id = fa.asset_id
  JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
  JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
  JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id
  LEFT JOIN dim_solution ds ON dv.nexpose_id = ds.nexpose_id

WHERE
  dvr.source IN ('CVE', 'NVD')
  LIMIT 10;

[henryaj]

Thanks! That is close but am running into an issue where if there’s no CVE associated with the vulnerability, then it will not show the vulnerability at all

[bradpjaxx]

so you mean when there is only a nexpose id and not a identified CVE correct?

you can

-- change the join to:
LEFT JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id

and then change this line in the SELECT:

-- replace this:
-- dvr.source AS "CVE ID"

-- with this:
COALESCE(dvr.reference, dv.nexpose_id) AS "CVE ID"

This way, you always get something — either the CVE/NVD reference or the Nexpose ID.

If you still want to show the source, you could do:

COALESCE(dvr.source, 'Rapid7') AS "Reference Source"

[henryaj]

Still seeing the issue, I will give an example below of how the report should look:

Asset Name CVE ID Vulnerability ID Vulnerability Title

Asset name CVE-2025-3029 mfsa2025-22-cve-2025-3029 MFSA Firefox Security
Asset name google-chrome-cve-2025-3074 Google Chrome Vulnerability

When running the query on the DW, the only result that shows is the first one that lists the CVE ID, so if the CVE id field is blank, it won’t show up in the data warehouse.

[bradpjaxx]

ok well then lets try to Use a LEFT JOIN to dim_vulnerability_reference — not a regular (inner) join and Remove dvr.source IN (‘CVE’, ‘NVD’) from the WHERE clause , or it undoes the outer join, then we can prioritize CVE/NVD sources if you want only one reference.

SELECT
  da.host_name AS "Asset Name",
  dvr.reference AS "CVE ID",
  dv.nexpose_id AS "Vulnerability ID",
  dv.title AS "Vulnerability Title"

FROM
  dim_asset da
  JOIN fact_asset fa ON da.asset_id = fa.asset_id
  JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
  JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id

  -- LEFT JOIN here to keep non-CVE vulns
  LEFT JOIN dim_vulnerability_reference dvr 
    ON dv.vulnerability_id = dvr.vulnerability_id 
    AND dvr.source IN ('CVE', 'NVD')

LIMIT 50;

and then if you want something at least in the row/column when no CVE ID exists

COALESCE(dvr.reference, dv.nexpose_id) AS "Vulnerability Identifier"

Or separate it

CASE 
  WHEN dvr.reference IS NOT NULL THEN dvr.reference
  ELSE NULL
END AS "CVE ID",
dv.nexpose_id AS "Vulnerability ID"

[henryaj]

Awesome! Thanks so much, that worked. Any way you can look over the final query I came up with to see if my joins can be cleaned up at all? It runs a bit slower than I’d like.

SELECT

da.host_name AS “Asset Names”,

CAST(da.ip_address AS TEXT) AS “Asset IP Address”,

da.os_family AS " Asset OS Family",

da.os_name AS “Asset OS Name”,

da.os_version AS “Asset OS Version”,

to_char(round(fa.risk_score::numeric,0),‘999G999G999’) AS “Asset Risk Score”,

da.sites AS “Site Name”,

to_char(da.last_assessed_for_vulnerabilities, ‘MM/DD/YYYY’) AS “Vulnerability Test Date”,

dvr.reference AS “Vulnerability CVE IDs”,

dv.nexpose_id AS “Vulnerability ID”,

dv.title AS “Vulnerability Title”,

htmlToText(dv.description) AS “Vulnerability Description”,

string_agg(dvc.category_name, ', ') AS “Vulnerability Tags”,

date_part(‘day’::text, ‘now’::text::timestamp without time zone - favi.date) AS “Vulnerability Age”,

to_char(favi.date, ‘MM/DD/YYYY’) AS “Vulnerable Since”,

to_char(dv.date_published, ‘MM/DD/YYYY’) AS “Vulnerability Publish Date”,

htmlToText(favi.proof) AS “Proof”,

to_char(round(dv.risk_score::numeric,0),‘999G999G999’) AS “Vulnerability Risk Score”,

dv.cvss_score AS “Vulnerability CVSS Score”,

dv.severity_score AS “Vulnerability Severity Level”,

dv.exploits AS “Exploit Count”,

ds.summary AS “Vulnerability Solution”,

CASE WHEN favi.service IS NOT NULL THEN favi.service
ELSE ‘No service was found for this vulnerability’ END AS “Service Name”,

favi.protocol AS “Service Protocol”,

favi.port AS “Service Port”,

CASE WHEN dv.exploit_skill_level IS NOT NULL THEN dv.exploit_skill_level
ELSE ‘There were no exploits found for this vulnerability’ END AS “Exploit Minimum Skill”

FROM fact_asset_vulnerability_instance favi

JOIN dim_asset da ON da.asset_id = favi.asset_id

JOIN fact_asset fa ON fa.asset_id = favi.asset_id

JOIN dim_vulnerability dv USING (vulnerability_id)

JOIN dim_vulnerability_category dvc USING (vulnerability_id)

LEFT JOIN dim_vulnerability_reference dvr
ON dv.vulnerability_id = dvr.vulnerability_id
AND dvr.source IN (‘CVE’, ‘NVD’)

LEFT JOIN dim_asset_vulnerability_finding_rollup_solution davfrs ON davfrs.asset_id = favi.asset_id
AND davfrs.vulnerability_id = favi.vulnerability_id

JOIN dim_solution ds ON ds.solution_id = davfrs.solution_id

JOIN dim_asset_group_asset daga ON da.asset_id = daga.asset_id
JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id

WHERE
(
dvr.source = ‘CVE’
OR dvr.source = ‘NVD’
OR dvr.source = ‘*’
OR dvr.source IS NULL
OR dvr.source = ‘’
)
AND (dag.name = ‘’)

GROUP BY
da.host_name,
da.ip_address,
da.os_family,
da.os_name,
da.os_version,
fa.risk_score,
da.sites,
da.last_assessed_for_vulnerabilities,
dv.cvss_score,
dvr.reference,
dv.nexpose_id,
dv.title,
dv.description,
favi.date,
dv.date_published,
favi.proof,
dv.risk_score,
dv.severity_score,
dv.exploits,
ds.summary,
favi.service,
favi.port,
favi.protocol,
dv.exploit_skill_level

[bradpjaxx]

AND (dag.name = ‘’) isnt a valid request, it literally filters for empty string names, which almost certainly doesn’t exist.

If you want to include all asset groups, just remove the filter on dag.name.

If you’re using this to optionally filter by group name then

-- Optional asset group filter:
-- WHERE dag.name = 'External Assets'

here is a cleaned up version:

SELECT
  da.host_name AS "Asset Names",
  CAST(da.ip_address AS TEXT) AS "Asset IP Address",
  da.os_family AS "Asset OS Family",
  da.os_name AS "Asset OS Name",
  da.os_version AS "Asset OS Version",
  TO_CHAR(ROUND(fa.risk_score::numeric, 0), '999G999G999') AS "Asset Risk Score",
  da.sites AS "Site Name",
  TO_CHAR(da.last_assessed_for_vulnerabilities, 'MM/DD/YYYY') AS "Vulnerability Test Date",
  COALESCE(dvr.reference, dv.nexpose_id) AS "Vulnerability CVE ID",
  dv.nexpose_id AS "Vulnerability ID",
  dv.title AS "Vulnerability Title",
  htmlToText(dv.description) AS "Vulnerability Description",
  string_agg(DISTINCT dvc.category_name, ', ') AS "Vulnerability Tags",
  DATE_PART('day', CURRENT_DATE - favi.date) AS "Vulnerability Age",
  TO_CHAR(favi.date, 'MM/DD/YYYY') AS "Vulnerable Since",
  TO_CHAR(dv.date_published, 'MM/DD/YYYY') AS "Vulnerability Publish Date",
  htmlToText(favi.proof) AS "Proof",
  TO_CHAR(ROUND(dv.risk_score::numeric, 0), '999G999G999') AS "Vulnerability Risk Score",
  dv.cvss_score AS "Vulnerability CVSS Score",
  dv.severity_score AS "Vulnerability Severity Level",
  dv.exploits AS "Exploit Count",
  ds.summary AS "Vulnerability Solution",
  COALESCE(favi.service, 'No service was found for this vulnerability') AS "Service Name",
  favi.protocol AS "Service Protocol",
  favi.port AS "Service Port",
  COALESCE(dv.exploit_skill_level, 'There were no exploits found for this vulnerability') AS "Exploit Minimum Skill"

FROM
  fact_asset_vulnerability_instance favi
JOIN dim_asset da ON da.asset_id = favi.asset_id
JOIN fact_asset fa ON fa.asset_id = favi.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = favi.vulnerability_id
LEFT JOIN dim_vulnerability_reference dvr 
       ON dv.vulnerability_id = dvr.vulnerability_id AND dvr.source IN ('CVE', 'NVD')
LEFT JOIN dim_asset_vulnerability_finding_rollup_solution davfrs 
       ON davfrs.asset_id = favi.asset_id AND davfrs.vulnerability_id = favi.vulnerability_id
LEFT JOIN dim_solution ds ON ds.solution_id = davfrs.solution_id
LEFT JOIN dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
LEFT JOIN dim_asset_group_asset daga ON da.asset_id = daga.asset_id
LEFT JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id

-- Optional asset group filter:
-- WHERE dag.name = 'WINDOWS SERVER 2022'

GROUP BY
  da.host_name,
  da.ip_address,
  da.os_family,
  da.os_name,
  da.os_version,
  fa.risk_score,
  da.sites,
  da.last_assessed_for_vulnerabilities,
  dvr.reference,
  dv.nexpose_id,
  dv.title,
  dv.description,
  favi.date,
  dv.date_published,
  favi.proof,
  dv.risk_score,
  dv.cvss_score,
  dv.severity_score,
  dv.exploits,
  ds.summary,
  favi.service,
  favi.protocol,
  favi.port,
  dv.exploit_skill_level