ITNinja

Attempts so far:

From Dell Pre-Sales:
Try creating a report with the reporting wizard, select patch and then chose these fields, let me know if this gets you closer to what you are looking for ( this will be for all patches but if this is the info you need we should be able to filter by label )

Query it spat out:

SELECT TITLE, LABEL.NAME AS LABEL_NAME, (CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END) AS PATCH_STATUS, TARGET_ALL_MACHINES, SUM(P.STATUS='PATCHED') AS PATCHED 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 (1 in (select 1 from LABEL, PATCHLINK_PATCH_LABEL_JT where PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID AND LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID and LABEL.NAME LIKE '%2011-1-31 Server Patches%')) AND PATCHLINK_PATCH_STATUS.STATUS = 'Active' GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY TITLE asc,LABEL.NAME asc,PATCHLINK_PATCH_STATUS.STATUS asc,TARGET_ALL_MACHINES asc,PATCHED asc

This just spits out a list of active patches, but I think it is Just the patches in the 2011-1-31 Server Patches label! Just need to format it like the “For each Machine, what patches are installed” report and only include machines in a specified label like Test Servers.

For the machine label, do we just need to add something like:
LABEL.NAME LIKE '%Test Servers%

I'm trying to generate the same report - patch list by date and machine label name..

have you had any luck with this?

Thank you

Try this:
SELECT DISTINCT CASE
WHEN MACHINE.SYSTEM_DESCRIPTION = ' THEN MACHINE.NAME
WHEN MACHINE.SYSTEM_DESCRIPTION != ' THEN
Concat(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION)
END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME,
P.IDENTIFIER AS KB_ARTICLE
FROM PATCHLINK_MACHINE_STATUS S,
MACHINE,
KBSYS.PATCHLINK_PATCH P,
PATCHLINK_PATCH_LABEL_JT PL,
LABEL PATCH_LABEL,
LABEL MACHINE_LABEL,
MACHINE_LABEL_JT ML
WHERE MACHINE.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND PL.PATCHUID = P.UID
AND PATCH_LABEL.ID = PL.LABEL_ID
AND ML.MACHINE_ID = MACHINE.ID
AND MACHINE_LABEL.ID = ML.LABEL_ID
AND S.STATUS = 'PATCHED'
AND PATCH_LABEL.NAME IN('win 7 x64 critical < 60')
AND MACHINE_LABEL.NAME IN ('workstations')
ORDER BY MACHINE_NAME,
P.TITLE;

You should just have to change the:
AND PATCH_LABEL.NAME IN('win 7 x64 critical < 60')
AND MACHINE_LABEL.NAME IN ('workstations')
For you patch or machine label.

Hope this helps!

I am using:

SELECT LABL.NAME as MACHINE_GROUP,
L.NAME as PATCH_GROUP,
MACH.NAME as MACHINE,
PP.TITLE AS PATCH_TITLE,
CASE PLMS.STATUS
WHEN 'PATCHED' THEN 'Patched'
WHEN 'NOTPATCHED' THEN 'NOT Patched'
ELSE 'Unknown'
END as PATCH_STATUS,
PLMS.STATUS_DT as STATUS_AS_OF,
PLMS.DEPLOY_STATUS_DT as DEPLOYED_AS_OF
FROM ORG1.PATCHLINK_PATCH_LABEL_JT PPLJT
join
ORG1.LABEL L
on L.ID = PPLJT.LABEL_ID
join
KBSYS.PATCHLINK_PATCH PP
on PP.UID = PPLJT.PATCHUID
join
ORG1.PATCHLINK_MACHINE_STATUS PLMS
ON PP.UID = PLMS.PATCHUID
JOIN
ORG1.MACHINE MACH
ON MACH.ID = PLMS.MACHINE_ID
JOIN
ORG1.MACHINE_LABEL_JT MLJT
ON MACH.ID = MLJT.MACHINE_ID
JOIN
ORG1.LABEL LABL
ON MLJT.LABEL_ID = LABL.ID
WHERE L.NAME IN ('patch label name')
AND LABL.NAME IN ('machine label name')
order by LABL.NAME,
PPLJT.LABEL_ID,
MACH.NAME




just replace patch label name and machine label name (leave the ' in) and use to your liking!

This works great, Thank You!