With a command line .. I can list the updates, hotfixes, SP applied to my servers


Today in our Brazilian SQL Server list , a friend asked what the best way to list all the updates applied to servers, with date and description of them. This includes, Service Packs, Hot Fix and also all updates applied by MSI.

My good friend Demétrio, who is a PowerSheller too, showed the IUpdateHistoryEntry Interface. Then was just playing with it:

First Script :

This script output a Com Object

TypeName: System.__ComObject#{c2bfb780-4539-4132-ab8c-0a8772013ab6}

   1: function Get-Updates { 

   2:  

   3: [CmdletBinding()]

   4:  

   5:     Param (

   6:         [Parameter(position=0,Mandatory = $true,ValueFromPipeline = $true,ValueFromPipelinebyPropertyName=$true)][String] $ComputerName

   7:  

   8:     )

   9:     begin 

  10:     {

  11:         [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.Update.Session') | Out-Null

  12:     }

  13:     process {

  14:     

  15:         $session =  [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$ComputerName))

  16:         $us = $session.CreateUpdateSearcher()

  17:         $qtd = $us.GetTotalHistoryCount()

  18:         $hot = $us.QueryHistory(1, $qtd)

  19:         $hot 

  20:                         

  21:             

  22:     }

  23:  

  24: }

Second Script :

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

   1:  

   2:  

   3: function Get-Updates { 

   4:  

   5: [CmdletBinding()]

   6:  

   7:     Param (

   8:         [Parameter(position=0,Mandatory = $true,ValueFromPipeline = $true,ValueFromPipelinebyPropertyName=$true)][String] $ComputerName

   9:  

  10:     )

  11:     begin 

  12:     {

  13:         [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.Update.Session') | Out-Null

  14:     }

  15:     process {

  16:     

  17:         $session =  [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$ComputerName))

  18:         $us = $session.CreateUpdateSearcher()

  19:         $qtd = $us.GetTotalHistoryCount()

  20:         $hot = $us.QueryHistory(1, $qtd)

  21:     

  22:         foreach ($Upd in $hot) {

  23:             $Property = @{     

  24:                             'ServerName'=$computername; 

  25:                             'UpdateDate'=$Upd.date ;

  26:                             'UpdateTitle'=$Upd.title;

  27:                             'UpdateDescription'=$Upd.Description;

  28:                             'UpdateClientApplicationID'=$Upd.ClientApplicationID

  29:                         }

  30:             Write-Output (New-Object -Type PSObject -Prop $Property)    

  31:             

  32:         }

  33:  

  34:  

  35:     }

  36: }

  37:  

  38:  

  39:  

Yeah but what is the difference of each one:
In the first script (faster), we work with that what we call a living object, In other words, all properties, methods, type information is preserved. Ex If I use get-process, can I use it methods to kill a process.
In the second script, the output object is formatted. It is slower but takes up less RAM and only the custom properties are preserved. This method is interesting if you want to merge values ​​of different objects in a single output.
What is the best? One that fits what your need. Smile

(Big Thanks to my friend Robert Robelo (Twitter | Blog) , another guy from Jedi Council, that point me what is a “Live Object” )

So if I want to see the SP, hot fix .., and all the updates from a server :

   1: Get-Updates Server1

 

image_thumb2

Lets Scaling out ?

   1: Server1,Server2,Server3 | get-updates

or maybe store the servers in a flat file :

image_thumb4

   1: 1: get-content c:\temp\servers.txt | get-updates

and to save the output into a txt file :

   1: get-content c:\temp\servers.txt | get-updates | out-file c:\servers\audit.txt

 

Now we will send you to a SQL Server table

We will use two functions from my friend and mentor Chad Miller, a Write-DataTable e a Out-DataTable
(In the end you will find the link to download)

To save in SQL Server without having to go line by line, first we have to format the output of the object to a data table and then import (if you see the code of the Write-DataTable  is  using Bulk Insert)

First let’s create the table in SQL Server;

   1: CREATE TABLE [dbo].[UpdateInventory](

   2:     [ServerName] [varchar](50) NULL,

   3:     [UpdateDate] [varchar](max) NULL,

   4:     [UpdateTitle] [varchar](max) NULL,

   5:     [UpdateDescription] [varchar](max) NULL,

   6:     [UpdateClientApplicationID] [varchar](max) NULL

   7: ) ON [PRIMARY]

   8:  

   9: GO

 

and then go to the final code

One server;

   1:  

   2: $variable = Get-Updates -ComputerName Server1 | select  ServerName,UpdateDate,UpdateTitle,UpdateDescription,UpdateClientApplicationID

   3: $valuedatatable = Out-DataTable -InputObject $variable 

   4: Write-DataTable -ServerInstance ServerRepository -Database Dbrepository -TableName UpdateInventory -Data $valuedatatable

Scaling out  :

   1: $variable = @('Server1','Server2','Server3' | get-updates |select  ServerName,UpdateDate,UpdateTitle,UpdateDescription,UpdateClientApplicationID  )

   2: $valuedatatable = Out-DataTable -InputObject $variable 

   3: Write-DataTable -ServerInstance ServerRepository -Database Dbrepository -TableName UpdateInventory -Data $valuedatatable

Scaling out by txt :

   1: $variable = @(Get-Content c:\temp\servers.txt | get-updates |select  ServerName,UpdateDate,UpdateTitle,UpdateDescription,UpdateClientApplicationID  )

   2: $valuedatatable = Out-DataTable -InputObject $variable 

   3: Write-DataTable -ServerInstance ServerRepository -Database Dbrepository -TableName UpdateInventory -Data $valuedatatable

That’s it. If you still think that DBA does not need to know PowerShell , my answer is that. With three command lines I can list all the updates of 1000  servers  and save in SQL Server table.

I challenge you to do in fewer lines Smile

download4

#PowershellLifeStyle

 

POWERSHELL ROCKS

could stick around a little longer with you, hello.
It doesn’t really mean that I’m into you, hello.
You’re alright but I’m here, darling, to enjoy the party.
Don’t get too excited ’cause that’s all you get from me, hey.
Yeah, I think you’re cute, but really you should know.
I just came to say hello, hello, hello, hello.

I’m not the kinda girl to get messed up with you, hello.
I’ma let you try to convince me to, hello.
It’s alright I’m getting dizzy just enjoy the party.
It’s OK with me if you don’t have that much to say, hey.
Kinda like this game but there’s something you should know.
I just came to say hello, hello, hey, hey.

I could stick around a little longer with you, hello.
It doesn’t really mean that I’m into you, hello.
You’re alright but I’m here, darling, to enjoy the party.
Don’t get too excited cause thats all you get from me hey.
Yeah I think your cute but really you should know.
I just came to say hello, hello, hello, hello.

Hello

Martin Solveig

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Algo que Esqueci de Categorizar. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s