Trying to pull historical VM scan results (summaries) for sites to build external dashboard for risk scores and the average number of vulnerabilities per asset located in the site.
However, I have not been able to pull data at the site level that reflects historical scans.
If you look here and crtl+f and search for fact_scan, scroll down to the “Dimensional model” you will see how you might be able to get this information.
Some sudo SQL
SELECT
fs.*
FROM
fact_scan fs
JOIN
dim_scan sc fs.scan_id = sc.scan_id
JOIN
dim_site_scan dss ON sc.scan_id = dss.scan_id
JOIN
dim_site ds ON dss.site_id = ds.site_id
WHERE
ds.name = ''
AND DATE(ds.finished) BETWEEN startDate AND endDate
From the Documentation:
fact_scan
Description: The fact_scan fact provides the summarized information for every scan any asset within the scope of the report was scanned during. For each scan, there will be a record in this fact table with the summarized results.
dim_scan
Description: Dimension that provides access to the scans for any assets within the scope of the report.
dim_site_scan
Description: Dimension that provides access to the relationship between a site and its associated scans. For each scan of a site within the scope of the report, a record will be present in this table.
dim_site
Description: Dimension that provides access to the textual information of all sites configured to be within the scope of the report. There will be one record in this dimension for every site which any asset in the scope of the report is associated to, including assets specified through configuring scans, sites, or asset groups.