Created
July 4, 2018 08:17
-
-
Save DarkAllien/3e4d957234c641f211e2e57aee991e10 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Function Detect_Application_Updates { | |
| $flag = $false | |
| $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*") { | |
| $flag = $true | |
| } | |
| } | |
| } | |
| "$time - CI.SQL.Servers.Application_Updates - Detection = $flag" | out-file "$env:windir\Logs\CI.SQL.Servers.Application_Updates.log" -Append | |
| return $flag | |
| } | |
| Function Reboot_Pending { | |
| <# | |
| .SYNOPSIS | |
| The function is checking if there is a reboot pending. | |
| .DESCRIPTION | |
| The function is checking if there is a reboot pending. | |
| #> | |
| $Reboot = $false | |
| $SCCMUpdate = get-wmiobject -query "SELECT * FROM CCM_SoftwareUpdate" -namespace "ROOT\ccm\ClientSDK" | |
| $Reboot = $Reboot -or ([wmiclass]'ROOT\ccm\ClientSDK:CCM_ClientUtilities').DetermineIfRebootPending().RebootPending | |
| $Reboot = $Reboot -or ([wmiclass]'ROOT\ccm\ClientSDK:CCM_ClientUtilities').DetermineIfRebootPending().IsHardRebootPending | |
| if (@(((Get-ItemProperty("HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager")).$("PendingFileRenameOperations")) | Where-Object { $_ }).length -ne 0) {$Reboot = $true} | |
| if (@($SCCMUpdate | Where-Object { $_.EvaluationState -eq 8 -or $_.EvaluationState -eq 9 -or $_.EvaluationState -eq 10 }).length -ne 0) {$Reboot = $true} | |
| return $Reboot | |
| } | |
| Function Detect_Backup { | |
| <# | |
| .SYNOPSIS | |
| The function is checking if there is a backup of sql taken in last 12 hours. | |
| .DESCRIPTION | |
| The function is checking if there is a backup of sql taken in last 12 hours. | |
| #> | |
| $sqlCmd = " | |
| SELECT TOP 1 | |
| @@SERVERNAME [ServerName] | |
| ,[canBePatched] = | |
| CASE | |
| WHEN (DATEDIFF([hh], [LastBackup], GETDATE()) < 12) THEN 1 | |
| ELSE 0 | |
| END | |
| ,DatabaseName | |
| ,[LastBackup] | |
| ,BackupType | |
| FROM (SELECT | |
| bck.DatabaseName | |
| ,LastBackup = | |
| CASE | |
| WHEN LastFullbackup > LastLogBackup THEN LastFullbackup | |
| ELSE LastLogbackup | |
| END | |
| ,BackupType = | |
| CASE | |
| WHEN LastFullbackup > LastLogBackup THEN 'Full' | |
| ELSE 'Log' | |
| END | |
| FROM (SELECT | |
| [DatabaseName] = [db].[name] | |
| ,[LastFullbackup] = (SELECT TOP 1 | |
| [s].[backup_start_date] | |
| FROM [msdb].[dbo].[backupset] [s] | |
| WHERE [s].[database_name] = [db].[name] | |
| AND [s].[Type] = 'D' | |
| ORDER BY [s].[backup_start_date] DESC) | |
| ,[LastLogbackup] = (SELECT TOP 1 | |
| [s].[backup_start_date] | |
| FROM [msdb].[dbo].[backupset] [s] | |
| WHERE [s].[database_name] = [db].[name] | |
| AND [s].[Type] = 'L' | |
| ORDER BY [s].[backup_start_date] DESC) | |
| FROM [Sys].[databases] [db] | |
| WHERE [db].[database_id] > 4 | |
| AND state = 0 | |
| AND is_read_only = 0) bck) AS [t] | |
| ORDER BY [LastBackup]; | |
| " | |
| try { | |
| $result = Invoke-Sqlcmd $sqlCmd -Database master | |
| } | |
| catch { | |
| return $false | |
| } | |
| $DatabaseName=$result.DatabaseName.tostring() | |
| if ($DatabaseName.Length -eq 0){$DatabaseName='Null'} | |
| $LastBackup=$result.LastBackup.tostring() | |
| if ($LastBackup.Length -eq 0){$LastBackup='Null'} | |
| $BackupType=$result.BackupType.tostring() | |
| if ($BackupType.Length -eq 0){$BackupType='Null'} | |
| if ($result.canBePatched -eq 0){$backupOK='False'} | |
| else {$backupOK='True'} | |
| #Get-CimInstance -ClassName SQLPatching -filter "Status='Detecting'" | Remove-CimInstance | |
| New-CimInstance -ClassName SQLPatching -Property @{DatabaseName = $DatabaseName; BackupType = $BackupType; canBePatched = $backupOK; LastBackupTime = $LastBackup; QueryTime= $t; Status = 'Detecting';SQLVersion=$SQLVersion.tostring()} | |
| ##Detecting | |
| if ($result.canBePatched -eq 1) | |
| { return $true } | |
| else | |
| { | |
| return $false} | |
| } | |
| Function Installation_InProgress { | |
| <# | |
| .SYNOPSIS | |
| The function will check if there are updates in progress | |
| .DESCRIPTION | |
| #> | |
| $Installing = $False | |
| $installing_app = $False | |
| $SCCMUpdate = get-wmiobject -query "SELECT * FROM CCM_SoftwareUpdate" -namespace "ROOT\ccm\ClientSDK" | |
| if (@($SCCMUpdate | Where-Object {($_.EvaluationState -ge 2 -and $_.EvaluationState -le 7) -or $_.EvaluationState -eq 11 }).length -ne 0) { $installing = $true } else { $installing = $false } | |
| $SCCMApplication = get-wmiobject -query "SELECT * FROM CCM_Application" -namespace "ROOT\ccm\ClientSDK" | |
| if (@($SCCMApplication | Where-Object {($_.EvaluationState -ge 5 -and $_.EvaluationState -le 12) -or $_.EvaluationState -eq 20}).length -ne 0) { $installing_app = $true } else { $installing_app = $false } | |
| if ($installing_app -or $installing) { | |
| return $true | |
| } | |
| else { | |
| return $false | |
| } | |
| } | |
| #cleaning WMI calss | |
| $count_instances = Get-CimInstance -ClassName SQLPatching | |
| if ($count_instances.Count -ge 20) { | |
| Get-CimInstance -ClassName SQLPatching | Remove-CimInstance | |
| } | |
| #Creating WMI class with properties | |
| $newClass = New-Object System.Management.ManagementClass ("root\cimv2", [String]::Empty, $null); | |
| $newClass["__CLASS"] = "SQLPatching"; | |
| $newClass.Qualifiers.Add("Static", $true) | |
| $newClass.Properties.Add("DatabaseName", [System.Management.CimType]::String, $false) | |
| $newClass.Properties["DatabaseName"].Qualifiers.Add("Key", $true) | |
| $newClass.Properties.Add("BackupType", [System.Management.CimType]::String, $false) | |
| $newClass.Properties["BackupType"].Qualifiers.Add("Key", $false) | |
| $newClass.Properties.Add("canBePatched", [System.Management.CimType]::String, $false) | |
| $newClass.Properties["canBePatched"].Qualifiers.Add("Key", $false) | |
| $newClass.Properties.Add("LastBackupTime", [System.Management.CimType]::String, $false) | |
| $newClass.Properties["LastBackupTime"].Qualifiers.Add("Key", $false) | |
| $newClass.Properties.Add("QueryTime", [System.Management.CimType]::String, $false) | |
| $newClass.Properties["QueryTime"].Qualifiers.Add("Key", $true) | |
| $newClass.Properties.Add("Status", [System.Management.CimType]::String, $false) | |
| $newClass.Properties["Status"].Qualifiers.Add("Key", $false) | |
| $newClass.Properties.Add("SQLVersion", [System.Management.CimType]::String, $false) | |
| $newClass.Properties["SQLVersion"].Qualifiers.Add("Key", $false) | |
| $newClass.Put() | Out-Null | |
| $time = Get-Date | |
| $t = $time.ToString() | |
| 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 | |
| } | |
| $Detect_Backup = Detect_Backup | |
| $Backup = $Detect_Backup[-1] | |
| $Reboot_Pending = Reboot_Pending | |
| $Installation_InProgress = Installation_InProgress | |
| "$time - CI.SQL.Servers.Application_Updates - Started Detection" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| "$time - CI.SQL.Servers.Application_Updates - Backup = $Backup" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| "$time - CI.SQL.Servers.Application_Updates - Backup DATA = $Detect_Backup" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| "$time - CI.SQL.Servers.Application_Updates - Reboot Pending = $Reboot_Pending" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| "$time - CI.SQL.Servers.Application_Updates - Installation in Progress = $Installation_InProgress" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| #if backup taken in last 8 hours, no current software or update is installing and no pending reboot, install SQL updates (Applications) | |
| if (Detect_Application_Updates) { | |
| if (($Backup) -and (!($Reboot_Pending) -and !($Installation_InProgress))) { | |
| "$time - CI.SQL.Servers.Application_Updates - Conditions Met - Started Installation" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| $WMIdata = Get-CimInstance -ClassName SQLPatching -filter "QueryTime='$t'" | |
| Get-CimInstance -ClassName SQLPatching -filter "QueryTime='$t'"| Remove-CimInstance | |
| New-CimInstance -ClassName SQLPatching -Property @{DatabaseName = $WMIdata.DatabaseName.ToString(); BackupType = $WMIdata.BackupType.ToString(); canBePatched = $WMIdata.canBePatched.ToString(); LastBackupTime = $WMIdata.LastBackupTime.ToString(); QueryTime= $WMIdata.QueryTime.ToString(); Status = 'Started Applications Installation';SQLVersion=$SQLVersion.tostring()} -ErrorAction SilentlyContinue | Out-Null | |
| #Started Applications Installation | |
| return $false | |
| } | |
| Else { | |
| "$time - CI.SQL.Servers.Application_Updates - Conditions NOT Met" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| $WMIdata = Get-CimInstance -ClassName SQLPatching -filter "QueryTime='$t'" | |
| Get-CimInstance -ClassName SQLPatching -filter "QueryTime='$t'"| Remove-CimInstance | |
| New-CimInstance -ClassName SQLPatching -Property @{DatabaseName = $WMIdata.DatabaseName.ToString(); BackupType = $WMIdata.BackupType.ToString(); canBePatched = $WMIdata.canBePatched.ToString(); LastBackupTime = $WMIdata.LastBackupTime.ToString(); QueryTime= $WMIdata.QueryTime.ToString(); Status = 'Conditions NOT Met - Apps';SQLVersion=$SQLVersion.tostring()} -ErrorAction SilentlyContinue | Out-Null | |
| #Conditions NOT Met | |
| return $true | |
| } | |
| } | |
| Else { | |
| "$time - CI.SQL.Servers.Application_Updates - No Applications to install" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| $WMIdata = Get-CimInstance -ClassName SQLPatching -filter "QueryTime='$t'" | |
| Get-CimInstance -ClassName SQLPatching -filter "QueryTime='$t'"| Remove-CimInstance | |
| New-CimInstance -ClassName SQLPatching -Property @{DatabaseName = $WMIdata.DatabaseName.ToString(); BackupType = $WMIdata.BackupType.ToString(); canBePatched = $WMIdata.canBePatched.ToString(); LastBackupTime = $WMIdata.LastBackupTime.ToString(); QueryTime= $WMIdata.QueryTime.ToString(); Status = 'No Applications to install';SQLVersion=$SQLVersion.tostring()} -ErrorAction SilentlyContinue | Out-Null | |
| #No Applications to install | |
| return $true | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment