Patching Report, Part Deux
Backstory: I was asked to give a detailed report of patch status because the summary report I provided didn't give enough info. The user wanted to see for each patch, which server had it and which server did not. When you ask for detail, you get it...and 500 pages of details turned out to be too much. The user loved the detail, there was just too much of it. And conceivably, the report wouldn't get any smaller because it would always show total detail. Ugh. So now I am tasked with adding additional analysis in the report that is (currently) beyond me.
Current SQL yields a report like this:
Patch A (KB12345)
ServerA Patched
ServerB Patched
ServerC NotPatched
Patch B (KB67890)
ServerA Patched
ServerB Patched
ServerC Patched
::Lather, rinse, repeat for hundreds of pages::
The current request/demand is to take this report and when a patch is fully deployed, (that is, every server has a status of "patched" for that particular patch) then they want the patch to disappear from the report altogether. So in the example above, PatchB and its detail would not show up in the report, but PatchA and its detail would remain (because one server has yet to be patched).
So my question for all the SQL geniuses out there: Can this type of magic be performed in the SQL for my report, or am I destined to dump the data to Excel/Access and take it further? Obviously, I am crossing my fingers for a SQL-based answer so I don't interrupt my Minecraft time. :)
I welcome all tips and suggestions, apart from getting rid of my demanding user. She is actually quite pleasant. My SQL is below for all who would like to take a crack at my problem. (BTW, PatchName is in the "Break on Columns" field.)
Thanks for any/all help!
Mike
Current SQL yields a report like this:
Patch A (KB12345)
ServerA Patched
ServerB Patched
ServerC NotPatched
Patch B (KB67890)
ServerA Patched
ServerB Patched
ServerC Patched
::Lather, rinse, repeat for hundreds of pages::
The current request/demand is to take this report and when a patch is fully deployed, (that is, every server has a status of "patched" for that particular patch) then they want the patch to disappear from the report altogether. So in the example above, PatchB and its detail would not show up in the report, but PatchA and its detail would remain (because one server has yet to be patched).
So my question for all the SQL geniuses out there: Can this type of magic be performed in the SQL for my report, or am I destined to dump the data to Excel/Access and take it further? Obviously, I am crossing my fingers for a SQL-based answer so I don't interrupt my Minecraft time. :)
I welcome all tips and suggestions, apart from getting rid of my demanding user. She is actually quite pleasant. My SQL is below for all who would like to take a crack at my problem. (BTW, PatchName is in the "Break on Columns" field.)
Thanks for any/all help!
Mike
select P.TITLE AS PatchName, ucase(MACHINE.NAME) as ServerName, LABEL.Name as LabelName, IP, S.STATUS, S.DEPLOY_STATUS_DT as InstalledDate
from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P, MACHINE_LABEL_JT, LABEL
where
MACHINE.ID = S.MACHINE_ID and
S.PATCHUID = P.UID and
P.IMPACTID = 'CRITICAL' and
MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and
MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and
LABEL.Name IN ('DR Servers','Corporate Servers','Remote Servers')
order by P.TITLE,MACHINE.Name
0 Comments
[ + ] Show comments
Answers (6)
Please log in to answer
Posted by:
dchristian
12 years ago
Does this do what your looking for?
SELECT P.TITLE AS PATCHNAME,
Ucase(MACHINE.NAME) AS SERVERNAME,
LABEL.NAME AS LABELNAME,
IP,
S.STATUS,
S.DEPLOY_STATUS_DT AS INSTALLEDDATE
FROM PATCHLINK_MACHINE_STATUS S,
MACHINE,
KBSYS.PATCHLINK_PATCH P,
MACHINE_LABEL_JT,
LABEL
WHERE MACHINE.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND P.IMPACTID = 'CRITICAL'
AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
AND LABEL.NAME IN ( 'DR Servers', 'Corporate Servers', 'Remote Servers' )
AND P.UID IN (SELECT DISTINCT PATCHUID
FROM PATCHLINK_MACHINE_STATUS P
WHERE STATUS != 'PATCHED')
ORDER BY P.TITLE,
MACHINE.NAME
Posted by:
scottlutz
12 years ago
Why not just add:
S.STATUS = 'NOTPATCHED' AND
(it seemed to do a lovely job shortening my report from 389 lines to 19) :)
S.STATUS = 'NOTPATCHED' AND
(it seemed to do a lovely job shortening my report from 389 lines to 19) :)
Posted by:
mbretzke
12 years ago
Thanks for the response! Adding S.STATUS = 'NOTPATCHED' AND strips out all the entries where the patch has a status of "patched." The user's requirement is to see both statuses in the report. I am just hoping there is an easy way to prevent a patch that is fully-deployed (i.e. all servers show it as "patched") from showing up at all. I'm rather tired of dumping it in Excel and becoming cross-eyed as I "clean up" the report. I have been monkeying around with grouping, but nothing yet.
I tried talking her into a report showing just unpatched statuses, but from her perspective, seeing that a patch was installed on a test server (and when), helps her determine if the patch can be sent to the unpatched production server next to it on the list.
I tried talking her into a report showing just unpatched statuses, but from her perspective, seeing that a patch was installed on a test server (and when), helps her determine if the patch can be sent to the unpatched production server next to it on the list.
Posted by:
steelc
12 years ago
In the UI when viewing the patch list there is a column for the number of unpatched computers. I have looked through the various tables in the MySQL database but I cannot find where that information is stored. I also can't find where the number of patched machines or machines with an error is stored. If you could find that information, then you should be able to include that table and then only show patches that have unpatched != 0.
Posted by:
mbretzke
12 years ago
David,
Thank you! Thank you! Thank you! Thank you! Thank you!
This is exacty the kind of report my user and I were looking for. Thank you very much for your help. She has 300 fewer pages than before (we are shamefully behind in patches, obviously) and I come out looking like a rockstar.
I appreciate the help from you and everyone else.
Mike
Thank you! Thank you! Thank you! Thank you! Thank you!
This is exacty the kind of report my user and I were looking for. Thank you very much for your help. She has 300 fewer pages than before (we are shamefully behind in patches, obviously) and I come out looking like a rockstar.
I appreciate the help from you and everyone else.
Mike
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.