SQL example - new assets since 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