Created
July 4, 2018 08:19
-
-
Save DarkAllien/eb9c442c32f063769a827f014fea00ae 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 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()} | |
| 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 | |
| } | |
| } | |
| Function Missing_updates { | |
| <# | |
| .SYNOPSIS | |
| The function will check if there are missing updates | |
| #> | |
| $MissingUpdates = get-wmiobject -query "SELECT * FROM CCM_SoftwareUpdate WHERE ComplianceState = 0 and name like '%sql%'" -namespace "ROOT\ccm\ClientSDK" | |
| if ( @($MissingUpdates | ForEach-Object {{[WMI]$_.__PATH}}).count -eq 0) { | |
| return $false | |
| } | |
| else { | |
| return $true | |
| } | |
| } | |
| #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.Updates - Started Detection" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| "$time - CI.SQL.Servers.Updates - Backup = $Backup" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| "$time - CI.SQL.Servers.Updates - Backup DATA = $Detect_Backup" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| "$time - CI.SQL.Servers.Updates - Reboot Pending = $Reboot_Pending" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| "$time - CI.SQL.Servers.Updates - Installation in Progress = $Installation_InProgress" | out-file "$env:windir\Logs\CB.SQL.Servers.Updates.log" -Append | |
| #if backup taken in last 12 hours, no current software or update is installing and no pending reboot, install SQL updates | |
| if (Missing_updates) { | |
| if (($Backup) -and !($Reboot_Pending) -and !($Installation_InProgress)) { | |
| "$time - CI.SQL.Servers.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 = 'Updates will install'; SQLVersion = $SQLVersion.tostring()} -ErrorAction SilentlyContinue | Out-Null | |
| #Updates will install | |
| return $false | |
| } | |
| else { | |
| "$time - CI.SQL.Servers.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 - Updates'; SQLVersion = $SQLVersion.tostring()} -ErrorAction SilentlyContinue | Out-Null | |
| #Conditions NOT Met | |
| return $true | |
| } | |
| } | |
| else { | |
| "$time - CI.SQL.Servers.Updates - No Updates 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 Updates to install'; SQLVersion = $SQLVersion.tostring()} -ErrorAction SilentlyContinue | Out-Null | |
| #No Updates to install | |
| return $true | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment