Powershell WSUS export script for reporting

 
I’ve been working on a PowerShell script that reads info from a WSUS (SQL 2005) database and reports relevant information
to a SQL database, so it can be queried / manipulated using SQL queries and put into reports/websites for overview info.
 
It uses the WSUS .NET Microsoft.UpdateServices.Administration NameSpace that can perform pretty much anything that WSUS can do,
and more if you customize it a bit like I do (I started off from a MSDN example script and built from there).
You need to install the WSUS admin tools to have the .NET Asssemblies installed easily, so the script han run remotely from a
management station or server.
 
Also it uses a textfile that contain the KB numbers from the hotfixes I like to get information from, you need to create this in the current directory from where
the script is started.
 
Any comments welcome on this, probably there are is some stuff in there that could be done fancier but it does the job we want 😉
Please note this is a first version, I plan to built further on this, any suggestions welcome !
 
#Initialization
$SqlServer = "corp-sql"
$SqlCatalog = "corp-data"
$HotfixList= ".\hotfixes.txt"
$WindowsUpdateServer= "corp-wsus"
#Required WSUS Assembly – auto installed with WSUS Administration Tools
[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
 
#Get list of Hotfixes
$Hotfixes= @(get-Content $HotfixList )
Function CreatePatchCollection {
 
if (!$wsus) {
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer($WindowsUpdateServer,$False)
}
$computerScope = new-object Microsoft.UpdateServices.Administration.ComputerTargetScope
$computerScope.IncludedInstallationStates = [Microsoft.UpdateServices.Administration.UpdateInstallationStates]::All
$updateScope = new-object Microsoft.UpdateServices.Administration.UpdateScope
$updateScope.IncludedInstallationStates = [Microsoft.UpdateServices.Administration.UpdateInstallationStates]::Installed
$computers = $wsus.GetComputerTargets($computerScope)
$WsusServers=@()
$computers | foreach-object {
$Target=""
$WsusServer = "" | Select-Object ComputerName,IPAddress,FailedCount,InstalledCount,DownloadedCount,InstalledPendingRebootCount,`
LastUpdated,ClientVersion,OSArchitecture,OSDescription,servicepackmajor,Build,LastReportedStatusTime
$WsusServer.ComputerName=$_.FullDomainName
$WSusServer.IpAddress=$_.IPAddress
$WsusServer.ClientVersion=$_.ClientVersion
$WsusServer.OSArchiTecture=$_.OSArchitecture
$WsusServer.OSDescription=$_.OSDescription
$WsusServer.ServicePackMajor=$_.OSinfo.version.servicepackmajor
$WsusServer.Build=$_.OSinfo.version.build
$WsusServer.LastReportedStatusTime=$_.LastReportedStatusTime
 
#List updates installed on the computer.
$updates = $_.GetUpdateInstallationInfoPerUpdate($updateScope)
$updates | foreach-object {
$installedUpdate = $wsus.GetUpdate($_.UpdateId)
ForEach ($hotfix in $Hotfixes) {
If ($Hotfix -eq $installedupdate.KnowledgebaseArticles )
{
$WsusServer | add-member -membertype noteproperty -name $Hotfix -value $True -force
}
}
}
$updateSummary=$_.GetUpdateInstallationSummary($updateScope)
$updateSummary | ForEach-Object {
$WsusServer.FailedCount=$_.FailedCount
$WsusServer.InstalledCount=$_.InstalledCount
$WsusServer.DownloadedCount=$_.DownloadedCount
$WsusServer.InstalledPendingRebootCount=$_.InstalledPendingRebootCount
$WsusServer.LastUpdated=[datetime]::Parse($_.LastUpdated.DateTime)
}
$targetgroups=$_.GetComputerTargetGroups()
$targetgroups | foreach-object {$target += $_.name; $target += "|" }
$WsusServer | add-member -membertype noteproperty -name Targets -value $Target
 
SaveToSQL $WsusServer
}
   
}
#############################################################################################################################
Function SaveToSQL ($ServerInfo){
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlConnection.Open()
$SqlCmd.Connection = $SqlConnection
$DataSet = New-Object System.Data.DataSet
#Format attributes for SQL format
$Server=$ServerInfo.ComputerName ; $Server="’$Server’"
$IpAddress=$ServerInfo.IPAddress ; $IpAddress= "’$IPAddress’"
$ClientVersion=$ServerInfo.ClientVersion ; $ClientVersion="’$ClientVersion’"
$OSArchiTecture=$ServerInfo.OSArchitecture ; $OSArchiTecture="’$OSArchiTecture’"
$OSDescription=$ServerInfo.OSDescription ; $OSDescription="’$OSDescription’"
$ServicePackMajor=$ServerInfo.ServicepackMajor ; $ServicePackMajor="’$ServicePackMajor’"
$Build=$ServerInfo.Build ; $Build="’$Build’"
$LastUpdated=$ServerInfo.LastUpdated ; $LastUpdated="’$LastUpdated’"
$TargetGroups=$ServerInfo.Targets ; $TargetGroups="’$TargetGroups’"
$LastReportedStatusTime=$ServerInfo.LastReportedStatusTime ; $LastReportedStatusTime="’$LastReportedStatusTime’"
 
#Query ComputerName to see if this is a new record
$SqlQuery = "Select ComputerName From T_Pats_CrossTab2 WHERE ComputerName=$Server"
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
$ExistingRecord=$SqlCmd.ExecuteScalar()
#If this is a non existing record do an Insert Else do An Update
If ($ExistingRecord) {
$SqlQuery = "UPDATE PatchTable SET IpAddress = $IPAddress,ClientVersion=$ClientVersion,`
OSArchitecture=$OSArchitecture,OSDescription=$OSDescription,ServicePackMajor=$ServicePackMajor,`
Build=$Build,LastUpdated=$LastUpdated,LastReportedStatusTime=$LastReportedStatusTime,TargetGroups=$TargetGroups"
ForEach ($hotfix in $Hotfixes) {
If ($Serverinfo.$Hotfix) {
$SqlQuery=$SqlQuery + ",KB$Hotfix = " + "1"
}
}
$SqlQuery = $SqlQuery + " WHERE ComputerName=$Server"
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.UpdateCommand = $SqlCmd
$SqlCmd.ExecuteNonQuery()
}
Else {
$KBColumns="";$KBValues=""
ForEach ($hotfix in $Hotfixes) {
If ($Serverinfo.$Hotfix) {
$KBColumns=$KBColumns+",KB$Hotfix"
$KBValues=$KBValues + ",1"
}
}
$SqlQuery = "INSERT INTO PatchTable(ComputerName,IPAddress,ClientVersion,`
OSArchitecture,OSDescription,ServicePackMajor,Build,LastUpdated,LastReportedStatusTimeTargetGroups" +$KBColumns +") VALUES ($Server,$IPAddress,$ClientVersion,`
$OSArchitecture,$OSDescription,$ServicePackMajor,$Build,$LastUpdated,$LastReportedStatusTime,$TargetGroups"+ $KBValues + " )"
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.InsertCommand = $SqlCmd
$SqlCmd.ExecuteNonQuery()
}
$SqlConnection.Close()
}
######### Main Script #########################################################################################################
CreatePatchCollection
###############################################################################################################################
 
 
 
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s