/build/static/layout/Breadcrumb_cap_w.png

Need help updating custom sql report from version 9 to version10 -> Patch listing completion rate by machine - concise

SELECT M.NAME AS MACHINE_NAME,

OS_NAME AS WINDOWS_VERSION,

SUM(MS.STATUS='PATCHED') AS PATCHED,

SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,

ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')

  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED

FROM PATCHLINK_MACHINE_STATUS MS

JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)

JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)

JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)

JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)

JOIN LABEL L ON (ML.LABEL_ID = L.ID)

WHERE PP.IMPACTID = 'Critical'

AND PPS.STATUS = 0

AND PP.IS_SUPERCEDED = 0

AND L.NAME rlike 'DL_Dell_Devices_All|DL_Macintosh_Devices_All'

GROUP BY M.NAME

ORDER BY PERCENT_PATCHED, M.NAME


Error Running Report

mysqli error: [1142: SELECT command denied to user 'R1'@'localhost' for table 'PATCHLINK_PATCH'] in EXECUTE("SELECT M.NAME AS MACHINE_NAME, OS_NAME AS WINDOWS_VERSION, SUM(MS.STATUS='PATCHED') AS PATCHED, SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED, ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED') +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED FROM PATCHLINK_MACHINE_STATUS MS JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID) JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID) JOIN MACHINE M ON (M.ID = MS.MACHINE_ID) JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) JOIN LABEL L ON (ML.LABEL_ID = L.ID) WHERE PP.IMPACTID = 'Critical' AND PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0 AND L.NAME rlike 'DL_Dell_Devices_All|DL_Macintosh_Devices_All' GROUP BY M.NAME ORDER BY PERCENT_PATCHED, M.NAME")



2 Comments   [ + ] Show comments
  • I have the same problem, I could make my changes myself, but there is no documentation that completely lists all the changes to SQL database from 9 to 10, why can't we have this? - htharp@omig.com 4 years ago
  • I agree, absolutely no clear guidance or support on the change. I found the sql report on ITNinja, from jverbosk, K1000 Reports - Patching Reports for Completion by Patch, Machine & Vendor using Labels. The user was let go a few years back, I wish they were here now, they would help. KACE SMA version 10 is a bust for me. Reporting is messed up, labels are messed up due to chassis type changes, patch catalog is messed up- showing patches from 5 years ago for whatever reason. Seriously considering moving to SCCM, and that saying something. - jgunter 4 years ago
    • SQL reports from versions 9.1 or older are not going to work with version 10 , the database changed a lot in this version, because of the new patching engine, those reports will not work.

      You could attach to the KACE SMA Database using MySQL Workbench or HeidiSQL tools, and explore the database to perform the changes you need.

      Database Schema Changes for version 10:
      https://support.quest.com/es-es/kb/309180/kace-sma-10-0-database-schema-changes

      Here's a Guide with tips to upgrade old reports to version 10:
      https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0

      And here's a guide that will tell you how to access your KACE SMA Database and explore it:
      https://support.quest.com/kb/114992/can-i-access-the-k1000-appliance-database-using-a-third-party-reporting-tool-

      With those three links, a SQL report, SQL label or manual label, could be updated. - Channeler 4 years ago

Answers (1)

Posted by: Channeler 4 years ago
Red Belt
0

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 for 50,000 ft answer but doesn't help with the above issue of correcting the report. - jgunter 4 years ago
    • you should check this post from another ITNinja member, asking about this same exact report

      After accessing the KACE SMA Database and modding the SQL sentence a bit, it seems the report is working for them on version 10:

      https://www.itninja.com/question/k1000-custom-report-broken-in-version-10 - Channeler 4 years ago
      • ^This helped. Grazie - jgunter 4 years ago
  • I was able to massage a report to get the same functionality, no guarantees, but should show the differences.

    SELECT
    M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    M.Notes as Notes,
    M.LAST_SYNC as Last_Inventory,
    M.LAST_REBOOT as Last_Re_Boot,
    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =
    'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS
    PERCENT_PATCHED
    FROM
    PATCH_MACHINE_STATUS MS
    JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
    JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
    JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
    JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)
    WHERE

    PPS.STATUS = 0
    AND PPS.IS_SUPERCEDED = 0
    GROUP BY MS.MACHINE_ID
    ORDER BY PERCENT_PATCHED , M.NAME - htharp@omig.com 4 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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