Hi together,
we are currently using below query to create report that summarizes Cves under fixes per asset with some additional information.
However one important part is missing. We want also the oldest detection date of all cves which are summarized under one fix in a seperate column.
In other words, this is the same as when the fix could have been applied to the asset for the first time.
Do you have any idea how we can achieve this?
thank you in advance
and kind regards
WITH region_tags
AS (SELECT dta.asset_id,
dt.tag_name AS region
FROM dim_tag dt
JOIN dim_tag_asset dta
ON dt.tag_id = dta.tag_id
WHERE dt.tag_type = ‘LOCATION’
AND dt.tag_name = ‘###’
),
country_tags
AS (SELECT dta.asset_id,
dt.tag_name AS country
FROM dim_tag dt
JOIN dim_tag_asset dta
ON dt.tag_id = dta.tag_id
WHERE dt.tag_type = ‘LOCATION’
AND (dt.tag_name <> ‘###’
AND dt.tag_name <> ‘###’
AND dt.tag_name <> ‘###’
AND dt.tag_name <> ‘###’
)
)
SELECT ds.fix,
ds.summary,
rt.region,
string_agg(DISTINCT(ct.country), ', ') as countries,
da.host_name,
string_agg(DISTINCT(dos.description), ', ') as OS,
da.ip_address,
string_agg(DISTINCT(dv.title), ', ') as titles,
string_agg(DISTINCT(dv.description), ', ') as descriptions,
string_agg(DISTINCT(dv.nexpose_id), ', ') as nexpose_ids,
Count(dv.vulnerability_id) as count_vuln,
Max(dv.cvss_v3_score) as max_cvss,
SUM(dv.exploits) as exploits,
SUM(dv.malware_kits) as malware_kits,
string_agg(DISTINCT(dvr.reference), ', ') as cves,
da.last_assessed_for_vulnerabilities
FROM dim_asset_vulnerability_solution davs
JOIN dim_asset da
ON davs.asset_id = da.asset_id
JOIN dim_operating_system dos
ON da.operating_system_id = dos.operating_system_id
JOIN region_tags rt
ON davs.asset_id = rt.asset_id
JOIN country_tags ct
ON davs.asset_id = ct.asset_id
JOIN dim_vulnerability dv
ON davs.vulnerability_id = dv.vulnerability_id
JOIN dim_solution ds
ON davs.solution_id = ds.solution_id
JOIN dim_vulnerability_reference dvr
ON davs.vulnerability_id = dvr.vulnerability_id
GROUP BY ds.fix,
ds.summary,
rt.region,
da.host_name,
dos.description,
da.ip_address,
da.last_assessed_for_vulnerabilities
