SQL example - delta since the last scan

sql
1
WITH
2

3
   site_last_scan AS (
4

5
      SELECT site_id,
6

7
      (SELECT scan_id AS last_scan
8

9
       FROM dim_site_scan
10

11
          JOIN dim_scan USING (scan_id)
12

13
       WHERE site_id = ds.site_id
14

15
       ORDER BY finished DESC
16

17
       LIMIT 1) AS last_scan
18

19
      FROM dim_site ds
20

21
   ),
22

23
   site_previous_scan AS (
24

25
      SELECT site_id,
26

27
      (SELECT scan_id AS last_scan
28

29
       FROM dim_site_scan
30

31
          JOIN dim_scan USING (scan_id)
32

33
       WHERE site_id = ds.site_id AND scan_id NOT IN (SELECT last_scan FROM site_last_scan WHERE site_id = ds.site_id)
34

35
       ORDER BY finished DESC
36

37
       LIMIT 1) AS previous_scan
38

39
      FROM dim_site ds
40

41
   ),
42

43
last_asset_count AS (SELECT sls.site_id, count(fas.asset_id) AS last_asset_count
44

45
FROM site_last_scan AS sls
46

47
LEFT OUTER JOIN fact_asset_scan AS fas ON sls.last_scan = fas.scan_id
48

49
GROUP BY sls.site_id),
50

51
 
52

53
previous_asset_count AS (SELECT sps.site_id, count(fas.asset_id) AS previous_asset_count
54

55
FROM site_previous_scan AS sps
56

57
LEFT OUTER JOIN fact_asset_scan AS fas ON sps.previous_scan = fas.scan_id
58

59
GROUP BY sps.site_id),
60

61
 
62

63
last_vuln_count AS (SELECT sls.site_id, count(fasv.vulnerability_id) AS last_vuln_count
64

65
FROM site_last_scan AS sls
66

67
LEFT OUTER JOIN fact_asset_scan_vulnerability_finding AS fasv ON sls.last_scan = fasv.scan_id
68

69
GROUP BY sls.site_id),
70

71
 
72

73
previous_vuln_count AS (SELECT sps.site_id, count(fasv.vulnerability_id) AS previous_vuln_count
74

75
FROM site_previous_scan AS sps
76

77
LEFT OUTER JOIN fact_asset_scan_vulnerability_finding AS fasv ON sps.previous_scan = fasv.scan_id
78

79
GROUP BY sps.site_id),
80

81
 
82

83
asset_count_change AS (SELECT lac.site_id, (lac.last_asset_count - pac.previous_asset_count)  AS asset_count_change,
84

85
CASE WHEN (lac.last_asset_count - pac.previous_asset_count)  > 1000 THEN 'MEGA INCREASE'
86

87
WHEN (lac.last_asset_count - pac.previous_asset_count)  > 100 THEN 'SLIGHT INCREASE'
88

89
WHEN (lac.last_asset_count - pac.previous_asset_count)  > 10 THEN 'TINY INCREASE'
90

91
WHEN (lac.last_asset_count - pac.previous_asset_count)  < -1000 THEN 'MEGA DECREASE'
92

93
WHEN (lac.last_asset_count - pac.previous_asset_count)  < -100 THEN 'SLIGHT DECREASE'
94

95
WHEN (lac.last_asset_count - pac.previous_asset_count)  < -10 THEN 'TINY DECREASE'
96

97
ELSE 'IGNORE'
98

99
END AS asset_status
100

101
FROM last_asset_count AS lac
102

103
JOIN previous_asset_count AS pac ON lac.site_id = pac.site_id),
104

105
 
106

107
vuln_count_change AS (SELECT lac.site_id, (lac.last_vuln_count - pac.previous_vuln_count) AS vuln_count_change,
108

109
CASE WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 1000 THEN 'MEGA INCREASE'
110

111
WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 100 THEN 'SLIGHT INCREASE'
112

113
WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 10 THEN 'TINY INCREASE'
114

115
WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -1000 THEN 'MEGA DECREASE'
116

117
WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -100 THEN 'SLIGHT DECREASE'
118

119
WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -10 THEN 'TINY DECREASE'
120

121
ELSE 'IGNORE'
122

123
END AS vuln_status
124

125
FROM last_vuln_count AS lac
126

127
JOIN previous_vuln_count AS pac ON lac.site_id = pac.site_id)
128

129
 
130

131
SELECT ds.name, lac.last_asset_count, pac.previous_asset_count, lvc.last_vuln_count, pvc.previous_vuln_count, acc.asset_count_change, acc.asset_status, vcc.vuln_count_change, vcc.vuln_status
132

133
FROM last_asset_count AS lac
134

135
JOIN previous_asset_count AS pac ON lac.site_id = pac.site_id
136

137
JOIN last_vuln_count AS lvc ON lac.site_id = lvc.site_id
138

139
JOIN previous_vuln_count AS pvc ON lac.site_id = pvc.site_id
140

141
JOIN asset_count_change AS acc ON lac.site_id = acc.site_id
142

143
JOIN vuln_count_change AS vcc ON lac.site_id = vcc.site_id
144

145
JOIN dim_site AS ds ON lac.site_id = ds.site_id