SQL Query Example: Authentication Level Achieved with Current Credentials

Document created by synapsr on Feb 6, 2015
Version 1Show Document
  • View in full screen mode

On the subject of credentials again, here's a query to report on what authentication levels assets are receiving during scans.  This will show the site, asset, IP and hostname with service, protocol and access level


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"

FROM dim_asset da

JOIN fact_asset_scan_service fass using (asset_id)

JOIN fact_asset fa using (asset_id)

JOIN dim_site_asset dsa using (asset_id)

JOIN dim_site dsite using (site_id)

JOIN dim_service ds using (service_id)

JOIN dim_protocol dp using (protocol_id)

JOIN dim_credential_status dcs using (credential_status_id)

GROUP BY dsite.name, da.ip_address, da.host_name, ds.name, dp.description, dcs.credential_status_description

ORDER BY da.ip_address DESC

3 people found this helpful