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