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

Answer Summary:
The link posted by jverbosk led to some SQL reports that I modified for my own use.
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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

Answered 07/27/2012 by: jverbosk
Red Belt

  • I have added the IMPACTID looking for Critical and that has helped. I will work on the active status part next.
Please log in to comment

Answers

1

Are you wanting which patches didn't get installed by Kace, or all MS patches in general?

Answered 07/27/2012 by: dugullett
Red Belt

  • 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.
    • We are looking at doing the same thing.
  • I am also looking on doing the same jeff
Please log in to comment
1

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

Answered 07/27/2012 by: SMal.tmcc
Red Belt

  • 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
  • found this link which futhers that use
    http://msinfluentials.com/blogs/jesper/archive/2007/12/28/remotely-listing-all-installed-updates.aspx
Please log in to comment
1

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.

Answered 08/01/2012 by: jeffwbrown
Senior Yellow Belt

  • Is the update client set to show for windows only update or all microsoft updates?
  • 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
  • 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.
  • 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
Please log in to comment
Answer this question or Comment on this question for clarity

Share