Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- param([switch]$Elevated)
- function Test-Admin {
- $currentUser = New-Object Security.Principal.WindowsPrincipal $([Security.Principal.WindowsIdentity]::GetCurrent())
- $currentUser.IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator)
- }
- if ((Test-Admin) -eq $false) {
- if ($elevated)
- {
- # tried to elevate, did not work, aborting
- }
- else {
- Start-Process powershell.exe -Verb RunAs -ArgumentList ('-noprofile -noexit -file "{0}" -elevated' -f ($myinvocation.MyCommand.Definition))
- }
- exit
- }
- 'running with full privileges'
- <#
- WSUS-CLEANUP-UPDATES
- Runs WSUS cleanup task using stored procedures in WSUS database
- thus avoiding timeout errors that may occur when running WSUS Cleanup Wizard.
- The script is intended to run as a scheduled task on WSUS server
- but can also be used remotely. $SqlServer and $SqlDB variables
- must be defined before running the script on a server without WSUS.
- Version 4
- Version history:
- 4 Added database connection state check before deleting an
- unused update: the script will now attempt to reestablish
- connection if broken.
- #>
- ##########################
- # Configurable parameters
- $SqlServer = "" # SQL server host name; leave empty to use information from local registry
- $SqlDB = "SUSDB" # WSUS database name
- $SkipFileCleanup = $SqlServer -ne ""
- $log_source = "WSUS cleanup Task" # Event log source name
- $log_debugMode = $true # set to false to suppress console output
- ##########################
- $ErrorActionPreference = "Stop"
- # basic logging facility
- function log_init{
- if ( -not [System.Diagnostics.EventLog]::SourceExists($log_source) ){
- [System.Diagnostics.EventLog]::CreateEventSource($log_source, "Application")
- }
- }
- function log( [string] $msg, [int32] $eventID, [System.Diagnostics.EventLogEntryType] $level ){
- Write-EventLog -LogName Application -Source $log_source -EntryType $level -EventId $eventID -Message $msg
- if ( $log_debugMode ){
- switch ($level){
- Warning {Write-Host $msg -ForegroundColor Yellow }
- Error { Write-Host $msg -ForegroundColor Red }
- default { Write-Host $msg -ForegroundColor Gray }
- }
- }
- }
- function dbg( [string] $msg ){
- if ( $log_debugMode ){
- log "DBG: $msg" 300 "Information"
- }
- }
- log_init
- #########################
- function DeclineExpiredUpdates( $dbconn ){
- log "Declining expired updates" 1 "Information"
- $Command = New-Object System.Data.SQLClient.SQLCommand
- $Command.Connection = $dbconn
- $Command.CommandTimeout = 3600
- $Command.CommandText = "EXEC spDeclineExpiredUpdates"
- try{
- $Command.ExecuteNonQuery() | Out-Null
- }
- catch{
- $script:errorCount++
- log "Exception declining expired updates:`n$_" 99 "Error"
- }
- }
- #########################
- function DeclineSupersededUpdates( $dbconn ){
- log "Declining superseded updates" 1 "Information"
- $Command = New-Object System.Data.SQLClient.SQLCommand
- $Command.Connection = $dbconn
- $Command.CommandTimeout = 1800
- $Command.CommandText = "EXEC spDeclineSupersededUpdates"
- try{
- $Command.ExecuteNonQuery() | Out-Null
- }
- catch{
- $script:errorCount++
- log "Exception declining superseded updates:`n$_" 99 "Error"
- }
- }
- #######################
- function DeleteObsoleteUpdates( $dbconn ){
- Log "Reading obsolete update list." 1 "Information"
- $Command = New-Object System.Data.SQLClient.SQLCommand
- $Command.Connection = $dbconn
- $Command.CommandTimeout = 600
- $Command.CommandText = "EXEC spGetObsoleteUpdatesToCleanup"
- $reader = $Command.ExecuteReader()
- $table = New-Object System.Data.DataTable
- $table.Load($reader)
- $updatesTotal = $table.Rows.Count
- log "Found $updatesTotal updates that can be deleted." 1 "Information"
- $updatesProcessed=0
- $Command.CommandTimeout = 300
- foreach( $row in $table.Rows ){
- try{
- if ( $dbconn.State -ne [System.Data.ConnectionState]::Open ){
- log "Re-opening database connection" 2 "Warning"
- $dbconn.Open()
- }
- $updatesProcessed++
- log "Deleting update $($row.localUpdateID) ($updatesProcessed of $updatesTotal)" 1 "Information"
- $Command.CommandText = "exec spDeleteUpdate @LocalUpdateID=$($row.localUpdateID)"
- $Command.ExecuteNonQuery() | Out-Null
- }
- catch{
- $errorCount++
- log "Error deleting update $($row.localUpdateID):`n$_" 8 "Warning"
- }
- }
- }
- ###################
- function DbConnectionString{
- $WsusSetupKey = "HKLM:\SOFTWARE\Microsoft\Update Services\Server\Setup"
- if ( $script:SqlServer -eq "" ){
- $server = Get-ItemProperty -path $WsusSetupKey -Name "SqlServerName" -ErrorAction SilentlyContinue
- $db = Get-ItemProperty -path $WsusSetupKey -Name "SqlDatabaseName" -ErrorAction SilentlyContinue
- if ( ! $server ){
- throw "Cannot determine SQL server name"
- }
- $script:SqlServer = $server.SqlServerName
- $script:SqlDB = $db.SqlDatabaseName
- }
- if ( $script:SqlServer -match "microsoft##" ){
- return "data source=\\.\pipe\$script:SqlServer\tsql\query;Integrated Security=True;database='$script:SqlDB';Network Library=dbnmpntw"
- }
- else{
- return "server='$script:SqlServer';database='$script:SqlDB';trusted_connection=true;"
- }
- }
- ##############
- function DeleteUnusedContent{
- log "Deleting unneeded content files" 1 "Information"
- try{
- Import-Module UpdateServices
- $status = Invoke-WsusServerCleanup -CleanupUnneededContentFiles
- log "Done deleting unneeded content files: $status" 1 "Information"
- }
- catch{
- $script:errorCount++
- log "Exception deleting unneeded content files:`n$_" 99 "Error"
- }
- }
- ###################
- function DeleteInactiveComputers( $DbConn ){
- log "Removing obsolete computers" 1 "Information"
- $Command = New-Object System.Data.SQLClient.SQLCommand
- $Command.Connection = $dbconn
- $Command.CommandTimeout = 1800
- $Command.CommandText = "EXEC spCleanupObsoleteComputers"
- try{
- $Command.ExecuteNonQuery() | Out-Null
- }
- catch{
- $script:errorCount++
- log "Exception removing obsolete computers:`n$_" 99 "Error"
- }
- }
- function RestartWsusService{
- log "Stopping IIS.." 1 "Information"
- try{
- Stop-Service W3SVC -Force
- try{
- log "Restarting WSUS service.." 1 "Information"
- Restart-Service WsusService -Force
- }
- finally{
- log "Starting IIS..." 1 "Information"
- Start-Service W3SVC
- }
- }
- catch{
- $script:errorCount++
- log "Error restarting WSUS services:`n$_" 99 "Error"
- }
- Start-Sleep -Seconds 30
- }
- <#------------------------------------------------
- MAIN
- -------------------------------------------------#>
- $timeExecStart = Get-Date
- $errorCount = 0
- try{
- $Conn = New-Object System.Data.SQLClient.SQLConnection
- $Conn.ConnectionString = DbConnectionString
- log "Connecting to database $SqlDB on $SqlServer" 1 "Information"
- $Conn.Open()
- try{
- DeclineExpiredUpdates $Conn
- DeclineSupersededUpdates $Conn
- DeleteObsoleteUpdates $Conn
- DeleteInactiveComputers $Conn
- RestartWsusService
- if ( ! $SkipFileCleanup ) {
- DeleteUnusedContent
- }
- }
- finally{
- $Conn.Close()
- }
- }
- catch{
- $errorCount++
- log "Unhandled exception:`n$_" 100 "Error"
- }
- $time_exec = ( Get-Date ) - $timeExecStart
- log "Completed script execution with $errorCount error(s)`nExecution time $([math]::Round($time_exec.TotalHours)) hours and $([math]::Round($time_exec.totalMinutes)) minutes." 1 "Information"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement