Hello, I am trying to recreate a report for the data warehouse with the below columns:
Asset Names
Asset IP Address
Asset OS Family
Asset OS Name
Asset OS Version
Asset Risk Score
Site Name
Vulnerability Test Date
Vulnerability CVE IDs
Vulnerability ID
Vulnerability Title
Vulnerability Description
Vulnerability Tags
Vulnerability Age
Vulnerable Since
Vulnerability Published 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
So far I have only been able to get this:
SELECT
da.host_name AS “Asset Name”,
da.ip_address AS “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 “Risk”,
da.sites AS “Site Name”
FROM dim_asset da
JOIN fact_asset fa USING (asset_id)
Can someone assist please??