Simple solution to collect applied Windows Updates List from Computers and Store them on any SQL Server Database Table

  • Article

You can use the following simple solution to collect applied windows updates list from computers. First create the table on a suitable database on a suitable SQL server. Then run the powershell code on the destination computer manually or scheduled to store the applied Windows updates list to a SQL server database table. Then query the results. Each time the code executes it adds the updates to the SQL server database table. So table will grow eventually when yor run the powershell code from multiple clients or servers that can be scheduled regularly. You can maintain or clean the table as you wish according to database maintenance plans. Additionally, you can filter discovered updates list in the powershell script by modifying where conditions. In the example, as you will notice I filter out Malicious Software Removal Tool and Definition Update.

NOTE: In some cases, you may find the other choices more convenient than using Microsoft.Update.Session class, for traversing applied hotfixes, such as using Win32_QuickFixEngineering WMI class or getting all KB prefixed child items under HKLM\Software\Microsoft\Windows NT\CurrentVersion\Hotfix registry key or using Get-Hotfix PowerShell cmdlet. Please prefer which one works for you and adapt the following solution according your situation. This is just an example solution.

First of all, please always test new changes in your test environment to make sure that it works as expected before rolling out to pilot or production environments.

TSQL code to create table. Please change the database name accordingly. USE [MYDBXXX]

GO

CREATE TABLE [dbo].[UpdateInventory](
ID_column INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[ServerName] [varchar](50) NULL,
[UpdateDate] [varchar](MAX) NULL,
[UpdateTitle] [varchar](MAX) NULL,
[UpdateKB] [varchar](50) NULL,
[UpdateDescription] [varchar](MAX) NULL,
[UpdateClientApplicationID] [varchar](MAX) NULL,
DateInserted DATETIME NOT NULL DEFAULT (GETDATE())
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NonClusteredIndex-Server-UpdateKB] ON [dbo].[UpdateInventory]
(
[ServerName] ASC,
[UpdateKB] ASC
)WITH (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)

GO

null

TSQL code to query table SELECT [ID_column] ,[ServerName] ,[UpdateDate] ,[UpdateTitle] ,[UpdateKB] ,[UpdateDescription] ,[UpdateClientApplicationID] ,[DateInserted] FROM [ReportServer].[dbo].[UpdateInventory] null
<br

Simple PowerShell code snippet that will run on computers as scheduled or manually. Please don't forget to change the connection string parameters below accordingly. This powerhsell code may require certain privileges, please consider the minimum rights required. function Get-MyUpdates {

[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)
}
}
}

cls

$conn = new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server=SERVERXXX;Database=MYDBXXX;Integrated Security=True;Connect Timeout=0"
$conn.ConnectionString = $ConnectionString
$conn.Open()

$mypatches = Get-MyUpdates $env:computername | where {$_.UpdateTitle -notlike 'Definition Update*' -and $_.UpdateTitle -notlike ''`
-and $_.UpdateTitle -notlike '*driver*' -and $_.UpdateTitle -notlike '*Windows Malicious Software Removal Tool*' }

$regex = "(?<=\().+?(?=\))"

Foreach ($myupdates in $mypatches)
{
$myupdates.updatetitle -match $regex

$commandText = "INSERT INTO UpdateInventory (ServerName, UpdateTitle, UpdateDescription, UpdateClientApplicationID, UpdateDate, UpdateKB) VALUES`
('" + $myupdates.ServerName + "','" + $myupdates.UpdateTitle + "','" + $myupdates.UpdateDescription.Replace("'","")`
+ "','" + $myupdates.UpdateClientApplicationID + "','" + $myupdates.UpdateDate + "','" + $matches[0] + "')"
$commandText
$command = $conn.CreateCommand()
$command.CommandText = $commandText
$command.ExecuteNonQuery()
}

$conn.Close()

null