SQL example - new assets since last scan
This example query shows new assets since the last scan.
sql
1WITH23last_scan_for_site AS (45SELECT site_id, MAX(scan_id) as scan_id67FROM dim_site_scan89JOIN dim_scan USING (scan_id)1011WHERE status_id = 'C'1213GROUP BY site_id1415),1617previous_scan_for_site AS (1819SELECT site_id, MAX(scan_id) as scan_id2021FROM dim_site_scan2223JOIN dim_scan USING (scan_id)2425WHERE (site_id, scan_id) NOT IN (2627SELECT * FROM last_scan_for_site2829)3031AND status_id = 'C'3233GROUP BY site_id3435),3637current_scan_state AS (3839SELECT site_id, asset_id, scan_id4041FROM dim_site4243JOIN dim_site_asset USING (site_id)4445JOIN last_scan_for_site USING (site_id)4647JOIN dim_asset_scan USING (asset_id, scan_id)4849),5051previous_scan_state AS (5253SELECT site_id, asset_id, scan_id5455FROM dim_site5657JOIN dim_site_asset USING (site_id)5859JOIN previous_scan_for_site USING (site_id)6061JOIN dim_asset_scan USING (asset_id, scan_id)6263),6465last_scan_for_asset AS (6667SELECT site_id, asset_id, scan_id6869FROM last_scan_for_site7071JOIN dim_site_asset USING (site_id)7273),7475new_assets AS (7677SELECT all_sate.site_id, all_sate.asset_id, baselineComparison(all_sate.scan_id, lsfa.scan_id)7879FROM (8081SELECT * FROM current_scan_state8283UNION ALL8485SELECT * FROM previous_scan_state8687) all_sate8889JOIN last_scan_for_asset lsfa USING (asset_id)9091GROUP BY all_sate.site_id, all_sate.asset_id9293HAVING baselineComparison(all_sate.scan_id, lsfa.scan_id) = 'New'9495)9697SELECT ip_address, mac_address, host_name, site.name, os.description9899FROM new_assets100101JOIN dim_asset USING (asset_id)102103JOIN dim_operating_system os USING (operating_system_id)104105JOIN dim_site site USING (site_id)106107ORDER BY ip_address
Did this page help you?