/build/static/layout/Breadcrumb_cap_w.png

Need help converting K1000 (Systems Management Appliance) patch report so it will work on version 10.x

Since the 10.x update, I have a patch report that errors when trying to run. I have looked over the database schema changes but I can't seem to fix the SQL on the report to get it to work. I get the following error: 

mysqli error: [1142: SELECT command denied to user 'R1'@'localhost' for table 'PATCHLINK_PATCH'] in EXECUTE("SELECT DISTINCT M.NAME, PM.STATUS_DT AS 'DETECT DATE', COUNT(P.TITLE) AS NUM_OF_PATCHES_NEEDED, IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0) AS NUM_PATCHES_INSTALLED, (COUNT(P.TITLE) - IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0)) AS NUM_OF_PATCHES_MISSING, ROUND(IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0) / COUNT(P.TITLE) * 100,2) AS PERCENT_COMPLETE FROM MACHINE M LEFT JOIN PATCHLINK_MACHINE_STATUS PM ON M.ID=PM.MACHINE_ID LEFT JOIN KBSYS.PATCHLINK_PATCH P ON P.UID=PM.PATCHUID left join (SELECT DISTINCT M.ID, COUNT(P.TITLE) AS NUM_OF_PATCHES_INSTALLED FROM MACHINE M LEFT JOIN PATCHLINK_MACHINE_STATUS PM ON M.ID=PM.MACHINE_ID LEFT JOIN KBSYS.PATCHLINK_PATCH P ON P.UID=PM.PATCHUID WHERE P.IMPACTID = 'Critical' AND P.IS_APP = '0' AND P.IS_SUPERCEDED != '1' AND PM.STATUS = 'PATCHED' AND OS_NAME NOT LIKE '%Server%' GROUP BY M.ID ORDER BY NUM_OF_PATCHES_INSTALLED) as PATCHINSTALLED on PATCHINSTALLED.ID = M.ID WHERE P.IMPACTID = 'Critical' AND P.IS_APP = '0' AND P.IS_SUPERCEDED != '1' AND OS_NAME NOT LIKE '%Server%' GROUP BY M.ID ORDER BY NUM_OF_PATCHES_MISSING ASC, M.NAME ASC")

Full SQL query: 

SELECT DISTINCT M.NAME, PM.STATUS_DT AS 'DETECT DATE',
COUNT(P.TITLE) AS NUM_OF_PATCHES_NEEDED, IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0) AS NUM_PATCHES_INSTALLED,
(COUNT(P.TITLE) - IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0)) AS NUM_OF_PATCHES_MISSING,
ROUND(IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0) / COUNT(P.TITLE) * 100,2) AS PERCENT_COMPLETE
FROM MACHINE M
LEFT JOIN PATCHLINK_MACHINE_STATUS PM ON M.ID=PM.MACHINE_ID
LEFT JOIN KBSYS.PATCHLINK_PATCH P ON P.UID=PM.PATCHUID

left join
(SELECT DISTINCT M.ID, COUNT(P.TITLE) AS NUM_OF_PATCHES_INSTALLED
FROM MACHINE M
LEFT JOIN PATCHLINK_MACHINE_STATUS PM ON M.ID=PM.MACHINE_ID
LEFT JOIN KBSYS.PATCHLINK_PATCH P ON P.UID=PM.PATCHUID
WHERE
P.IS_APP = '0' AND
P.IS_SUPERCEDED != '1' AND
PM.STATUS = 'PATCHED' AND
OS_NAME NOT LIKE '%Server%'
GROUP BY M.ID
ORDER BY NUM_OF_PATCHES_INSTALLED) as PATCHINSTALLED on PATCHINSTALLED.ID = M.ID

WHERE
P.IS_APP = '0' AND
P.IS_SUPERCEDED != '1' AND
OS_NAME NOT LIKE '%Server%'
GROUP BY M.ID
ORDER BY PERCENT_COMPLETE ASC, M.NAME ASC


Can anyone out there please help me convert this to work with version 10.x of KACE?



0 Comments   [ + ] Show comments

Answers (2)

Posted by: Nico_K 4 years ago
Red Belt
0

you can review the changes here:
https://support.quest.com/kb/309180/

Your query is going against the old PATCHLINK tables which are replaced.



Comments:
  • Right. I guess I'm just having trouble determining what they were replaced with. - mdilapi 4 years ago
Posted by: KevinG 4 years ago
Red Belt
0

This should help you. I just changed the names where needed. I did not validate the actual query.

SELECT DISTINCT M.NAME, PM.STATUS_DT AS 'DETECT DATE',
COUNT(P.TITLE) AS NUM_OF_PATCHES_NEEDED, IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0) AS NUM_PATCHES_INSTALLED,
(COUNT(P.TITLE) - IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0)) AS NUM_OF_PATCHES_MISSING,
ROUND(IFNULL(PATCHINSTALLED.NUM_OF_PATCHES_INSTALLED,0) / COUNT(P.TITLE) * 100,2) AS PERCENT_COMPLETE
FROM MACHINE M
LEFT JOIN PATCH_MACHINE_STATUS PM ON M.ID=PM.MACHINE_ID
LEFT JOIN KBSYS.PATCH P ON P.ID = PM.PATCH_ID

left join
(SELECT DISTINCT M.ID, COUNT(P.TITLE) AS NUM_OF_PATCHES_INSTALLED
FROM MACHINE M
LEFT JOIN PATCH_MACHINE_STATUS PM ON M.ID=PM.MACHINE_ID
LEFT JOIN KBSYS.PATCH P ON P.ID = PM.PATCH_ID
WHERE
P.IS_SUPERCEDED != '1' AND
PM.DETECT_STATUS = 'PATCHED' AND
OS_NAME NOT LIKE '%Server%'
GROUP BY M.ID
ORDER BY NUM_OF_PATCHES_INSTALLED) as PATCHINSTALLED on PATCHINSTALLED.ID = M.ID

WHERE
P.IS_SUPERCEDED != '1' AND
OS_NAME NOT LIKE '%Server%'
GROUP BY M.ID
ORDER BY PERCENT_COMPLETE ASC, M.NAME ASC


Comments:
  • KevinG,

    Thanks so much for this. It looks like I forgot a line in the Where clause. I tried adding it back when I went to test but it didn't like this line:

    P.IMPACTID = 'Critical' AND

    Would you know what to replace that with? Whole Where clause:

    WHERE
    P.IMPACTID = 'Critical' AND
    P.IS_APP = '0' AND
    P.IS_SUPERCEDED != '1' AND
    OS_NAME NOT LIKE '%Server%'
    GROUP BY M.ID
    ORDER BY NUM_OF_PATCHES_MISSING ASC, M.NAME ASC - mdilapi 4 years ago
  • You may want to review the following KB.
    https://support.quest.com/kb/309572/updating-custom-sql-queries-for-10-0

    P.IMPACTID and P.IS_APPiare no longer a valid column name.
    Are are looking for P.SEVERITY = ''Critical' ? - KevinG 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