Thanks slove, Unfortunately I have checked this queries before and there it is not what I’m looking for. Maybe I’m just not good enough at sql queries. I can share my query so you can see what I’m trying:
WITH assets AS (
SELECT asset_id, host_name,ip_address,sites,operating_system_id
FROM dim_asset
),
vulnerability_categories AS (
SELECT vulnerability_id, csv(category_name) as “categories”
FROM dim_vulnerability_category
GROUP BY vulnerability_id
),
vulnerabilities AS (
SELECT d_v.vulnerability_id, d_v.title, d_v.description, d_v.severity_score,d_v.cvss_score,d_v.cvss_v3_score,d_v.exploits, v_c.categories
FROM dim_vulnerability d_v
JOIN vulnerability_categories v_c ON (d_v.vulnerability_id = v_c.vulnerability_id)
),
scans AS (
SELECT asset_id, vulnerability_id, d_site.name as site_name, proof, last_assessed_for_vulnerabilities AS last_assessed, finished AS most_recent_scan
FROM (
SELECT fa_instance.asset_id, vulnerability_id, max(dim_scan.scan_id) as scan_id, proofAsText(proof) as proof, last_assessed_for_vulnerabilities
FROM dim_scan
JOIN fact_asset_scan_vulnerability_instance fa_instance ON (fa_instance.scan_id = dim_scan.scan_id)
JOIN dim_asset da_asset ON (da_asset.asset_id = fa_instance.asset_id)
WHERE last_assessed_for_vulnerabilities >= current_date - interval ‘7 days’
GROUP BY fa_instance.asset_id, vulnerability_id, proof, last_assessed_for_vulnerabilities
) d_scan_recent
JOIN dim_scan d_scan ON (d_scan_recent.scan_id = d_scan.scan_id)
JOIN dim_site_scan d_s_scan ON (d_scan.scan_id = d_s_scan.scan_id)
JOIN dim_site d_site ON (d_site.site_id = d_s_scan.site_id)
)
SELECT d_scan.asset_id as “Asset ID”,
LOWER(d_a.host_name) as “Hostname”,
d_a.sites as “Asset Sites”,
d_a.ip_address as “IP Address”,
d_scan.vulnerability_id as “Vulnerability ID”,
d_scan.proof as “Proof”,
d_v.title as “Vulnerability Title”,
d_v.description as “Vulnerability Description”,
d_v.severity_score as “Severity Score”,
d_v.categories as “Vulnerability Categories”,
CAST (d_v.cvss_score AS DECIMAL (10,2)) as “CVSS Score”,
CAST (d_v.cvss_v3_score AS DECIMAL (10,2)) as “CVSSv3 Score”,
d_v.exploits as “Exploits Available”,
d_sol.fix as “Fix”,
d_os.name as “Operating System”,
–d_sol.summary as “Summary”,
–d_sol.additional_data as “Additional Data”,
d_scan.most_recent_scan as “Scan Date”,
d_scan.last_assessed AS “Last Assessed”,
d_scan.site_name as “Scan Site Name”
FROM scans d_scan
JOIN vulnerabilities d_v ON (d_v.vulnerability_id=d_scan.vulnerability_id)
JOIN assets d_a ON (d_a.asset_id=d_scan.asset_id)
JOIN dim_asset_vulnerability_best_solution d_best_sol ON
( d_best_sol.asset_id = d_scan.asset_id AND d_best_sol.vulnerability_id = d_scan.vulnerability_id)
JOIN dim_solution d_sol ON (d_sol.solution_id = d_best_sol.solution_id)
JOIN dim_operating_system d_os ON (d_os.operating_system_id = d_a.operating_system_id)
Just in case someone can help. Thanks!