SQL example - new assets since last scan

This example query shows new assets since the last scan.

sql
1
WITH
2
3
last_scan_for_site AS (
4
5
SELECT site_id, MAX(scan_id) as scan_id
6
7
FROM dim_site_scan
8
9
JOIN dim_scan USING (scan_id)
10
11
WHERE status_id = 'C'
12
13
GROUP BY site_id
14
15
),
16
17
previous_scan_for_site AS (
18
19
SELECT site_id, MAX(scan_id) as scan_id
20
21
FROM dim_site_scan
22
23
JOIN dim_scan USING (scan_id)
24
25
WHERE (site_id, scan_id) NOT IN (
26
27
SELECT * FROM last_scan_for_site
28
29
)
30
31
AND status_id = 'C'
32
33
GROUP BY site_id
34
35
),
36
37
current_scan_state AS (
38
39
SELECT site_id, asset_id, scan_id
40
41
FROM dim_site
42
43
JOIN dim_site_asset USING (site_id)
44
45
JOIN last_scan_for_site USING (site_id)
46
47
JOIN dim_asset_scan USING (asset_id, scan_id)
48
49
),
50
51
previous_scan_state AS (
52
53
SELECT site_id, asset_id, scan_id
54
55
FROM dim_site
56
57
JOIN dim_site_asset USING (site_id)
58
59
JOIN previous_scan_for_site USING (site_id)
60
61
JOIN dim_asset_scan USING (asset_id, scan_id)
62
63
),
64
65
last_scan_for_asset AS (
66
67
SELECT site_id, asset_id, scan_id
68
69
FROM last_scan_for_site
70
71
JOIN dim_site_asset USING (site_id)
72
73
),
74
75
new_assets AS (
76
77
SELECT all_sate.site_id, all_sate.asset_id, baselineComparison(all_sate.scan_id, lsfa.scan_id)
78
79
FROM (
80
81
SELECT * FROM current_scan_state
82
83
UNION ALL
84
85
SELECT * FROM previous_scan_state
86
87
) all_sate
88
89
JOIN last_scan_for_asset lsfa USING (asset_id)
90
91
GROUP BY all_sate.site_id, all_sate.asset_id
92
93
HAVING baselineComparison(all_sate.scan_id, lsfa.scan_id) = 'New'
94
95
)
96
97
SELECT ip_address, mac_address, host_name, site.name, os.description
98
99
FROM new_assets
100
101
JOIN dim_asset USING (asset_id)
102
103
JOIN dim_operating_system os USING (operating_system_id)
104
105
JOIN dim_site site USING (site_id)
106
107
ORDER BY ip_address