/build/static/layout/Breadcrumb_cap_w.png

MySQL Query: Grabs a full inventory of all computers that are connecting to the KBOX and displays number of patches patched/total number of patches available

Hello Folks,

I wrote this query with the help of a developer at my office and I thought I'd share it. It grabs a list of all computers that have connected to the KBOX in the last X hours/days and displays how many patches each system has downloaded/total number of patches available to each system.

It also gets some information about some software that you have installed on each computer.

 Select M.ID as 'MACHINE ID',
       M.NAME as 'MACHINE_NAME',
       M.OS_NAME as 'OPERATING SYSTEM',
       M.SERVICE_PACK as 'SERVICE PACK',
       M.OS_VERSION as OS_VERSION,
       M.SERVICE_PACK as SERVICE_PACK,
       M.LAST_SYNC as 'LAST_CHECKIN',
       s.DISPLAY_NAME as ANTIVIRUS_NAME,
       s.DISPLAY_VERSION as ANTIVIRUS_VERSION,
COUNT(PP.TITLE) AS TOT_PATCHES,
SUM(IF(MS.STATUS='PATCHED', 1, 0)) AS TOT_PATCHED,
CONCAT(CAST(SUM(IF(MS.STATUS='PATCHED', 1, 0)) AS CHAR),'/',CAST(COUNT(PP.TITLE) AS CHAR)) AS PATCH_STR
FROM MACHINE M
left outer join PATCHLINK_MACHINE_STATUS MS ON M.ID = MS.MACHINE_ID
left outer JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
left outer JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
left outer join ORG1.MACHINE_SOFTWARE_JT msj ON M.ID = msj.MACHINE_ID
left outer join ORG1.SOFTWARE s ON msj.SOFTWARE_ID = s.ID
WHERE msj.SOFTWARE_ID = 156
AND '2013-05-10 00:00:00' <= M.LAST_SYNC
group by MACHINE_NAME
order by MACHINE_NAME


Comments

  • Nice post. The only change I would recommend is how you select the date. I normally use a relative function, unless you specifically want machines that have checked in since May 10th. Instead I use something like this:
    AND M.LAST_SYNC > DATE_SUB(NOW(), INTERVAL 7 DAY)

    You can adjust the Interval easily for how far you want to go back. - chucksteel 11 years ago
  • Heh, I didn't quite get what you meant, then today I ran the report and I was like, Oh I see...

    Thanks for the suggestion! - jobla 11 years ago
This post is locked

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