SQL example - unauthenticated/unmanaged assets

sql
1
SELECT dsite."name" as "Site", da.ip_address, da.host_name, dos.description as "OS", os.certainty_max
2
3
FROM fact_asset AS fa
4
5
   JOIN dim_asset da ON da.asset_id = fa.asset_id
6
7
   JOIN (
8
9
      SELECT asset_id, MAX(certainty) as certainty_max
10
11
      FROM dim_asset_operating_system
12
13
      GROUP BY asset_id
14
15
   ) os ON fa.asset_id = os.asset_id AND os.certainty_max < 1
16
17
JOIN dim_operating_system as dos
18
19
ON da.operating_system_id = dos.operating_system_id
20
21
JOIN dim_site_asset as dsa
22
23
ON fa.asset_id = dsa.asset_id
24
25
JOIN dim_site as dsite
26
27
ON dsa.site_id = dsite.site_id
28
29
GROUP BY dsite."name", da.ip_address, da.host_name,dos.description, os.certainty_max
30
31
ORDER BY "Site", da.ip_address