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