Finds the recommended N-1 cumulative update for SQL Server based on patching strategy that avoids intermediate releases

Get-RecommendedSqlCU.ps1

<#
.SYNOPSIS
    Finds the recommended N-1 cumulative update for SQL Server based on patching strategy that avoids intermediate releases
.DESCRIPTION
    This script queries a Google Sheets document containing SQL Server cumulative update information
    and determines the recommended N-1 cumulative update based on a patching strategy that avoids
    intermediate releases (e.g., hotfixes, security updates) between cumulative updates.
    The script outputs the recommended cumulative update, its release date, and a link to more details.

    The patching strategy is defined as follows:
        We do not patch to the absolute latest cumulative update(CU).
        
        Rather, we strive to patch to the cumulative update(CU) that 
        was released without any intermediate releases(such as hotfixes) 
        between it and the most recent CU. Basically N-1 as long as 
        there aren't any hotfixes. If there were hotfixes then we'll wait 
        until two CU subsequent releases have occurred 
        without hotfixes in between the two to deploy the CU.
        
        This minimizes the need to uninstall patches if they are 
        withdrawn by Microsoft. This also minimizes the need 
        to "patch the patch" when there are hotfixes / security
        updates / withdrawals for a given CU.
.PARAMETER SqlMajorVersion
    The major version of SQL Server for which to find the recommended cumulative update.
    This should be specified as a string, e.g., "2019"
.NOTES
    File Name      : Get-RecommendedSqlCU.ps1
    Author         : Lars Rasmussen (@nanoDBA)

    Adapted from https://sqlserverbuilds.blogspot.com/2019/08/how-to-get-data-programmatically.html#powershell-all-builds
    Huge thanks to SQL Server Builds folks for the inspiration!
    Please support them by visiting their site, sharing their work, and donate if you can.
       https://sqlserverbuilds.blogspot.com/2018/12/donate.html
    Proposed SQL Server Patching Approach
    From https://gist.github.com/nanoDBA/c1712c96e5c7163d23858fa2b8d55540
         and https://www.linkedin.com/feed/update/urn:li:activity:7033810717115891712
.EXAMPLE
    .\Get-RecommendedSqlCU.ps1 -SqlMajorVersion 2019
    Finds the recommended N-1 cumulative update for SQL Server 2019 without intermediate releases.
#>

param (
    [Parameter(Mandatory=$true)]
    [string]$SqlMajorVersion     # The major version of SQL Server for which to find the recommended CU
)

# Define the Google Sheets query to fetch data for the specified SQL major version
# - Select columns (C: Build, E: Description, F: Link, G: ReleaseDate)
# - Exclude rows with empty ReleaseDate (G is not null)
# - Order by ReleaseDate descending

# Original query: Fetch all rows for the specified SQL major version
#$Query = "select C,E,F,G where A='${SqlMajorVersion}' order by G desc" #and (I=TRUE or K=TRUE)"

# Adjusted query: Fetch all rows for the specified SQL major version, excluding empty ReleaseDate
$Query = @"
select C, E, F, G 
where A='${SqlMajorVersion}' 
  and G is not null
order by G desc
"@.Trim()

# Build the Google Sheets API URL
# - This fetches the data in CSV format
$URL   = "https://docs.google.com/spreadsheets/d/16Ymdz80xlCzb6CwRFVokwo0onkofVYFoSkc7mYe6pgw/gviz/tq?tq=" `
       + [System.Net.WebUtility]::UrlEncode($Query) `
       + "&tqx=out:csv"

# Fetch data from Google Sheets and convert CSV to objects
$results = Invoke-RestMethod -Uri $URL | ConvertFrom-Csv

# Convert the ReleaseDate to [datetime] (for accurate sorting and comparisons)
# Convert the Build column to [version] (for proper numerical comparisons)
$results | ForEach-Object { 
    $_.ReleaseDate = [datetime]$_.ReleaseDate  # Ensures ReleaseDate is treated as a date
    $_.Build = [version]$_.Build              # Treats Build as a version number
}

# Filter the results to include only rows describing cumulative updates (CUs)
$cumulativeUpdates = $results | Where-Object { $_.Description -like "*Cumulative update*" }

# Sort the cumulative updates by Build number in descending order
# This ensures the most recent CU appears first
$sortedCUs = $cumulativeUpdates | Sort-Object Build -Descending

# Initialize a variable to hold the selected CU
$targetCU = $null

# Begin analyzing the sorted list of CUs to find the N-1 CU
# Loop through each CU, comparing it with the next newer CU in the list
for ($i = 1; $i -lt $sortedCUs.Count; $i++) {
    # $currentCU is the CU we're evaluating as a potential patch
    $currentCU = $sortedCUs[$i]

    # $nextCU is the CU immediately newer than $currentCU
    $nextCU = $sortedCUs[$i - 1]

    # Check for any intermediate releases (hotfixes, security updates, etc.) between $currentCU and $nextCU
    $intermediateReleases = $results | Where-Object {
        ($_.Build -gt $currentCU.Build) -and  # Ensure Build is newer than $currentCU
        ($_.Build -lt $nextCU.Build) -and    # Ensure Build is older than $nextCU
        ($_.Description -notlike "*Cumulative update*")  # Exclude CUs from intermediate releases
    }

    # If no intermediate releases are found, select $currentCU as the target patch
    if ($intermediateReleases.Count -eq 0) {
        $targetCU = $currentCU
        break  # Exit the loop since we've found our N-1 CU w/o intermediate releases
    }
}

# Output the recommended CU or indicate that none were found
if ($targetCU) {
    Write-Output "Recommended CU: $($targetCU.Build) - $($targetCU.Description)"
    Write-Output "Release Date: $($targetCU.ReleaseDate)"
    Write-Output "Details: $($targetCU.Link)"
} else {
    Write-Output "No suitable CU found based on the provided patching strategy."
}