Hello everybody
For the external reporting of the vulnerability results I have created a simple query to export all relevant information into a .csv file. This query is always executed one day after the monthly global scan (This query is always executed one day after the monthly global scan (after the export of the results to the data warehouse).
Select
da.ip_address AS ip,
da.host_name AS asset,
da.os_vendor,
da.os_description AS os_version,
dvul.severity AS risk,
replace(dvul.title,’,’,’’) AS vulnerability_name,
to_char(fasvf.date, ‘YYYY-MM-DD’) AS first_seen,
to_char(da.last_assessed_for_vulnerabilities, ‘YYYY-MM-DD’) AS last_seen
FROM
dim_asset da
INNER JOIN
fact_asset_vulnerability_finding fasvf USING(asset_id)
INNER JOIN
dim_vulnerability dvul USING(vulnerability_id)
WHERE
date_trunc(‘day’, da.last_assessed_for_vulnerabilities) = ‘2020-10-04’
This query actually works quite well. As long as I always export after the global scan without another scan running in between.
Now I have added some columns to the query and should now pull another export, but this time also with the results of the September scan. To achieve this I had to adjust the query described above to get the historical results. But I am not sure if my approach is correct or if there is an easier way.
Select
da.ip_address AS IP,
da.host_name AS asset,
da.os_vendor,
da.os_description AS os_version,
replace(dvul.title,’,’,’’),
dvul.severity AS risk,
to_char(fvuld.first_discovered, ‘YYYY-MM-DD’) AS first_seen,
to_char(fvuld.most_recently_discovered, ‘YYYY-MM-DD’) AS last_seen,
to_char(fvuld.day, ‘YYYY-MM-DD’) AS snapshot_day
FROM
dim_vulnerability dvul
INNER JOIN
fact_vulnerability_date fvuld USING(vulnerability_id)
INNER JOIN
fact_asset_vulnerability_finding_date fasvfd USING(vulnerability_id)
INNER JOIN
dim_asset da USING(asset_id)
WHERE
dvul.title = ‘X.509 Server Certificate Will Expire Within 30 Days’
AND date_trunc(‘day’, fvuld.day) = ‘2020-09-02’
AND date_trunc(‘day’, fvuld.most_recently_discovered) = ‘2020-09-01’
AND da.host_name = ‘swchzh1640pv.a2.loc’
This query also works so far that I do not get any error messages. However, the output was incorrect. So I tried to limit the output to one asset and one vulnerability to locate the error. As you can see in this example, I have got several duplicates here.
I’d like to mention that I just recently started to work with database queries and therefore my know how is very limited. I was wondering if someone from the community could help me in this case.
Thanks in advance
