March 18, 2021 at 11:58 am
I needed a method to test whether all my SQL servers were patched, so I created a script that reads the tables from https://sqlserverbuilds.blogspot.com and gives the latest build numbers for all versions. I'm sure someone else already wrote something like this, but what else to do with the bosses' time?
Tested with powershell 5 and powershell 7.1.2.
function Convert-ToHTMLFile {
param(
[parameter(Mandatory = $true)][string]$Content
)
$NormalHTML = New-Object -Com "HTMLFile"
if ($PSVersionTable.PSVersion.Major -gt 5) {
$NormalHTML.write([System.Text.Encoding]::Unicode.GetBytes($WebRequestContent))
} else {
$NormalHTML.IHTMLDocument2_write($WebRequestContent)
}
return $NormalHTML
}
function Parse-HTMLTable {
param(
[Parameter(Mandatory = $true)][string]$WebRequestContent,
[Parameter(Mandatory = $true)][int] $TableNumber
)
$NormalHTML = Convert-ToHTMLFile -Content $WebRequestContent
## Extract the tables out of the web request
$tables = @($NormalHTML.getElementsByTagName("TABLE"))
if (($tables | Measure-Object).count -lt $TableNumber) {
return $null
}
$table = $tables[$TableNumber]
$titles = @()
$rows = @($table.Rows)
## Go through all of the rows in the table
foreach($row in $rows) {
$cells = @($row.Cells)
## If we've found a table header, remember its titles
if($cells[0].tagName -eq "TH") {
$titles = @($cells | % { ("" + $_.InnerText).Trim().Replace(' ','') })
continue
}
## If we haven't found any table headers, make up names "P1", "P2", etc.
if(-not $titles) {
$titles = @(1..($cells.Count + 2) | % { "P$_" })
}
## Now go through the cells in the the row. For each, try to find the
## title that represents that column and create a hashtable mapping those
## titles to content
$resultObject = [Ordered] @{}
for($counter = 0; $counter -lt $cells.Count; $counter++) {
$title = $titles[$counter]
if(-not $title) { continue }
$resultObject[$title] = ("" + $cells[$counter].InnerText).Trim()
}
## And finally cast that hashtable to a PSCustomObject
[PSCustomObject] $resultObject
}
}
$SQLVersions = (Invoke-WebRequest -Uri "https://sqlserverbuilds.blogspot.com/" -UseBasicParsing).content
$i = 0
$SQLVersionTable = Do {
$table = Parse-HTMLTable -WebRequestContent $SQLVersions -TableNumber $i
if ($null -eq $table) { continue }
If ($table[0].build) {
[pscustomobject][ordered]@{
Build = $table[0].Build
Description = $table[0].'KB/Description'
Released = $table[0].ReleaseDate -replace ' .*',''
}
}
$i++
} until ($null -eq $table)
$SQLVersionTableMarch 19, 2021 at 12:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply