SQL example - asset authentication access level

This example query returns asset credential statuses in terms of authentication access level.

sql
1
SELECT ds.name "Site", da.ip_address as "IP Address", da.host_name as "Host Name", dacs.aggregated_credential_status_description "Access Level"
2
3
FROM dim_asset da
4
5
JOIN fact_asset fa using (asset_id)
6
7
JOIN dim_site_asset dsa using (asset_id)
8
9
JOIN dim_site ds using (site_id)
10
11
JOIN dim_aggregated_credential_status dacs using (aggregated_credential_status_id)
12
13
GROUP BY ds.name, da.ip_address, da.host_name, dacs.aggregated_credential_status_description
14
15
ORDER BY da.ip_address DESC