I am currently using this SQL query:
WITH counts as
(SELECT scan_id, count(*) as total
GROUP BY scan_id)
SELECT ds.name as “Site Name”, counts.total as “Number of Assets”
FROM dim_site ds
JOIN dim_site_scan dss USING (site_id)
JOIN dim_scan dsc USING (scan_id)
JOIN dim_asset_scan das USING (scan_id)
JOIN counts on das.scan_id = counts.scan_id
GROUP BY ds.name, counts.total, dsc.started, dsc.finished
The purpose is to retrieve the name of each site scanned, and the number of assets found during each scan of each site. The current query returns the number of active assets, but I want to get the total number of assets that are listed in the scan report.
For example, this site in the screenshot below has 1293 active assets and 4016 inactive assets. My code returns the number 1293.
Looking into the latest scan report shows 1338, which is the number that I want to retrieve.
What is the table that I should be trying to get this information from?