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.
Any help would be appreciated.
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
dim_scan sc fs.scan_id = sc.scan_id
dim_site_scan dss ON sc.scan_id = dss.scan_id
dim_site ds ON dss.site_id = ds.site_id
ds.name = ''
AND DATE(ds.finished) BETWEEN startDate AND endDate
From the Documentation:
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.
Description: Dimension that provides access to the scans for any assets within the scope of the report.
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.
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.