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