I have a report I have inherited, but now management would like to add a first_discovered date to the existing report. I know I’m looking for fava.first_discovered, but I’m having a difficult time getting the SQL right to join the fact_asset_vulnerability_age table as I’m not super comfortable with SQL. I believe I have added fava.first_discovered AS first_discovered in all the appropriate places, but the join is killing me. Can anyone offer any assitance?
This is my query:
SELECT
x.*,
CASE
WHEN ( x.date_published >= (NOW() - INTERVAL ‘30 days’) ) THEN
1
ELSE
0
END AS “date_published <30”,
CASE
WHEN ( x.date_published >= (NOW() - INTERVAL ‘60 days’) and x.date_published < (NOW() - INTERVAL ‘30 days’) ) THEN
1
ELSE
0
END AS “date_published 31-60”,
CASE
WHEN ( x.date_published >= (NOW() - INTERVAL ‘90 days’) and x.date_published < (NOW() - INTERVAL ‘60 days’) ) THEN
1
ELSE
0
END AS “date_published 61-90”,
CASE
WHEN ( x.date_published < (NOW() - INTERVAL ‘90 days’) ) THEN
1
ELSE
0
END AS “date_published 91+”FROM
( WITH assets_vulns AS (
SELECT
fasv.asset_id,
fasv.vulnerability_id,
baselinecomparison(fasv.scan_id, current_scan) AS baseline,
s.baseline_scan,
s.current_scan
FROM
fact_asset_scan_vulnerability_instance fasv
JOIN (
SELECT
asset_id,
scanasofdate(asset_id, ‘02/1/2023’) AS baseline_scan,
scanasofdate(asset_id, ‘03/6/2023’) AS current_scan
FROM
dim_asset
) s ON s.asset_id = fasv.asset_id
AND ( fasv.scan_id = s.baseline_scan
OR fasv.scan_id = s.current_scan )
GROUP BY
fasv.asset_id,
fasv.vulnerability_id,
s.baseline_scan,
s.current_scan
HAVING ( baselinecomparison(fasv.scan_id, current_scan) = ‘Same’ )
OR ( baselinecomparison(fasv.scan_id, current_scan) = ‘New’ )
OR ( baselinecomparison(fasv.scan_id, current_scan) = ‘Old’ )
), baseline_scan_date AS (
SELECT
av.asset_id,
finished
FROM
assets_vulns av
LEFT JOIN dim_scan ds ON ds.scan_id = av.baseline_scan
GROUP BY
av.asset_id,
finished
), current_scan_date AS (
SELECT
av.asset_id,
finished
FROM
assets_vulns av
LEFT JOIN dim_scan ds ON ds.scan_id = av.current_scan
GROUP BY
av.asset_id,
finished
), new_vulns AS (
SELECT
av.asset_id,
av.vulnerability_id,
COUNT(av.vulnerability_id) AS new_vulns
FROM
assets_vulns AS av
WHERE av.baseline = ‘New’
GROUP BY av.asset_id,
av.vulnerability_id),remediated_vulns
AS (SELECT av.asset_id,
av.vulnerability_id,
Count (av.vulnerability_id) AS remediated_vulns
FROM assets_vulns AS av
WHERE av.baseline = ‘Old’
GROUP BY av.asset_id,
av.vulnerability_id),old_vulns
AS (SELECT av.asset_id,
av.vulnerability_id,
Count (av.vulnerability_id) AS remediated_vulns
FROM assets_vulns AS av
WHERE av.baseline = ‘Same’
GROUP BY av.asset_id,
av.vulnerability_id),vuln_exploit_count
AS (SELECT CASE
WHEN ec1.vulnerability_id IS NOT NULL THEN
ec1.vulnerability_id
ELSE ec2.vulnerability_id
END AS vulnerability_id,
metasploit,exploitdb
FROM (SELECT av.vulnerability_id,
Count(dve.source) AS metasploit
FROM assets_vulns av
JOIN dim_vulnerability_exploit dve
ON av.vulnerability_id = dve.vulnerability_id
WHERE dve.source = ‘Metasploit’
GROUP BY av.vulnerability_id) ec1
FULL JOIN (SELECT av.vulnerability_id,
Count(dve.source) AS exploitdb
FROM assets_vulns av
JOIN dim_vulnerability_exploit dve
ON av.vulnerability_id =
dve.vulnerability_id
WHERE dve.source = ‘Exploit DB’
GROUP BY av.vulnerability_id) ec2
ON ec2.vulnerability_id = ec1.vulnerability_id)SELECT
‘Remediated’ AS status,
da1.ip_address AS ip_address,
da1.host_name AS hostname,
bsd.finished AS baseline_scan_datetime,
csd.finished AS current_scan_datetime,
fava.first_discovered AS first_discovered,
dv1.vulnerability_id,
dv1.title,
Cast(dv1.cvss_score AS DECIMAL(10, 2)) AS cvss_score,
Cast(dv1.riskscore AS DECIMAL(10, 0)) AS riskscore,
dv1.malware_kits,
da1.asset_id,
dv1.date_added,
dv1.date_published,
dv1.severity,
CASE
WHEN vec.metasploit IS NULL THEN 0
ELSE vec.metasploit
END AS metasploit,
CASE
WHEN vec.exploitdb IS NULL THEN 0
ELSE vec.exploitdb
END AS exploitdb
FROM remediated_vulns rv
JOIN dim_asset da1
ON da1.asset_id = rv.asset_id
LEFT JOIN baseline_scan_date bsd
ON bsd.asset_id = da1.asset_id
LEFT JOIN current_scan_date csd
ON csd.asset_id = da1.asset_id
JOIN dim_vulnerability dv1
ON dv1.vulnerability_id = rv.vulnerability_id
LEFT JOIN vuln_exploit_count vec
ON vec.vulnerability_id = rv.vulnerability_idUNION ALL
SELECT ‘New’ AS status,
da2.ip_address AS ip_address,
da2.host_name AS hostname,
bsd.finished AS baseline_scan_datetime,
csd.finished AS current_scan_datetime,
fava.first_discovered AS first_discovered,
dv2.vulnerability_id,
dv2.title,
Cast(dv2.cvss_score AS DECIMAL(10, 2)) AS cvss_score,
Cast(dv2.riskscore AS DECIMAL(10, 0)) AS riskscore,
dv2.malware_kits,
da2.asset_id,
dv2.date_added,
dv2.date_published,
dv2.severity,
CASE
WHEN vec.metasploit IS NULL THEN 0
ELSE vec.metasploit
END AS metasploit,
CASE
WHEN vec.exploitdb IS NULL THEN 0
ELSE vec.exploitdb
END AS exploitdb
FROM new_vulns nv
JOIN dim_asset AS da2
ON da2.asset_id = nv.asset_id
LEFT JOIN baseline_scan_date bsd
ON bsd.asset_id = da2.asset_id
LEFT JOIN current_scan_date csd
ON csd.asset_id = da2.asset_id
JOIN dim_vulnerability dv2
ON dv2.vulnerability_id = nv.vulnerability_id
LEFT JOIN vuln_exploit_count vec
ON vec.vulnerability_id = nv.vulnerability_idUNION ALL
SELECT ‘Old’ AS status,
da3.ip_address AS ip_address,
da3.host_name AS hostname,
bsd.finished AS baseline_scan_datetime,
csd.finished AS current_scan_datetime,
fava.first_discovered AS first_discovered,
dv3.vulnerability_id,
dv3.title,
Cast(dv3.cvss_score AS DECIMAL(10, 2)) AS cvss_score,
Cast(dv3.riskscore AS DECIMAL(10, 0)) AS riskscore,
dv3.malware_kits,
da3.asset_id,
dv3.date_added,
dv3.date_published,
dv3.severity,
CASE
WHEN vec.metasploit IS NULL THEN 0
ELSE vec.metasploit
END AS metasploit,
CASE
WHEN vec.exploitdb IS NULL THEN 0
ELSE vec.exploitdb
END AS exploitdb
FROM old_vulns ov
JOIN dim_asset AS da3
ON da3.asset_id = ov.asset_id
LEFT JOIN baseline_scan_date bsd
ON bsd.asset_id = da3.asset_id
LEFT JOIN current_scan_date csd
ON csd.asset_id = da3.asset_id
JOIN dim_vulnerability dv3
ON dv3.vulnerability_id = ov.vulnerability_id
LEFT JOIN vuln_exploit_count vec
ON vec.vulnerability_id = ov.vulnerability_idORDER BY status DESC,
ip_address,
hostname,
title) x
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)