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