I need to know how I can create a report to show what patches got installed to our environment in the last 30 days for our audit department. I have a few examples of how my labels are setup:

Patch - Adobe Critical Windows
 
     
 
         
What they are asking for is a report that will show what patches were pushed to each machine and the percentage of success that the patch run was able to complete. Ex. Patched 500/1000   50% sucess rate.
 
The old WSUS team was able to hand them this report after each patch update and I REALLY need to give them this report. ANY help would be greatly appreciated!!
 
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

OK, I think I see where you're going with this now.  Try these - one lists each machine with relevant patches, the other lists the patches themselves.  Change the L.NAME in line 18 (machines report) and line 20 (patches report) to match the labels in your own environment and see if these will work (or at least get you closer to what you need).

Hope that helps!

John

______________________________________________

Machines report

*SQL Query*
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
PP.TITLE AS PATCH_NAME,
PP.IMPACTID as IMPACT,
(CASE PPS.STATUS
  WHEN 0 THEN 'Active'
  WHEN 1 THEN 'Inactive'
  WHEN 4 THEN 'Disabled'
  ELSE 'Unknown' END)
AS PATCH_STATUS,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = 'server'
AND PP.IMPACTID = 'Critical'
AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY)
AND PP.VENDOR rlike 'adobe|microsoft'
AND PPS.STATUS in (0)
GROUP BY M.NAME, PATCH_NAME
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE

*Break on Columns*
MACHINE_NAME

______________________________________________

Patches report

*SQL Query*
SELECT PP.TITLE AS PATCH_NAME,
PP.IMPACTID as IMPACT,
(CASE PPS.STATUS
  WHEN 0 THEN 'Active'
  WHEN 1 THEN 'Inactive'
  WHEN 4 THEN 'Disabled'
  ELSE 'Unknown' END)
AS PATCH_STATUS,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = 'server'
AND PP.IMPACTID = 'Critical'
AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY)
AND PP.VENDOR rlike 'adobe|microsoft'
AND PPS.STATUS in (0)
GROUP BY PATCH_NAME
ORDER BY PP.RELEASEDATE desc, PP.TITLE

Answered 10/18/2012 by: jverbosk
Red Belt

Please log in to comment

Answers

2
Answered 10/18/2012 by: nshah
Red Belt

  • I'm assuming that's were a couple lines of cwest311's query came from. Anyways, thanks for the heads up, I hadn't seen that one yet!

    John
Please log in to comment
1

First and foremost, cheers to the both of you for the assist!!! I have a majority of what I need but I am having one more issue. I have been told that the audit department just wants the percentage of servers and workstations. I created a smart label named "All Servers" and "All Workstations" but I just cant figure out where to place it. Here is an example of my script:

 

SELECT RELEASEDATE,VENDOR,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,
IMPACTID,
(CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END) AS PATCH_STATUS,
SUM(P.STATUS='PATCHED') AS PATCHED,SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(P.STATUS='PATCHED')/(SUM(P.STATUS='PATCHED')+SUM(P.STATUS='NOTPATCHED')))*100,0) AS PERCENT,
IDENTIFIER,TITLE
FROM KBSYS.PATCHLINK_PATCH 
LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID)
LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID)
LEFT JOIN PATCHLINK_PATCH_STATUS ON PATCHLINK_PATCH_STATUS .PATCHUID = PATCHLINK_PATCH.UID
LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
WHERE (RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY))
AND ((VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.'))
GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY LABEL_NAME_GROUPED desc,PATCH_STATUS desc

I read in one of the threads on here that it needed to be placed above the "group by" entry but when I do that I get nothing in return......

Answered 10/17/2012 by: cwest311
Second Degree Blue Belt

  • Is the Smart label targeting patches or machines?

    As for the query, try extending your WHERE statement like this:

    WHERE (RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY))
    AND ((VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.'))
    AND LABEL.NAME = 'All Servers'
    GROUP BY KBSYS.PATCHLINK_PATCH.ID

    And for workstations:

    WHERE (RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY))
    AND ((VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.'))
    AND LABEL.NAME = 'All WORKSTATIONS'
    GROUP BY KBSYS.PATCHLINK_PATCH.ID

    Just keep in mind that limiting/filtering results is usually done via WHERE statements, the same way you filtered by release date and vendors. Get the main query working and then chop it down as needed. ^_^

    John
  • Well as Im sure you can tell..... A SQL guru I am not. The smart label is targeting machines in our environment and he just wans the patches that came out for a certain month and how many of those patches were deployed to our farm if that helps you help me... lol.

    When I extend the 'WHERE' statement, MySQL returned 0 rows.

    Am I missing something here?
  • No problem, I'm not a SQL guru either - I just like puzzles! ^_^

    See my new answer above - hopefully that gets you 95% there.

    John
Please log in to comment
0

Here are a couple more articles that might help, in addition to what nshah suggested (the second to help with the SQL if you aren't too familiar with it):

http://www.itninja.com/blog/view/k1000-patching-setup-tips-things-i-have-learned-ldap-smart-labels-sql-reports

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

John

Answered 10/16/2012 by: jverbosk
Red Belt

Please log in to comment
0

Answered 10/17/2012 by: cwest311
Second Degree Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share