Data warehouse sql query check

InsightVM SQL Queries
1

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

2

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::date::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