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