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)
Please log in to answer
Posted by:
chucksteel
8 years ago
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 8 years ago-
Thanks I will give this a try. I will also enable the reporting user and setup MYSQL workbench - Johnnymac 8 years ago
