Public/Get-SPInstancePatchDetails.ps1

function Get-SPInstancePatchDetails { 
    <#
    .SYNOPSIS
    Function that gets the SQL patch level and details from a SQL instance.
 
    .EXAMPLE
    Get-SPInstancePatchDetails -SqlInstance "SERVER1\SQLDEV01"
 
    .EXAMPLE
    Get-SPPatchFileInfo "C:\SqlPatches\SQLServer2017-KB4535007-x64.exe"
 
    .NOTES
    Author : Patrick Cull
    Date : 2020-05-12
    #>

    [Cmdletbinding()]
    param(    
        #The server to be patched.
        [Parameter(ValueFromPipeline, Mandatory)]
        [string[]] $SqlInstance
    )
    
    process {
        foreach($instance in $SqlInstance) {
            #Get the target server and instance name from the SqlInstance passed.
            $InstanceNameParts = $instance -split '\\'
            $TargetServer = $InstanceNameParts[0]
            
            if($InstanceNameParts[1]) {
                $InstanceName = $InstanceNameParts[1]
            }
            else {
                $InstanceName = "MSSQLSERVER"
            }
            
            #Get the current patchlevel of the target instance along with the location of the SQL Server ERROR log - which we use to get more info about the instance.
            Invoke-Command -ComputerName $TargetServer {
                $InstanceRegPath = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL") | Select-Object -ExpandProperty $Using:InstanceName
                $InstanceVersionInfo = (Get-ItemProperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceRegPath\Setup")
            
                $InstanceParameters = (Get-ItemProperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceRegPath\MSSQLServer\Parameters")
                $ErrorLogLocation = ($InstanceParameters.PSObject.properties | ForEach-Object {$_.Value} | Where-Object {$_ -like "*ERRORLOG"}) -replace '-e'
                
                $InstanceVersionInfo.PatchLevel, $ErrorLogLocation
            }

            if(!$InstancePatchInfo[0]) {
                Write-Warning "Issue getting patch info of $InstanceName on $TargetServer"
            }

            else {
                $InstancePatchLevel = $InstancePatchInfo[0]
                $ErrorLogLocation = $InstancePatchInfo[1]

                $InstancePatchLevelSplit = $InstancePatchLevel -split '\.'

                #Number after the second dot in the patch level indicates the Service Pack number.
                $SPNumber = $InstancePatchLevelSplit[1]
                if($SPNumber -eq "0") {
                    $SPNumber = $null
                }
                #SQL 2008 R2 uses two numbers for the SP number, the second digit is the service pack number.
                elseif($SPNumber.Length -eq 2) {
                    $SPNumber = $SPNumber[-1]
                }

                $NetworkSqlServiceLogPath = "\\$TargetServer\$ErrorLogLocation" -replace ':', '$'
                
                $SqlServiceLogContent = Get-Content $NetworkSqlServiceLogPath
                $VersionString = "Microsoft " + (($SqlServiceLogContent[0]) -split "Microsoft ")[1]

                # This returns the year of the SQL Server version.
                $Version = ($VersionString.Split(" "))[3]

                if($Version -eq "2008") {
                    #Check for R2 release
                    $ReleaseNum = ($VersionString.Split(" "))[4]
                    if($ReleaseNum -eq "R2") {
                        $Version = $Version + " $ReleaseNum"
                    }
                }

                $SqlVersion =  "SQL Server $Version"

                #Check for any string after "CU" to get the instance CU number, also remove the "-GDR" string if that's in the string.
                $CUPattern = "(CU.*?)\)"
                $CUNumber = ([regex]::match($VersionString, $CUPattern).Groups[1].Value) -replace '-GDR'

                $KBPattern = "(KB.*?)\)"
                $KBNumber = ([regex]::match($VersionString, $KBPattern).Groups[1].Value)
                
                [PSCustomObject][Ordered] @{
                    SqlInstance = $instance
                    SqlVersion = $SqlVersion
                    PatchVersion = $InstancePatchLevel
                    ServicePack = $SPNumber
                    CumulativeUpdate = $CUNumber
                    KBNumber = $KBNumber
                }
            }
        
        }# end foreach instance
    } # end process
}# end Get-SPInstancePatchDetails