Systems Management

I am trying to get a report that shows all servers missing patches based off a cutoff date (our scheduled patching date) that ideally shows the sum of the number of patches missing and then the names of those missing patches and their Creation_Date.  

Ideal report format:

I've flipped the report to show the missing patches, creation date and sum of Notpatched, which works to get an idea of what patches are missing, but the computer names are useful as they all aren't patching in the same schedule, are running different apps, etc., and it'd be nice to have an idea of the machines that have the patching issues.  

Current Report:

Select M.NAME as MACHINE_NAME, 

SUM(PMS.DETECT_STATUS='NOTPATCHED') AS NOT_PATCHED, 

GROUP_CONCAT(PP.TITLE) AS PATCH_NAME

FROM PATCH_MACHINE_STATUS PMS 

JOIN MACHINE M ON M.ID = PMS.MACHINE_ID 

JOIN KBSYS.PATCH PP ON PP.ID = PMS.PATCH_ID 

JOIN PATCH_STATUS PPS ON PPS.PATCH_ID = PP.ID 

JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) 

JOIN LABEL L ON (ML.LABEL_ID = L.ID) 

LEFT JOIN ASSET ON ASSET.MAPPED_ID = M.ID AND ASSET.ASSET_TYPE_ID=5 

LEFT JOIN ASSET_CLASS ON ASSET_CLASS.ID = ASSET.ASSET_CLASS_ID 

WHERE ((( exists  (select 1 from LABEL, MACHINE_LABEL_JT where PMS.MACHINE_ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  AND LABEL.TYPE <> 'hidden' and (LABEL.NAME = 'label-name-here' or LABEL.NAME = 'other-label-name-here'))) ))   

AND PMS.DETECT_STATUS = "NOTPATCHED"

AND PP.IS_SUPERCEDED = 0

AND PPS.STATUS = 0 /* 0=active patches */

-- Change the date below to match up with our patching window cutoff date

AND PP.CREATION_DATE <'2019-10-16' 

group by MACHINE_NAME

order by MACHINE_NAME, PP.TITLE, CREATION_DATE

Flipped Report:

SELECT

PP.TITLE AS PATCH_NAME,

PP.CREATION_DATE,

SUM(PMS.DETECT_STATUS='NOTPATCHED') AS NOT_PATCHED

FROM PATCH_MACHINE_STATUS PMS 

JOIN MACHINE M ON M.ID = PMS.MACHINE_ID 

JOIN KBSYS.PATCH PP ON PP.ID = PMS.PATCH_ID 

JOIN PATCH_STATUS PPS ON PPS.PATCH_ID = PP.ID 

JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) 

JOIN LABEL L ON (ML.LABEL_ID = L.ID) 

LEFT JOIN ASSET ON ASSET.MAPPED_ID = M.ID AND ASSET.ASSET_TYPE_ID=5 

LEFT JOIN ASSET_CLASS ON ASSET_CLASS.ID = ASSET.ASSET_CLASS_ID 

WHERE ((( exists  (select 1 from LABEL, MACHINE_LABEL_JT where PMS.MACHINE_ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID  AND LABEL.TYPE <> 'hidden' and (LABEL.NAME = 'label1' or LABEL.NAME = 'label2'))) ))   

AND PMS.DETECT_STATUS = "NOTPATCHED"

AND PP.IS_SUPERCEDED = 0

AND PPS.STATUS = 0 /* 0=active patches */

/* Change the date below to match up with our patching window cutoff date */

AND PP.CREATION_DATE <'2019-10-16' 

GROUP by PP.TITLE

order by CREATION_DATE, PP.TITLE