CI.SQL.Servers.Update_Applications-remediate.ps1

Function Check_MW_Go {
    #getting maintenance windows of type 1 (All deployments)
    #https://msdn.microsoft.com/library/jj155419.aspx
    $CCMServiceWindows = Get-WmiObject -Namespace root\ccm\clientsdk -Class CCM_ServiceWindow -Filter 'Type=1'
    $Checker = $false

    #parsing maintenance windows to determine if we are in one
    foreach ($CCMServiceWindow in $CCMServiceWindows) {
        $StartTime = $CCMServiceWindow.StartTime.Substring(0, 12)
        $EndTime = $CCMServiceWindow.EndTime.Substring(0, 12)
        $StartMW = [datetime]::ParseExact($StartTime, "yyyyMMddHHmm", $null)
        $EndMW = [datetime]::ParseExact($EndTime, "yyyyMMddHHmm", $null)
        $c_time = get-date

        # checking MW interval
        if ($c_time -ge $StartMW -and $c_time -lt $EndMW) {
            $Checker = $true
            "$time - Found MW: $StartMW - $EndMW" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append
        }   
    }
    return $Checker
}
Function Trigger_AppInstallation {
    Param
    (
        [String][Parameter(Mandatory = $True, Position = 2)] $AppName,
        [ValidateSet("Install", "Uninstall")]
        [String][Parameter(Mandatory = $True, Position = 3)] $Method
    )
    Begin {
        $Application = (Get-CimInstance -ClassName CCM_Application -Namespace "root\ccm\clientSDK"  | Where-Object {$_.Name -like $AppName})
        $Args = @{EnforcePreference = [UINT32] 0
            Id                      = "$($Application.id)"
            IsMachineTarget         = $Application.IsMachineTarget
            IsRebootIfNeeded        = $False
            Priority                = 'High'
            Revision                = "$($Application.Revision)" 
        }
 
    }
    Process {
        Invoke-CimMethod -Namespace "root\ccm\clientSDK" -ClassName CCM_Application -MethodName $Method -Arguments $Args
    }
    End {}
}
$time = Get-Date
Try {

    $RegExPattern = "[0-9]+\.[0-9]+\.[0-9]+.[0-9]+"
    [System.Version]$SQLVersion = (Invoke-Command -ScriptBlock { SQLCMD.exe -Q "Select @@Version" } -ErrorAction Stop | Select-String -Pattern $RegExPattern).Matches.Value
}
Catch {
    ## Catch Error if SQLCMD is not Found
}

if (Check_MW_Go) {
    "$time - CI.SQL.Servers.Application_Updates - MW OK - Started Remediation" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append
    $Applications = Get-CimInstance -ClassName CCM_application -Namespace root\ccm\clientsdk | Select-Object InstallState, Name, ID, ApplicabilityState
    foreach ($app in $Applications) {
        if (($app.InstallState -ne "Installed") -and ($app.ApplicabilityState -eq "Applicable")) {
            if ($app.Name -like "*SQL*") {
                $Application = $app.name
                $WMIdata = Get-CimInstance -ClassName SQLPatching | Sort-Object -Property Querytime
                $t = $WMIdata[-1].QueryTime
                Get-CimInstance -ClassName SQLPatching -filter "QueryTime='$t'"| Remove-CimInstance
                "$time - CI.SQL.Servers.Application_Updates - Installing: $Application" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append
                New-CimInstance -ClassName SQLPatching -Property @{DatabaseName = $WMIdata[-1].DatabaseName.ToString(); BackupType = $WMIdata[-1].BackupType.ToString(); canBePatched = $WMIdata[-1].canBePatched.ToString(); LastBackupTime = $WMIdata[-1].LastBackupTime.ToString(); QueryTime = $WMIdata[-1].QueryTime.ToString(); Status = 'Installing: ' + $Application; SQLVersion = $SQLVersion.tostring()} -ErrorAction SilentlyContinue
                Trigger_AppInstallation $app.name Install
            }
        }
    }
}
else {
    "$time - CI.SQL.Servers.Application_Updates - MW NOT OK - Stopped Remediation" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append
}