List updates, hotfixes, and Service Packs with Simple Commands

A friend on the Brazilian SQL Server mailing list recently asked what the best way was to list all the updates applied to servers, complete with dates and descriptions. (including Service Packs, Hot Fix and all updates applied by MSI). At the time, I knew about the get-hotfix cmdlet, although this cmdlet has a restriction: updates made ??via MSI are not listed

This cmdlet uses the Win32_QuickFixEngineering WMI class, which represents small system-wide updates of the operating system. Starting with Windows Vista, this class returns only the updates supplied by Component Based Servicing (CBS). It does not include updates that are supplied by Microsoft Windows Installer (MSI) or the Windows update site.
Technet – Get-HotFix

Then my good friend Demétrio (blog), who is also PowerShell fan, showed me the IUpdateHistoryEntry interface, which I immediately started playing with (naturally):

First Script (Com Object):

This script outputs a Com Object, with TypeName: System.__ComObject#{c2bfb780-4539-4132-ab8c-0a8772013ab6}

Second Script (PsObject):

This Script output a PsObject – TypeName: System.Management.Automation.PSCustomObject

These little demos are nice to work with, but you may be (quite rightly) wondering what the real difference between them is.

Well, in the first script (which is also the faster of the two), we work with what we call a “living object”; In other words, all properties, methods, and type information about that object is preserved. When you’re working with these living objects, you can therefore interact with the full aspect of the object. This means that you can, for example, use get-process to return a live object, and then kill the process you’ve tracked down. If you were using a script that didn’t return a live object, this would not be possible.

(Big Thanks to my friend Robert Robelo (Twitter | Blog) for his clarification on “Live Objects”.)

In the second script, the output object is actively formatted, which is slower, but takes up less RAM, and only specific properties are preserved. This method is particularly interesting if you want to merge the values ??from different objects into a single output.

Which method is the best? The one that fits what your needs, of course!

Putting it into Action

So, in this instance, if I want to see the SP, hotfixes, and all the other updates applied to a server, here’s what I could do:


Scaling out

As usual, we want to be able to manage multiple servers, not just one. So, we can either specify servers in-line.

.. or maybe store the server names in a flat .txt file, and call that into our script

But what about the output? How about if we want to save that into a txt file, as well? Easy (if not necessarily convenient):

Sending the Output to a SQL Server table

Rather than storing this data in flat text files, where is difficult to work with, why don’t we pipe all this information directly into a SQL Server table? We will use two of Chad Miller’s excellent functions: Write-DataTable and Out-DataTable (as described in my previous blog post).

To save the output into SQL Server without having to go line by line, we first have to format the output of the object to a data table, and then import into SQL Server (if you look at the code of the Write-DataTable cmdlet, you’ll see it’s using sqlbulkcopy). However, first we have to create the table in SQL Server to receive that data:

Now we can run the code to get the update data and store it in our database:

For One Server

As always, let’s start small:

Scaling Out with a Flat File:

That’s it.

Now, if you still think that DBAs don’t need to know about PowerShell, my answer is what you see above;. with just three sets of command, I can list all the updates applied to 1000 servers, and save all that data directly in SQL Server table.

You can download the code here – enjoy!

Brief plug

If you want a complete audit with a graphical interface, I do suggest you download POSHPAIG – an excellent codeplex project from our friend Boe Prox (blog | twitter)

Project Description

PoshPAIG allows you to easily audit and install patches on your servers in the network by providing a graphical interface to select which servers to audit/install and to generate reports for the systems.
The utility works in any environment, but the optimal environment is a where you have a local WSUS server and your systems have Windows Update settings configured to “Download updates and do not install” either through Group Policy or local policy.