Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Example SQL Health Check script for querying the SQL Versions API for life cycle support information
# Example script for using the SQL Versions API to check the life cycle and support information of a particular SQL Version
# Further information here -> http://blog.matticus.net/2018/06/new-sql-version-life-cycle-tool.html
# Live data example of a single instance
# Import the SQL Module
# NOTE: In July 2016 a new module was released therefore there are two methods of this now
# https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/
# Method 1: Module as part of SQL Setup.exe
Push-Location;
Import-Module SqlPs -DisableNameChecking;
Pop-Location;
# Method 2: Module included with SSMS monthly releases
Import-Module SqlServer;
# Get the version number via appropriate method
$VersionNumber = Invoke-Sqlcmd -ServerInstance $SqlServerName -Query "SELECT SERVERPROPERTY('PRODUCTVERSION')";
$VersionNumber = $VersionNumber.Column1;
# Call the API to get the version information
$VersionData = Invoke-RestMethod -Uri "http://sqlserverbuildsapi.azurewebsites.net/api/builds?version=$($VersionNumber)";
# Want to improve your Health Check script, calculate the health of Support Status
if ($VersionData.ExtendedSupportEnd -le (Get-Date)) {$SupportStatus = 'Critical'}
elseif ($VersionData.MainstreamSupportEnd -le (Get-Date)) {$SupportStatus = 'Warning'}
else {$SupportStatus = 'Ok'}
# format the output data string
$OutputData = @"
Instance = $($Instance.Name)
Version = $($VersionData.BuildVersion)
Product = $($VersionData.ProductName)
Branch = $($VersionData.BranchName)
Update = $($VersionData.BuildUpdate)
MainstreamSupportEnd = $($VersionData.SupportEndMainstream)
ExtendedSupportEnd = $($VersionData.SupportEndExtended)
SupportStatus = $($SupportStatus)
"@
# Return the hashtable
$OutputData
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment