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}
function Get-Updates {
[CmdletBinding()]
Param (
[Parameter(position=0,Mandatory = $true,ValueFromPipeline =
$true,ValueFromPipelinebyPropertyName=$true)][String]
$ComputerName
)
begin
{
[System.Reflection.Assembly]::LoadWithPartialName
('Microsoft.Update.Session') | Out-Null
}
process {
$session = [activator]::CreateInstance([type]::
GetTypeFromProgID("Microsoft.Update.Session",
$ComputerName))
$us = $session.CreateUpdateSearcher()
$qtd = $us.GetTotalHistoryCount()
$hot = $us.QueryHistory(1, $qtd)
$hot
}
}
Second Script (PsObject):
This Script output a PsObject - TypeName: System.Management.Automation.PSCustomObject
function Get-Updates {
[CmdletBinding()]
Param (
[Parameter(position=0,Mandatory = $true,ValueFromPipeline =
$true,ValueFromPipelinebyPropertyName=$true)][String]
$ComputerName
)
begin
{
[System.Reflection.Assembly]::LoadWithPartialName
('Microsoft.Update.Session') | Out-Null
}
process {
$session = [activator]::CreateInstance([type]::
GetTypeFromProgID("Microsoft.Update.Session",
$ComputerName))
$us = $session.CreateUpdateSearcher()
$qtd = $us.GetTotalHistoryCount()
$hot = $us.QueryHistory(1, $qtd)
foreach ($Upd in $hot) {
$Property = @{
'ServerName'=$computername;
'UpdateDate'=$Upd.date ;
'UpdateTitle'=$Upd.title;
'UpdateDescription'=$Upd.Description;
'UpdateClientApplicationID'=
$Upd.ClientApplicationID
}
Write-Output (New-Object -Type PSObject -Prop $Property)
}
}
}
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:
Get-Updates Server1
Scaling out
As usual, we want to be able to manage multiple servers, not just one. So, we can either specify servers in-line.
Server1,Server2,Server3 | get-updates
.. or maybe store the server names in a flat .txt file, and call that into our script
get-content c:\temp\servers.txt | get-updates
But what about the output? How about if we want to save that into a txt file, as well? Easy (if not necessarily convenient):
get-content c:\temp\servers.txt | get-updates | out-file
c:\servers\audit.txt
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:
CREATE TABLE [dbo].[UpdateInventory](
[ServerName] [varchar](50) NULL,
[UpdateDate] [varchar](MAX) NULL,
[UpdateTitle] [varchar](MAX) NULL,
[UpdateDescription] [varchar](MAX) NULL,
[UpdateClientApplicationID] [varchar](MAX) NULL
) ON [PRIMARY]
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:
$variable = Get-Updates -ComputerName Server1 | select ServerName,
UpdateDate,UpdateTitle,UpdateDescription,UpdateClientApplicationID
$valuedatatable = Out-DataTable -InputObject $variable Write-DataTable -ServerInstance ServerRepository -Database Dbrepository -TableName UpdateInventory -Data$valuedatatable
$variable = @('Server1','Server2','Server3' | get-updates |select
ServerName,UpdateDate,UpdateTitle,UpdateDescription,
UpdateClientApplicationID )
$valuedatatable = Out-DataTable -InputObject $variable Write-DataTable -ServerInstance ServerRepository -Database Dbrepository -TableName UpdateInventory -Data$valuedatatable
Scaling Out with a Flat File:
$variable = @(Get-Content c:\temp\servers.txt | get-updates |select
ServerName,UpdateDate,UpdateTitle,UpdateDescription,
UpdateClientApplicationID )
$valuedatatable = Out-DataTable -InputObject $variable
Write-DataTable -ServerInstance ServerRepository -Database Dbrepository
-TableName UpdateInventory -Data $valuedatatable
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.
#PowerShellLifeStyle