Report Showing What Patches Are NOT Installed
I'm working on creating a report that shows a list of computers with what Microsoft patches are NOT installed. I copied the premade report that shows what patches are applied and modified it as shown:
Select MACHINE.NAME AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME, P.IDENTIFIER as KB_ARTICLE from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P
where
MACHINE.NAME = S.MACHINE_ID and
S.PATCHUID = P.UID and
S.STATUS = 'NOTPATCHED' and
P.VENDOR = 'Microsoft Corp.' and
P.TITLE LIKE '%Windows 7%'
group by P.TITLE ASC
For my computer, this brings up 57 patches. However, when I manually run a Windows update check through the built-in application in Windows, it only brings back 17 results. I'm basically trying to match the results that come up when you manually check a computer for Windows updates using the Windows applications. Any suggestions on how to do this? Thanks.
Jeff
Answers (4)
The link posted by jverbosk led to some SQL reports that I modified for my own use.
Check out the reports at the end of this blog:
http://www.itninja.com/blog/view/k1000-patching-setup-tips-things-i-have-learned-ldap-smart-labels-sql-reports
There are some things you could add to your report, such the severity rating (Impact - AND PP.IMPACTID = ('Critical')) and whether they are active or not (AND PPS.STATUS in (0)) that could help you get a bit closer to the numbers you are seeing with the MS patch scan.
John
Comments:
-
I have added the IMPACTID looking for Critical and that has helped. I will work on the active status part next. - jeffwbrown 11 years ago
Are you wanting which patches didn't get installed by Kace, or all MS patches in general?
Comments:
-
All MS patches in general. Let me go into what we are trying to do.
Once a month, after Patch Tuesday, we go to each server and check for Windows updates. We then document each patch it detects that needs to be installed via Windows Updates, then we patch the server. This way we have a list of which patches were installed each month. We are hoping to automate the documenting part by running a report in KACE that will just show what patches are needed, then we can update as needed. - jeffwbrown 11 years ago-
We are looking at doing the same thing. - Kapp230 11 years ago
-
I am also looking on doing the same jeff - brighstarcuit 11 years ago
You can also run this command to create a file with all the patches and the date and time installed:
WMIC qfe list >> c:\server1.txt
Comments:
-
you could run that as a scheduled task/k1000 push and output the files to a central share on one server so you could view all your servers files in one location - SMal.tmcc 11 years ago
-
found this link which futhers that use
http://msinfluentials.com/blogs/jesper/archive/2007/12/28/remotely-listing-all-installed-updates.aspx - SMal.tmcc 11 years ago
Just a follow up.
Here's what I ended up with:
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME, DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
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
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
AND PP.VENDOR='Microsoft Corp.'
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE
Break on Columns: COMPUTER_NAME
This has gotten me pretty close. The only odd thing is it shows some Office updates, but when I manually run Windows updates, the Office updates do not show up. It may just be a malfunctioning Windows update client. This is a modified SQL report from the blog posted by jverbosk.
Comments:
-
Is the update client set to show for windows only update or all microsoft updates? - SMal.tmcc 11 years ago
-
The K1000 splits patches into two types - OS & Application. The reports I posted will show patches from both. If you want to exclude the Application patches, add this to your WHERE statement (which is oddly missing from your SELECT query, but should start before the AND MS.STATUS line):
AND KBSYS.PATCHLINK_PATCH.IS_APP = '0'
Just be aware that *quite* a few "OS-like" patches may end up here, such as security updates for .Net, SQL, Untrusted Certificates, Windows Media Player, Kerberos, Visual C++ Redistributables, Windows Kernel, etc. It all depends on how Lumension classifies them. This is personally why I also patch applications with the K1000 (aside from wanting to keep the 3rd party apps patched).
John - jverbosk 11 years ago -
Office updates are seen as application updates, did not think about that. I added your line where you said but got an eror, but changed it up to:
SELECT PP.TITLE AS PATCH_NAME,
M.NAME AS COMPUTER_NAME, DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
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
AND PP.IS_APP = '0'
AND MS.STATUS = 'NOTPATCHED'
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
AND PP.VENDOR='Microsoft Corp.'
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE
And that worked fine. My test laptop that doesn't show any updates using the Windows Update client also does not show up in my report. I'll continue testing it on more computers. Thanks again. - jeffwbrown 11 years ago -
Yes, the alias (PP) would need to be used, which your report takes care of - glad that's working for you!
Also, just so your syntax is correct, I would change this line:
AND PP.IS_APP = '0'
to this:
WHERE PP.IS_APP = '0'
I honestly thought the WHERE was necessary, but I guess MySQL is flexible enough to handle using the first AND instead.
John - jverbosk 11 years ago