September 1, 2015 at 8:07 am
I need to calculate the success rate of our OS Patch deployments. the data from system is stored in SQL with corresponding states (installed, missing, ...)
I would need to provide monthly report that shows how successful the deployment was for particular patchgroup.
I have the following 2 dummy tables (just used as example)
table5 ==> Table containing patch groups + patches
table6 ==> Table with machines names, patches and patch state
select * from table5
pgrouppatch
GROUP1PATCH1
GROUP1PATCH2
GROUP1PATCH3
GROUP1PATCH4
GROUP1PATCH5
GROUP2PATCH6
GROUP2PATCH7
GROUP3PATCH8
GROUP3PATCH9
GROUP3PATCH10
select * from table6
machinenamepatch state
MACHINE1 PATCH1 INSTALLED
MACHINE1 PATCH2 INSTALLED
MACHINE1 PATCH3 INSTALLED
MACHINE1 PATCH4 INSTALLED
MACHINE1 PATCH5 INSTALLED
MACHINE1 PATCH6 INSTALLED
MACHINE1 PATCH7 MISSING
MACHINE1 PATCH8 INSTALLED
MACHINE1 PATCH9 INSTALLED
MACHINE1 PATCH10 MISSING
MACHINE2 PATCH1 MISSING
MACHINE2 PATCH2 MISSING
MACHINE2 PATCH3 MISSING
MACHINE2 PATCH4 INSTALLED
MACHINE2 PATCH5 INSTALLED
MACHINE2 PATCH6 INSTALLED
MACHINE2 PATCH7 MISSING
MACHINE2 PATCH8 INSTALLED
MACHINE2 PATCH9 MISSING
MACHINE2 PATCH10 MISSING
MACHINE3 PATCH1INSTALLED
MACHINE3 PATCH2INSTALLED
MACHINE3 PATCH3INSTALLED
MACHINE3 PATCH4INSTALLED
MACHINE3 PATCH5INSTALLED
MACHINE3 PATCH6INSTALLED
MACHINE3 PATCH7MISSING
MACHINE3 PATCH8INSTALLED
MACHINE3 PATCH9INSTALLED
MACHINE3 PATCH10MISSING
Result would be
pgroup install missing
group1 80% 20%
group2 50% 50%
group3 55% 45%
Ideally I would like to do this in T-SQL but if necessary can also do this in .NET Function.
only mention this but both Patchgroup and machines are dynamic each month can be different.
Any help suggestions are appreciated.
below are the sql scripts to create tables and populate with data
CREATE TABLE [dbo].[table5](
[pgroup] [varchar](128) NOT NULL,
[patch] [nvarchar](128) NOT NULL
)
CREATE TABLE [dbo].[table6](
[machinename] [varchar](128) NOT NULL,
[patch] [nvarchar](128) NOT NULL,
[state] [varchar](128) NOT NULL
)
INSERT INTO TABLE5 VALUES ('GROUP1', 'PATCH1')
INSERT INTO TABLE5 VALUES ('GROUP1', 'PATCH2')
INSERT INTO TABLE5 VALUES ('GROUP1', 'PATCH3')
INSERT INTO TABLE5 VALUES ('GROUP1', 'PATCH4')
INSERT INTO TABLE5 VALUES ('GROUP1', 'PATCH5')
INSERT INTO TABLE5 VALUES ('GROUP2', 'PATCH6')
INSERT INTO TABLE5 VALUES ('GROUP2', 'PATCH7')
INSERT INTO TABLE5 VALUES ('GROUP3', 'PATCH8')
INSERT INTO TABLE5 VALUES ('GROUP3', 'PATCH9')
INSERT INTO TABLE5 VALUES ('GROUP3', 'PATCH10')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH1','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH2','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH3','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH4','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH5','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH6','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH7','MISSING')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH8','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH9','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE1', 'PATCH10','MISSING')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH1','MISSING')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH2','MISSING')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH3','MISSING')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH4','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH5','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH6','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH7','MISSING')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH8','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH9','MISSING')
INSERT INTO table6 VALUES ('MACHINE2', 'PATCH10','MISSING')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH1','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH2','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH3','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH4','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH5','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH6','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH7','MISSING')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH8','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH9','INSTALLED')
INSERT INTO table6 VALUES ('MACHINE3', 'PATCH10','MISSING')
September 1, 2015 at 8:36 am
This worked for me.
SELECT DISTINCT
t5.pgroup,
CONVERT(NUMERIC(5,2),(CAST(COUNT(CASE WHEN t6.state = 'INSTALLED' THEN 1 ELSE NULL END) OVER (PARTITION BY t5.pgroup) AS NUMERIC)/CAST(COUNT(t6.state) OVER (PARTITION BY t5.pgroup) AS NUMERIC))*100) AS INSTALLED,
CONVERT(NUMERIC(5,2),(CAST(COUNT(CASE WHEN t6.state = 'MISSING' THEN 1 ELSE NULL END) OVER (PARTITION BY t5.pgroup) AS NUMERIC)/CAST(COUNT(t6.state) OVER (PARTITION BY t5.pgroup) AS NUMERIC))*100) AS MISSING
FROM
table5 t5
JOIN table6 t6 ON t5.patch = t6.patch
ORDER BY t5.pgroup
Cheers,
September 1, 2015 at 8:40 am
Hi,
this should do the trick :
SELECT pgroup,
CAST(installed as MONEY)/(installed+missing)*100 as percent_installed,
CAST(missing as MONEY)/(installed+missing)*100 as percent_missing
FROM (SELECT t5.pgroup, SUM(CASE WHEN t6.state='INSTALLED' THEN 1 ELSE 0 END) as installed, SUM(CASE WHEN t6.state='MISSING' THEN 1 ELSE 0 END) as missing
FROM table5 t5
JOIN table6 t6 ON t6.patch=t5.patch
GROUP BY t5.pgroup) as Q
Of course, it only works correct in defined conditions. For example, that there is no other state except INSTALLED and MISSING... but your question was very well written and from the sample data it seems that this should be OK.
Vladan
September 1, 2015 at 8:43 am
A bit simpler:
SELECT
t5.pgroup,
CONVERT(NUMERIC(5,2),SUM(CASE WHEN t6.state = 'INSTALLED' THEN 100. END) / COUNT(*)) AS INSTALLED,
CONVERT(NUMERIC(5,2),SUM(CASE WHEN t6.state = 'MISSING' THEN 100. END) / COUNT(*)) AS MISSING
FROM
table5 t5
JOIN table6 t6 ON t5.patch = t6.patch
GROUP BY t5.pgroup
ORDER BY t5.pgroup
September 1, 2015 at 8:53 am
All, thx for the quick solution one thing I forgot to mention how would I also include the column that shows the # machines this patchgroup got deployed?
pgroup #machines installed missing
group1 3 80.00 20.00
group2 3 50.00 50.00
group3 3 55 45
Thx again for all the help
September 1, 2015 at 8:54 am
Luis Cazares (9/1/2015)
A bit simpler:
SELECT
t5.pgroup,
CONVERT(NUMERIC(5,2),SUM(CASE WHEN t6.state = 'INSTALLED' THEN 100. END) / COUNT(*)) AS INSTALLED,
CONVERT(NUMERIC(5,2),SUM(CASE WHEN t6.state = 'MISSING' THEN 100. END) / COUNT(*)) AS MISSING
FROM
table5 t5
JOIN table6 t6 ON t5.patch = t6.patch
GROUP BY t5.pgroup
ORDER BY t5.pgroup
Nice touch forcing the INT conversion...I keep forgetting about that. Also I never thought to avoid the multiplication by adding 100 instead of 1. Good tip!
September 1, 2015 at 8:54 am
Solution of Luis seems to me to be the best of the 3 variants. My code has rounding problem (returns 55.55 instead of 55.56), which I overlooked before and only noticed it when comparing the results.
September 1, 2015 at 8:58 am
denis.gendera (9/1/2015)
All, thx for the quick solution one thing I forgot to mention how would I also include the column that shows the # machines this patchgroup got deployed?pgroup #machines installed missing
group1 3 80.00 20.00
group2 3 50.00 50.00
group3 3 55 45
Thx again for all the help
Just add a COUNT(DISTINCT).
SELECT
t5.pgroup,
COUNT(DISTINCT machinename) AS #machines,
CONVERT(NUMERIC(5,2),SUM(CASE WHEN t6.state = 'INSTALLED' THEN 100. END) / COUNT(*)) AS INSTALLED,
CONVERT(NUMERIC(5,2),SUM(CASE WHEN t6.state = 'MISSING' THEN 100. END) / COUNT(*)) AS MISSING
FROM
table5 t5
JOIN table6 t6 ON t5.patch = t6.patch
GROUP BY t5.pgroup
ORDER BY t5.pgroup
September 1, 2015 at 9:01 am
Vladan (9/1/2015)
Solution of Luis seems to me to be the best of the 3 variants. My code has rounding problem (returns 55.55 instead of 55.56), which I overlooked before and only noticed it when comparing the results.
That's why I avoid the money data type.
September 2, 2015 at 1:20 am
All,
thx again for all the help much appreciated.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply