/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


K1000 Custom Report broken in Version 10 - MySql Table KBSYS.PATCHLINK_PATCH missing in Version 10

09/23/2019 1420 views

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?

0 Comments   [ + ] Show comments

Comments



Community Chosen Answer

2

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

Answered 09/23/2019 by: Channeler
Red Belt

  • Thanks, that definitely gives a good starting point to begin rebuilding all my different reports.

All Answers

1

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

Answered 09/24/2019 by: RyanTech
Yellow Belt

  • Ryan - Thanks for the query refresh and saving me some time digging into it. Much appreciated.
  • 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
    • 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
      • 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!
0

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. 


Answered 10/21/2019 by: lama01
Blue Belt

  • 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
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ