Skip to content

Instantly share code, notes, and snippets.

  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
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 ->
# 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
# Method 1: Module as part of SQL Setup.exe
Import-Module SqlPs -DisableNameChecking;
# 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 "$($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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment