Check current patch levels for all SQL Servers in environment

CREATE TABLE [Inventory] (
   [ServerName] VARCHAR(128),
   [InstanceName] VARCHAR(128),
   [Version] VARCHAR(20),
   [BuildNumber] VARCHAR(20), 
   [ServicePack] VARCHAR(20), 
   [OSVersion] VARCHAR(20), 
   [OSBuildNumber] VARCHAR(20), 
   [OSServicePack] VARCHAR(20), 
   [DatetimeLastUpdate] DATETIME) 
 
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('nonexist', 'nonexist', 'Microsoft SQL Server 2000')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WIND01', 'WIND01', 'Microsoft SQL Server 2014')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINT01', 'WINT01\TEST01', 'Microsoft SQL Server 2008')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINP04', 'WINP04\PROD04', 'Microsoft SQL Server 2016')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINP04', 'WINP04\PROD05', 'Microsoft SQL Server 2017')

$ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = "."
$database = "Inventory"
$query = @"
SELECT [ServerName], [InstanceName]
  FROM [Inventory]
"@
$SleepTimer = 1000 #after X milliseconds, check if the jobs have finished. 1000 is every second.
$MaxResultTime = 300 #after X seconds, all jobs are killed. 300 is 5 minutes.
$Maxthreads = 20 #number of parallel jobs
# import modules
Import-Module SqlPs -DisableNameChecking
$error.clear() #clear error generated by last command
# get list of instances
$objects = @(Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query)
# environment setup
$output = ""
$errors = ""
$Jobs = @()
$ISS = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$RunspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $ISS, $Host)
$RunspacePool.Open()
$script = {
   Param ([string]$serverName, [string]$instanceName)
   $ErrorActionPreference = "Stop"
   $query = "
      SET NOCOUNT ON --No 'rows returned' message
      SELECT SERVERPROPERTY('productversion') [ProductVersion], SERVERPROPERTY('productlevel') [ProductLevel]"
   try {
      $command = "UPDATE [Inventory] SET "
      # Get SQL info
      $data = sqlcmd -S $instanceName -Q $query -h-1 -W -b -r1 -s"\t" 2>&1 #no header, no whitespaces, break on error, errors to output, tab separator, stderr to stdout
      if (!$data -or $data.length -eq 0) { return $instanceName + ": Unable to query server" | Out-String } #server may not exist anymore
      $data = $data.split('\t')
      $command += "[BuildNumber] = '"+$data[0]+"'"
      # Get OS info
      $data = Get-WmiObject -class Win32_OperatingSystem -computername $serverName | select Version, BuildNumber, ServicePackMajorVersion
      $command += ", [OSVersion] = '"+$data."Version"+"', [OSBuildNumber] = '"+$data."BuildNumber"+"', [OSServicePack] = '"+$data."ServicePackMajorVersion"+"'"
      "" #indicate there was no error
      $command+", [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = '"+$instanceName+"';"
   }
   catch {
      return $instanceName + ": " + $LastExitCode + " " + $_ | foreach { $_.ToString() } | Out-String
   }   
}
function CreateThread() {
   param ([string]$serverName, [string]$instanceName, [ref]$Jobs)
   $PowershellThread = [powershell]::Create().AddScript($script) #scriptToRun
   $PowershellThread.AddArgument($serverName) | out-null
   $PowershellThread.AddArgument($instanceName) | out-null
   $PowershellThread.RunspacePool = $RunspacePool
   $Handle = $PowershellThread.BeginInvoke()
   $Job = "" | select Handle, Thread, object
   $Job.Handle = $Handle; $Job.Thread = $PowershellThread
   $Jobs.value += $Job
}
$ResultTimer = Get-Date #start time
#start processing first task for each instance
for ($i=0; $i -lt $objects.length; $i++) {
   CreateThread $objects[$i].ServerName $objects[$i].InstanceName ([ref]$Jobs)
}
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
   #update completed jobs, get errors and result, and dispose them
   foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
      $results = $Job.Thread.EndInvoke($Job.Handle)
      if ($results[0] -and $results[0] -ne "") {
         $errors += $results[0]
      }
      for ($i = 1; $i -lt $results.count; $i++) {
         $output += $results[$i]+"`r`n"
      }
      #end thread
      $Job.Thread.Dispose()
      $Job.Thread = $Null
      $Job.Handle = $Null
   }
   #show progress
   $inProgress = @($Jobs | where {$_.Handle.IsCompleted -eq $False}).count
   Write-Progress `
      -Activity "Gathering data" `
      -PercentComplete (($objects.length - $inProgress) * 100 / $objects.length) `
      -Status "$inProgress pending"
   #exit on timeout
   $currentTime = Get-Date
   if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
      Write-Error "Child script appears to be frozen, try increasing MaxResultTime"
      break
   }
   #sleep
   Start-Sleep -Milliseconds $SleepTimer
}
#dispose thread pools
$RunspacePool.Close() | Out-Null
$RunspacePool.Dispose() | Out-Null
$errors #return errors
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $output
			
			

PS C:\Users\PabloEcheverria\Desktop> .\UpdateInventory.ps1
nonexist: 1 HResult 0x102, Level 16, State 1
 
UPDATE [Inventory] SET [BuildNumber] = '12.0.4100.1', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'DEV01';
UPDATE [Inventory] SET [BuildNumber] = '10.0.5890.0', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'TEST01';
UPDATE [Inventory] SET [BuildNumber] = '13.0.4466.4', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'PROD04';
UPDATE [Inventory] SET [BuildNumber] = '14.0.3022.28', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'PROD05';
			

USE [Inventory]
GO
 
CREATE TABLE #Info ([Version] VARCHAR(128), [Build] VARCHAR(20))
INSERT INTO #Info VALUES ('Microsoft SQL Server 2000',    '8.00.2305')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2005',    '9.00.5324.00')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2008',    '10.0.6556.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2008 R2', '10.50.6560.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2012',    '11.0.7462.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2014',    '12.0.5579.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2016',    '13.0.4474.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2017',    '14.0.3023.8')
 
SELECT [msl].[ServerName], [msl].[InstanceName], [DatetimeLastUpdate], [msl].[BuildNumber], [i].[Build] [LatestBuild], CASE WHEN ISNULL([msl].[BuildNumber], '') <> [i].[Build] THEN 'NO' ELSE 'YES' END [Latest?] 
FROM [Inventory] [msl]
LEFT JOIN #Info [i] ON [i].[Version] = [msl].[Version]
ORDER BY [latest?], [msl].[InstanceName]
 
DROP TABLE #Info