/build/static/layout/Breadcrumb_cap_w.png

Help with Patch Reports

I am new to Kace and not a SQL person so I would greatly appreciate guidance on creating a couple of reports dealing with patching.

I want to be able to see Vendor patches with patch name by Vendor rating that are not installed on a device.

Any help would be greatly appreciated!



0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 9 years ago
Red Belt
0
Welcome to KACE! If you want to learn SQL (which I highly recommend doing) then you should make sure that the reporting user is enabled on your K1000 and setup MySQL Workbench. This will allow you to look at the database tables and will help making reports easier.

The list of patches is stored in the KBSYS.PATCHLINK_PATCH table. If you look at this table it will give you a list of all of the patches that are in the database.

To know where a patch has been installed you need to look at ORG1.PATCHLINK_MACHINE_STATUS. This table shows a record for each patch and computer where KACE has tried to deploy the patch.

The report you want would combine information from these tables like this:
SELECT TITLE, VENDOR, IMPACTID, COUNT(MACHINE_ID) AS INSTALLED
FROM KBSYS.PATCHLINK_PATCH
LEFT JOIN ORG1.PATCHLINK_MACHINE_STATUS on PATCHLINK_MACHINE_STATUS.PATCHUID = PATCHLINK_PATCH.UID
GROUP BY UID
HAVING INSTALLED = 0 
ORDER BY IMPACTID, VENDOR, TITLE
This selects the Title, Vendor and Impact ID (which is actually text) and the number of machines where the patch is installed. It only shows results with a 0 in the INSTALLED column. I took your "not installed on a device" to mean not installed on any devices. If you meant not installed on a particular device that would be a different query.


Comments:
  • Here's an updated query. It includes information from ORG1.PATCHLINK_PATCH_STATUS so that only active and non-superceded patches are included in the report.

    SELECT TITLE, VENDOR, IMPACTID, PATCHLINK_PATCH_STATUS.STATUS, COUNT(MACHINE_ID) AS INSTALLED
    FROM KBSYS.PATCHLINK_PATCH
    LEFT JOIN ORG1.PATCHLINK_MACHINE_STATUS on PATCHLINK_MACHINE_STATUS.PATCHUID = PATCHLINK_PATCH.UID
    JOIN ORG1.PATCHLINK_PATCH_STATUS on ORG1.PATCHLINK_PATCH_STATUS.PATCHUID = PATCHLINK_PATCH.UID
    WHERE ORG1.PATCHLINK_PATCH_STATUS.STATUS = 0
    AND PATCHLINK_PATCH_STATUS.IS_SUPERCEDED = 0
    GROUP BY UID
    HAVING INSTALLED = 0
    ORDER BY IMPACTID, VENDOR, TITLE - chucksteel 9 years ago
    • Thanks I will give this a try. I will also enable the reporting user and setup MYSQL workbench - Johnnymac 9 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