1
BEGIN
2
-- Create exception handler
3
DECLARE EXIT HANDLER FOR SQLEXCEPTION
4
BEGIN -- On exception
5
-- Get any tables back to their original state
6
ROLLBACK;
7
-- Clean up tables
8
DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_NEW;
9
DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_OLD;
10
DROP TEMPORARY TABLE IF EXISTS _tempHotfixForMicrosoftNonCompliantPatchCounts;
11
DROP TEMPORARY TABLE IF EXISTS _tempComputers;
12
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Set isolation level back to default
13
RESIGNAL; -- Rethrow exception
14
END;
15
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- Allows selects in a nonlocking fashion.
16
START TRANSACTION;
17
-- Create the table if its not found so we dont blow up.
18
-- Since we rename the table there is a veeeeery small chance the stored proc gets interupted before finishing
19
-- Which if it happens at the exact right moment, the system could be left without a table!
20
CREATE TABLE IF NOT EXISTS `MicrosoftNonCompliantPatchCounts` (
21
`ComputerId` INT(11) NOT NULL,
22
`LowSeverityCount` INT(11) DEFAULT 0,
23
`ModerateSeverityCount` INT(11) DEFAULT 0,
24
`ImportantSeverityCount` INT(11) DEFAULT 0,
25
`CriticalSeverityCount` INT(11) DEFAULT 0,
26
`UnspecifiedSeverityCount` INT(11) DEFAULT 0,
27
`LowCvssCount` INT(11) DEFAULT 0,
28
`MediumCvssCount` INT(11) DEFAULT 0,
29
`HighCvssCount` INT(11) DEFAULT 0,
30
`TotalNonCompliantCount` INT(11) DEFAULT 0,
31
PRIMARY KEY (`ComputerId`)
32
) ENGINE=INNODB DEFAULT CHARSET=utf8;
33
#Cleanup in case something went wrong previously
34
DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_NEW;
35
DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_OLD;
36
DROP TEMPORARY TABLE IF EXISTS _tempHotfixForMicrosoftNonCompliantPatchCounts;
37
DROP TEMPORARY TABLE IF EXISTS _tempComputers;
38
-- Create temp table of computerIds
39
CREATE TEMPORARY TABLE IF NOT EXISTS _tempComputers
40
(
41
INDEX (`ComputerId`)
42
)ENGINE=MEMORY
43
SELECT `ComputerId` FROM `ComputerPatchingStats`;
44
-- Create temporary copy of hotfix to reduce time spent locking the hotfix table NOTE: WHERE clause is used here so we do not need it on anything else
45
CREATE TEMPORARY TABLE IF NOT EXISTS _tempHotfixForMicrosoftNonCompliantPatchCounts
46
( INDEX (`ComputerId`)
47
) ENGINE=MEMORY
48
SELECT `ComputerId`,
49
SUM(IF(`Severity`='low',1,0)) AS LowSeverityCount,
50
SUM(IF(`Severity`='moderate',1,0)) AS ModerateSeverityCount,
51
SUM(IF(`Severity`='important',1,0)) AS ImportantSeverityCount,
52
SUM(IF(`Severity`='critical',1,0)) AS CriticalSeverityCount,
53
SUM(IF(`Severity`='', 1, 0)) AS UnspecifiedSeverityCount,
54
SUM(IF(`HotfixData`.`Cvss` > 0 AND `HotfixData`.`Cvss` < 4, 1, 0)) AS LowCvssCount,
55
SUM(IF(`HotfixData`.`Cvss` >= 4 AND `HotfixData`.`Cvss` < 7, 1, 0)) AS MediumCvssCount,
56
SUM(IF(`HotfixData`.`Cvss` >= 7, 1, 0)) AS HighCvssCount,
57
COUNT(*) AS TotalNonCompliantCount
58
FROM Hotfix JOIN HotfixData USING (HotfixId,OS) WHERE Approved = 2 AND Installed = 0 GROUP BY ComputerId;
59
-- Create table to hold collected Non-Compliant Patch Counts
60
CREATE TABLE IF NOT EXISTS MicrosoftNonCompliantPatchCounts_NEW LIKE MicrosoftNonCompliantPatchCounts;
61
TRUNCATE TABLE MicrosoftNonCompliantPatchCounts_NEW;
62
-- Tested against 10k computers ~2 million hotfixes. Runs in 6 seconds.
63
INSERT INTO MicrosoftNonCompliantPatchCounts_NEW
64
(
65
`ComputerId`, `LowSeverityCount`, `ModerateSeverityCount`, `ImportantSeverityCount`, `CriticalSeverityCount`, `UnspecifiedSeverityCount`,
66
`LowCvssCount`, `MediumCvssCount`, `HighCvssCount`, `TotalNonCompliantCount`
67
)
68
(
69
SELECT _tempComputers.ComputerId,
70
IFNULL(LowSeverityCount, 0),
71
IFNULL(ModerateSeverityCount, 0),
72
IFNULL(ImportantSeverityCount, 0),
73
IFNULL(CriticalSeverityCount, 0),
74
IFNULL(UnspecifiedSeverityCount, 0),
75
IFNULL(LowCvssCount, 0),
76
IFNULL(MediumCvssCount, 0),
77
IFNULL(HighCvssCount, 0),
78
IFNULL(TotalNonCompliantCount, 0)
79
FROM _tempComputers LEFT JOIN _tempHotfixForMicrosoftNonCompliantPatchCounts USING (ComputerId)
80
);
81
-- If data was successfully collected by query, go ahead and rename the table
82
RENAME TABLE MicrosoftNonCompliantPatchCounts TO MicrosoftNonCompliantPatchCounts_OLD;
83
-- Rename the new table to the proper name
84
RENAME TABLE MicrosoftNonCompliantPatchCounts_NEW TO MicrosoftNonCompliantPatchCounts;
85
COMMIT;
86
-- Clean up temp and unneaded tables
87
DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_NEW;
88
DROP TABLE IF EXISTS MicrosoftNonCompliantPatchCounts_OLD;
89
DROP TEMPORARY TABLE IF EXISTS _tempHotfixForMicrosoftNonCompliantPatchCounts;
90
DROP TEMPORARY TABLE IF EXISTS _tempComputers;
91
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Set isolation level back to default
92
END