SQL example - sites with credentials issues

This example query is one of the ways to get an idea on what assets have poorly managed credentials such as default accounts.

sql
1
SELECT ds.name AS site, da.ip_address, da.host_name, dv.title AS vulnerability_title, dos.description AS operating_system, dos.cpe
2
3
FROM fact_asset_vulnerability_finding favf
4
5
JOIN dim_asset da USING (asset_id)
6
7
JOIN dim_operating_system dos USING (operating_system_id)
8
9
JOIN dim_vulnerability dv USING (vulnerability_id)
10
11
JOIN dim_vulnerability_category dvc USING (vulnerability_id)
12
13
JOIN dim_site_asset dsa USING (asset_id)
14
15
JOIN dim_site ds USING (site_id)
16
17
WHERE (dvc.category_name LIKE '%Default Account%')
18
19
ORDER BY ds.name ASC, dv.title ASC