Patch Report for Installed Patches Within One Month
Hey everyone,
This is in regards to K1000 reporting, version 6.4.
I have been trying to setup a report that shows patches installed to all devices in my organization within 1 month. Here is what I have so far:
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 1 month)
ORDER BY M.NAME, PP.TITLE
It displays the information perfectly! However, it does not show all devices in the organization, only a handful of them. Can anyone tell me what is wrong with this?
Please note I do not take credit for this, I found this script here: http://www.itninja.com/blog/view/kace-patch-report-for-installed-patches-past-week-and-missing-patches-for-label
Thank you!
This is in regards to K1000 reporting, version 6.4.
I have been trying to setup a report that shows patches installed to all devices in my organization within 1 month. Here is what I have so far:
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 1 month)
ORDER BY M.NAME, PP.TITLE
It displays the information perfectly! However, it does not show all devices in the organization, only a handful of them. Can anyone tell me what is wrong with this?
Please note I do not take credit for this, I found this script here: http://www.itninja.com/blog/view/kace-patch-report-for-installed-patches-past-week-and-missing-patches-for-label
Thank you!
2 Comments
[ + ] Show comments
-
I recommend identifying a machine that is not included in the report and then checking the patching status for that machine. Then check the data in the tables to see what is there, in particular check the PATCHLINK_MACHINE_STATUS table. - chucksteel 8 years ago
-
I have 2 machines pulled up, one that shows in the report, the other does not. I cannot find a difference between the two, both are in the same labels, both were detected and patched on the same date, none are missing patches, both are the same OS. This is so strange, there shouldn't be any reason why all patched devices don't show. I'll keep digging around. - CadePeters 8 years ago
-
What dates are listed in the PATCHLINK_MACHINE_STATUS for those machines and that patch? Are you using MySQL WorkBench or a similar tool to look at the tables directly? - chucksteel 8 years ago
-
Thank you for replying Chucksteel. My apologies, SQL coding is outside of my knowledge, I got this code from another ITNinja topic. I noticed a few users had questioned this issue but no answer was made for over a year. I was hoping if I re-posted the code someone could identify some missing code or something that needed to be removed to make this work. My expertise with this only goes as far as what I can see in the k1 GUI. From within the GUI everything is the same, so there must be something behind the scene on these devices that is different. I'll keep digging around and let you know if I find anything. - CadePeters 8 years ago
Answers (0)
Please log in to answer
Be the first to answer this question
