/build/static/layout/Breadcrumb_cap_w.png
09/23/2019 623 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

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

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
Senior White 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.