Fact_site fact_site_date

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

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.