CI.SQL.Servers.Updates-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
}

$time = Get-Date

if (Check_MW_Go) {
    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
    }
    # Add regkey to Control Restart (0 initial, 1 needs restart)
    Push-Location
    Set-Location HKLM:
    $registryPath = "SYSTEM\SCCM"
     
    $Group = "SQL_Restart"
    $Group_value = "0"
    New-Item -Path $registryPath
    New-ItemProperty -Path $registryPath -Name $Group -Value $Group_value -PropertyType String -Force
    Pop-Location

    "$time - CI.SQL.Servers.Updates - MW OK - Started Installation" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append
    $MissingUpdates = get-wmiobject -query "SELECT * FROM CCM_SoftwareUpdate WHERE ComplianceState = 0 and name like '%sql%'" -namespace "ROOT\ccm\ClientSDK"
    "$time - CI.SQL.Servers.Updates - $MissingUpdates" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append
    $WMIdata = Get-CimInstance -ClassName SQLPatching | Sort-Object -Property Querytime
    $t = $WMIdata[-1].QueryTime
    Get-CimInstance -ClassName SQLPatching -filter "QueryTime='$t'"| Remove-CimInstance
    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: ' + $MissingUpdates.Name; SQLVersion = $SQLVersion.tostring()} -ErrorAction SilentlyContinue
    Get-Service -name SQLSERVERAGENT | Stop-Service
    Invoke-WmiMethod -Class CCM_SoftwareUpdatesManager -Name InstallUpdates -ArgumentList (, $MissingUpdates) -Namespace root\ccm\clientsdk 
   
    # Add regkey to Control Restart (0 initial, 1 needs restart)
    Push-Location
    Set-Location HKLM:
    $Group_value = "1"
    New-Item -Path $registryPath
    New-ItemProperty -Path $registryPath -Name $Group -Value $Group_value -PropertyType String -Force
    Pop-Location
}
else {
    "$time - CI.SQL.Servers.Updates - MW NOT OK - Stopped Installation" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append
}