K1000 Custom Report broken in Version 10 - MySql Table KBSYS.PATCHLINK_PATCH missing in Version 10
The report below was used to give me all computers in a specific label that were coming out with a specific Deploy Error Code 102 (required a reboot), but could be changed to find whatever code we needed.
SELECT M.NAME AS ComputerName,
PP.TITLE AS DISPLAY_NAME,
STATUS,
STATUS_DT AS Date,
DEPLOY_STATUS,
DEPLOY_ERROR_CODE
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE ((( exists (select 1 from LABEL, MACHINE_LABEL_JT where MS.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')) ))
AND DEPLOY_ERROR_CODE="102"
Order by M.Name
What I see now after the upgrade to version 10:
Error Running Report
mysqli error: [1142: SELECT command denied to user 'R76'@'localhost' for table 'PATCHLINK_PATCH'] in EXECUTE(" SELECT M.NAME AS ComputerName, PP.TITLE AS DISPLAY_NAME, STATUS, STATUS_DT AS Date, DEPLOY_STATUS, DEPLOY_ERROR_CODE FROM PATCHLINK_MACHINE_STATUS MS JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID JOIN MACHINE M ON M.ID = MS.MACHINE_ID WHERE ((( exists (select 1 from LABEL, MACHINE_LABEL_JT where MS.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')) )) AND DEPLOY_ERROR_CODE="102" Order by M.Name")
I went through the Administrator Guide appendix between the Version 9.1 and Version 10
Version 9.1 under KBSys tables has: "PATCHLINK_PATCH Security: Patch Management," but it's missing from the Version 10 and there's no What's changed with the tables in the Admin guide.
Anyone resolved this yet?
Answers (3)
Hi,
version 10 has a new and overhauled Patching Module, probably all reports or custom reports and labels, might not work:
https://support.quest.com/kace-systems-management-appliance/kb/264454/deprecated-patching-items-in-the-10-0-release
I would start there.
and here:
https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0
Comments:
-
Thanks, that definitely gives a good starting point to begin rebuilding all my different reports. - Tim_Lawhead 4 years ago
Agree with the others to reference that documentation. Here's your SQL corrected with the new 10.0 version semantics to use as a starting reference example:
SELECT
M.NAME AS ComputerName,
PP.TITLE AS DISPLAY_NAME,
DETECT_STATUS,
STATUS_DT AS Date,
DEPLOY_STATUS,
DEPLOY_ERROR_CODE
FROM
PATCH_MACHINE_STATUS MS
JOIN
KBSYS.PATCH PP ON PP.ID = MS.PATCH_ID
JOIN
MACHINE M ON M.ID = MS.MACHINE_ID
WHERE
(((EXISTS( SELECT
1
FROM
LABEL,
MACHINE_LABEL_JT
WHERE
MS.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'))))
AND DEPLOY_ERROR_CODE = '102'
ORDER BY M.Name
Hope this helps!
Ryan
Comments:
-
Ryan - Thanks for the query refresh and saving me some time digging into it. Much appreciated. - Tim_Lawhead 4 years ago
-
Ryan can you help with this query?
select M.NAME as COMPUTER_NAME, PP.TITLE as PATCH_NAME, PP.IMPACTID as PATCH_IMPACT, MS.DEPLOY_STATUS as PATCH_STATUS, MS.DEPLOY_STATUS_DT as INSTALL_DATE from MACHINE M
left join PATCHLINK_MACHINE_STATUS MS on MS.MACHINE_ID = M.ID
left join KBSYS.PATCHLINK_PATCH PP on PP.UID = MS.PATCHUID
left join PATCHLINK_SCHEDULE PS on PS.ID = MS.SCHEDULE_ID
where MS.DEPLOY_STATUS_DT > subdate(now(), interval 10 day)
and PS.DESCRIPTION = 'Servers - Revised Patch Group 1'
ORDER BY M.NAME, PP.TITLE - jcaine 4 years ago-
This will run in mine but I don't have the label so may still need some tweaking:
SELECT
M.NAME as COMPUTER_NAME,
PP.TITLE as PATCH_NAME,
PP.Severity as PATCH_Severity,
MS.DEPLOY_STATUS as PATCH_STATUS,
MS.DEPLOY_STATUS_DT as INSTALL_DATE
FROM MACHINE M
LEFT JOIN PATCH_MACHINE_STATUS MS on MS.MACHINE_ID = M.ID
left join KBSYS.PATCH PP on PP.ID = MS.PATCH_ID
left join PATCH_SCHEDULE_MACHINE_STATUS PS on PS.MACHINE_ID = M.ID
left join PATCH_SCHEDULE PSS on PSS.ID = PS.PATCH_SCHEDULE_ID
where MS.DEPLOY_STATUS_DT > subdate(now(), interval 10 day)
and PSS.DESCRIPTION = 'Servers - Revised Patch Group 1'
ORDER BY M.NAME, PP.TITLE - Tim_Lawhead 4 years ago-
Thank you Tim! Looks good so far, no errors. I just don't have any results yet as I have to run a patch cycle first. I will report back if this has been successful. Thank you so much for posting the reply.
*EDIT - Confirmed this does indeed work and is exactly what I was looking for. Thank you Tim! - jcaine 4 years ago
How about the normal default report?
select NAME, IP, LAST_SYNC, UPTIME
from MACHINE
join KBSYS.KONDUCTOR_TASK KT on KT.KUID = MACHINE.KUID and KT.TYPE like 'patch%' and KT.PHASE ='reboot pending'
order by MACHINE.NAME
Im looking on the v10 Admin Guide at the Database Tables Name page and cant make sense on how to update it. I would like to understand it. For example I find "machine" but how do you know "NAME, IP, LAST_SYNC, UPTIME" is under that? Also where / what is "KBSYS.KONDUCTOR_TASK" "KT" "KT.KUID" etc? I search the guide and these keywords are not in the document.
Comments:
-
This is what I got with the wizard
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.IP, GROUP_CONCAT(DISTINCT KONDUCTOR_TASK.PHASE SEPARATOR '\n') AS KONDUCTOR_TASK_PHASE_GROUPED FROM MACHINE LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID = MACHINE.KUID
AND substring(SCHEMA(),4) = KBSYS.KUID_ORGANIZATION.ORGANIZATION_ID left join KBSYS.KONDUCTOR_TASK on KONDUCTOR_TASK.KUID=MACHINE.KUID WHERE ((KONDUCTOR_TASK.PHASE = 'reboot pending')) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME - lama01 4 years ago
