functions/Get-SQLServerUpdates.ps1
|
#Requires -Version 4.0 # https://github.com/AngleSharp/AngleSharp # https://github.com/AngleSharp/AngleSharp/blob/master/doc/Basics.md function Get-SQLServerUpdates { <# .Synopsis Returns available update list for SQl Server .DESCRIPTION Returns available updates list (cumulatvie update, service pack) for SQL Server. Source for updates list - http://sqlserverupdates.com/ Additional information. When I wrote this script I had a problem with Exception 0x800A01B6, some tips for me were at this site: https://www.sepago.com/blog/2016/05/03/powershell-exception-0x800a01b6-while-using-getelementsbytagname-getelementsbyname. In the script I use IHTMLDocument3_getElementsByTagName instead of method getElementsByTagName. I recommend to use the script at client stations, but I encourage to make a test at servers alternatively. Please send me information about errors and problems by email mnadobnik+blog@gmail.com .NOTES Author: Mateusz Nadobnik, mnadobnik.pl Requires: sysadmin access on SQL Servers SQLServerUpdates PowerShell module (http://mnadobnik.pl/sqlserverupdates, mnadobnik@gmail.com) Copyright (C) 2017 Mateusz Nadobnik .LINK http://mnadobnik.pl/sqlserverupdates .EXAMPLE Get-SQLServerUpdateList Update list for all SQL Server from version 2008 to 2016 .EXAMPLE Get-SQLServerUpdateList -Version 2012 Update list only for SQL Server 2012 .LINK Author: Mateusz Nadobnik Link: mnadobnik.pl Date: 09.12.2017 Version: 1.0.1.1 Keywords: SQL Server, Updates, Get Notes: 1.0.0.4 - Added new object (Link) with links without marks HTML. 1.0.0.5 - Repaired error with TLS 1.2 and added SQL Server 2017 1.0.0.7 - Repaired error Cannot index into a null array. 1.0.1.0 - Repaired error with SQL Server 2017 and refactoring of code. 1.0.1.1 - Repaired error with SQL Server 2008 R2 1.0.1.2 - Bad property outerHTML instead of innerText in the function Get-SQLServerUpdates #> [CmdletBinding()] [Alias()] [OutputType([string])] Param ( #Version SQL Sever [ValidateSet('SQL Server 2008', 'SQL Server 2008 R2', 'SQL Server 2012', 'SQL Server 2014', 'SQL Server 2016', 'SQL Server 2017')] [string]$Version ) Begin { $linkRegex = '"[^"]*"' $ObjReturn = @() try { #Enable TLS 1.2 [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 $content = Invoke-WebRequest -Uri 'http://sqlserverupdates.com/' } catch { Write-Host "Check connection..." Write-Warning $_.Exception.Message Break } # setting for count of column in table on website $ColumnSetting = [ordered]@{ 'SQL Server 2008' = 4 'SQL Server 2008 R2' = 4 'SQL Server 2012' = 5 'SQL Server 2014' = 5 'SQL Server 2016' = 5 'SQL Server 2017' = 4 } $VersionSQL = [ordered]@{ 'SQL Server 2008' = ($content.Links | Where-Object InnerText -like "SQL*2008?U*") 'SQL Server 2008 R2' = ($content.Links | Where-Object InnerText -like "SQL*2008?R2*") 'SQL Server 2012' = ($content.Links | Where-Object InnerText -like "SQL*2012*") 'SQL Server 2014' = ($content.Links | Where-Object InnerText -like "SQL*2014*") 'SQL Server 2016' = ($content.Links | Where-Object InnerText -like "SQL*2016*") 'SQL Server 2017' = ($content.Links | Where-Object InnerText -like "SQL*2017*") } # if set parameter -Version if ($Version) { $VersionSQL = [ordered]@{ $Version = $VersionSQL.$Version } } } Process { foreach ($SQL in $VersionSQL.Keys) { #$SQL = 'SQL Server 2008' try { $ListUpdates = Invoke-WebRequest -Uri $VersionSQL.$SQL.href } catch { Write-Warning $_.Exception.Message Break } try { $tableUpdateTR = ($ListUpdates.ParsedHtml).IHTMLDocument3_getElementsByTagName("tr") $tableUpdateTD = $tableUpdateTR | ForEach-Object { ($_.children) } } catch { Write-Warning $_.Exception.Message Break } for ($i = $ColumnSetting.$SQL; $i -lt $tableUpdateTD.Count; $i++) { # new object $update = @{ } | Select-Object Name, ServicePack, CumulativeUpdate, ReleaseDate, Link, Build, SupportEnds $update.Name = ($VersionSQL.$SQL.innerText).Replace(" Updates", "") if ($ColumnSetting.$SQL -eq 5) { $update.ServicePack = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", ""); $i++ $update.CumulativeUpdate = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", ""); $i++ $update.ReleaseDate = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", ""); $i++ $update.Build = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", ""); $i++ $update.SupportEnds = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", "") } elseif ($ColumnSetting.$SQL -eq 4) { $update.ServicePack = '' $update.CumulativeUpdate = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", ""); $i++ $update.ReleaseDate = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", ""); $i++ $update.Build = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", ""); $i++ $update.SupportEnds = (($tableUpdateTD[$i].innerHTML) -Replace "( | |^\s)", "") } try { if ([regex]::Matches($update.CumulativeUpdate, $linkRegex) -ne "") { $update.Link = ([regex]::Matches($update.CumulativeUpdate, $linkRegex)[0].Value).Replace('"', '') } elseif ([regex]::Matches($object.ServicePack, $linkRegex) -ne "") { $update.Link = ([regex]::Matches($update.ServicePack, $linkRegex)[0].Value).Replace('"', '') } } catch { $object.Link = $null } $ObjReturn += $update } } } End { return $ObjReturn } } |