I have a query to pull basic asset information to check against our other asset audits lists but I am not sure how to get the site name rather than the site ID. Can anyone help?
SELECT da.ip_address, da.host_name, dss.site_id AS “Site ID”, dos.description AS “Operating System”,
fa.scan_started AS “Last Scan Date”
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN fact_asset fa ON fa.asset_id = da.asset_id
JOIN dim_scan ds2 ON ds2.scan_id = fa.last_scan_id
JOIN dim_site_scan dss ON dss.scan_id = ds2.scan_id
There’s a dim_site and dim_site_asset table where you can join to get the site name. I added those to your query.
SELECT da.ip_address, da.host_name, dss.site_id AS "Site ID", dsite.name AS "Site Name",
dos.description AS "Operating System", fa.scan_started AS "Last Scan Date"
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN fact_asset fa ON fa.asset_id = da.asset_id
JOIN dim_scan ds2 ON ds2.scan_id = fa.last_scan_id
JOIN dim_site_scan dss ON dss.scan_id = ds2.scan_id
JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id
JOIN dim_site dsite ON dsite.site_id = dsa.site_id
Also if you’re looking to do more with queries, you can refer to this page to see more of the database schema. Here’s the dim_site table so you can see what other info it has.
One follow-up question (to show my total lack of SQL skills): is there a way to configure the export so that if an asset is in more than one site that it lists them all in one row, perhaps separated by a comma instead of a record for every asset/site combination?
SELECT da.ip_address, da.host_name, da.sites as "Sites",
dos.description AS "Operating System", fa.scan_started AS "Last Scan Date"
FROM dim_asset da
JOIN dim_operating_system dos using (operating_system_id)
JOIN fact_asset fa using (asset_id)
