SQL example - authentication level achieved with current credentials

This example query returns what authentication levels assets are receiving during scans. This will show the site, asset, IP and hostname with service, protocol and access level.

sql
1
SELECT dsite.name "Site", da.ip_address as "IP Address", da.host_name as "Host Name", ds.name as "Service", dp.description as "Protocol", dcs.credential_status_description "Access Level"
2
3
FROM dim_asset da
4
5
JOIN fact_asset_scan_service fass using (asset_id)
6
7
JOIN fact_asset fa using (asset_id)
8
9
JOIN dim_site_asset dsa using (asset_id)
10
11
JOIN dim_site dsite using (site_id)
12
13
JOIN dim_service ds using (service_id)
14
15
JOIN dim_protocol dp using (protocol_id)
16
17
JOIN dim_credential_status dcs using (credential_status_id)
18
19
GROUP BY dsite.name, da.ip_address, da.host_name, ds.name, dp.description, dcs.credential_status_description
20
21
ORDER BY da.ip_address DESC