SQL example - asset details

This example query provides:

  • scan site
  • hostname
  • ip address
  • operating system
  • software name
  • software version last scan date
SQL
1
SELECT dss.site_id AS "Site ID", da.host_name, da.ip_address, dos.description AS "Operating System", 
2
fa.scan_started AS "Last Scan Date", ds.name AS "Software Name", ds.version AS "Software Version"
3
FROM dim_asset da
4
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
5
JOIN fact_asset fa ON fa.asset_id = da.asset_id
6
JOIN dim_asset_software das ON das.asset_id = da.asset_id
7
JOIN dim_software ds ON ds.software_id = das.software_id
8
JOIN dim_scan ds2 ON ds2.scan_id = fa.last_scan_id
9
JOIN dim_site_scan dss ON dss.scan_id = ds2.scan_id