/build/static/layout/Breadcrumb_cap_w.png

SQL report for computer count and patches completed and not

Hey guys,

I saw a bunch of reports of how to make a list of servers and how to get data on each one whether they were patched or not.  I was wondering if there is a way to make it one step high level.  I was wondering if anybody has come across making a SQL report on the count of computers in a label and count or % of how they are patched as a whole group.

instead of seeing 30 servers i just wanted it to be like this
30 Servers
Total patches applied in the last 90 days
Total patches not applied in the last 90 days
Percentage of patches not applied in the last 90 days.

any help or insight would be awesome.  I am not the best at SQL writing.

5 Comments   [ + ] Show comments
  • Do you just want the report for one label or a group of labels? - chucksteel 6 years ago
    • i have hoping to get it for a group. We have maybe 6-7 labels and they wanted to split the report between workstation labels and server labels - ckiernik 6 years ago
  • Taking a closer look at this, and I'm not sure what you mean by total patches not applied in the last 90 days. Are you looking for patches that were released in the past 90 days but haven't been applied to computers? - chucksteel 6 years ago
  • That works as well but instead of seeing every patch name i just want to see a count of the total patches in for 90 days and the next would be total patches installed and total patches not installed. We are trying to generate a 1 page quick report with a graph if possible to show how many patches were not applied total in the whole organization. I have made a report from a previous post that shows us each computer line by line that shows patches complete and not complete. It is to long to take into a board room meeting for compliance. - ckiernik 6 years ago
    • Can you post your current report? - chucksteel 6 years ago
    • So you want a one-line report which has "number of patches with release dates in the last 90 days", "number of those patches which were detected as patched on devices", and then "number of those patches which were detected as not patched on devices" ?
      I don't think this would be a very good "compliance" report since some apps would have had multiple patches released in 90 days (e.g. Chrome) and some will have not had a patch released in that time (e.g. 7-zip). I would go by active and non-superseded patches instead of doing it based on release date.
      Unless you mean you want the report to be about detect/deploy jobs which have happened in the last 90 days? - JasonEgg 6 years ago
  • Sure I pretty much found it on another post here in the past but this is what it looks like

    SELECT M.NAME AS SUM,
    OS_NAME AS WINDOWS_VERSION,
    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_PATCHED
    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 PP.IMPACTID = 'Critical'
    AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 90 DAY)
    AND PPS.STATUS = 0
    AND PP.IS_SUPERCEDED = 0
    AND L.NAME rlike 'NB VMWare Servers|PowerEdge 2950 Servers|PowerEdge R420 Servers'
    GROUP BY M.NAME
    ORDER BY PERCENT_PATCHED DESC, M.NAME - ckiernik 6 years ago
  • so i guess if we are talking about patches in general i would like it just to be windows update patches. - ckiernik 6 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ