/bundles/itninjaweb/img/Breadcrumb_cap_w.png
• We need to make a report similar to “For each Machine, what patches are installed”

o for each machine in machine label, what patches in patch label are installed (patched successfully) and/or be able to enter a Date Range
o a separate failure report for the above machine and patch labels
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

0
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%
Answered 03/22/2011 by: sprintgeek
Senior Yellow Belt

Please log in to comment
0
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
Answered 10/27/2011 by: mstoyles
Purple Belt

Please log in to comment
0
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!
Answered 10/28/2011 by: dchristian
Red Belt

Please log in to comment
0
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!
Answered 11/09/2011 by: sprintgeek
Senior Yellow Belt

Please log in to comment
0
This works great, Thank You!
Answered 01/26/2012 by: azd_dmt
Senior Yellow Belt

Please log in to comment