· 6 years ago · Oct 03, 2019, 10:16 PM
1#Requires –Version 3.0
2################################
3# WSUSClean Clean-WSUS #
4# Version 2.11.1 #
5# #
6# The free WSUS Script #
7# you want! #
8# #
9# Taken from various sources #
10# from the Internet. #
11# #
12# Modified By: The Internet #
13################################
14<#
15################################
16# Prerequisites #
17################################
18
191. This script has to be saved as plain text in ANSI format. If you use Notepad++, you must
20 change the encoding to ANSI (Encoding > 'Encode in ANSI' or Encode > 'Convert to ANSI').
21 An easy way to tell if it is saved in plain text (ANSI) format is that there is a #Requires
22 statement at the top of the script. Make sure that there is a hyphen before the word
23 "Version" and you shouldn't have a problem with executing it. If you end up with an error
24 like below, it is due to the encoding of the file as you can tell by the – characters
25 before the word Version.
26
27 At C:\Scripts\Clean-WSUS.ps1:1 char:13
28 + #Requires –Version 3.0
29
302. You must run this on the WSUS Server itself and any downstream WSUS servers you may have.
31
323. On the WSUS Server, you must install the SQL Server Management Studio (SSMS) from Microsoft
33 so that you have the SQLCMD utility. The SSMS is not a requirement but rather a good tool for
34 troubleshooting if needed. The bare minimum requirement is the Microsoft Command Line
35 Utilities for SQL Server at whatever version yours is.
36
374. You must have Powershell 3.0 or higher installed. I recommend version 4.0 or higher.
38
39 Prerequesite Downloads
40 ----------------------
41
42 - For Server 2008 SP2:
43 - Install Windows Powershell from Server Manager - Features
44 - Install .NET 3.5 SP1 from - https://www.microsoft.com/en-ca/download/details.aspx?id=25150
45 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
46 You want to choose SQLManagementStudio_x64_ENU.exe
47 - Install .NET 4.0 - https://www.microsoft.com/en-us/download/details.aspx?id=17718
48 - Install Powershell 2.0 & WinRM 2.0 from https://www.microsoft.com/en-ca/download/details.aspx?id=20430
49 - Install Windows Management Framework 3.0 from https://www.microsoft.com/en-us/download/confirmation.aspx?id=34595
50
51 - For Server 2008 R2:
52 - Install .NET 4.5.2 from https://www.microsoft.com/en-ca/download/details.aspx?id=42642
53 - Install Windows Management Framework 4.0 and reboot from https://www.microsoft.com/en-ca/download/details.aspx?id=40855
54 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
55 You want to choose SQLManagementStudio_x64_ENU.exe
56
57 - For SBS:
58 - This script WILL work on SBS - you must install the pre-requisites above depending on your underlying OS. .NET 4 is
59 backwards compatible and I have a lot of users who have installed it on SBS and use the script.
60
61 - For Server 2012 & 2012 R2
62 - Install SQL Server Management Studio from https://www.microsoft.com/en-us/download/details.aspx?id=29062
63 You want to choose the ENU\x64\SQLManagementStudio_x64_ENU.exe
64
65 - For Server 2016
66 - I've not personally tested this on server 2016, however many people have run it without issues on Server 2016.
67 I don't think Microsoft has changed much between 2012 R2 WSUS and 2016 WSUS.
68 - Install SQL Server Management Studio from https://msdn.microsoft.com/library/mt238290.aspx
69
70 IF YOU DON'T WANT TO INSTALL SQL SERVER MANAGEMENT STUDIO:
71 Microsoft Command Line Utilities for SQL Server (Minimum requirement instead of SQL Server Management Studio)
72 SQL 2008/2008R2 - https://www.microsoft.com/en-ca/download/details.aspx?id=16978
73 SQL 2012/2014 - Version 11 - https://www.microsoft.com/en-us/download/details.aspx?id=36433
74 SQL 2016 - Version 13 - https://www.microsoft.com/en-us/download/details.aspx?id=53591
75
76################################
77# Instructions #
78################################
79
80 1. Edit the variables below to match your environment.
81 2. Open PowerShell using "Run As Administrator" on the WSUS Server.
82 3. Because you downloaded this script from the internet, you cannot initially run it directly
83 as the ExecutionPolicy is default set to "Restricted" (Server 2008, Server 2008 R2, and
84 Server 2012) or "RemoteSigned" (Server 2012 R2). You must change your ExecutionPolicy to
85 Bypass. You can do this with Set-ExecutionPolicy, however that will change it globally for
86 the server, which is not recommended. Instead, launch another PowerShell.exe with the
87 ExecutionPolicy set to bypass for just that session. At your current PowerShell prompt,
88 type in the following and then press enter:
89
90 PowerShell.exe -ExecutionPolicy Bypass
91
92 3. Run the script using -FirstRun.
93
94 .\Clean-WSUS.ps1 -FirstRun
95
96 4. Run the script using -InstallTask to install the Scheduled Task.
97
98 .\Clean-WSUS.ps1 -InstallTask
99
100You can use Get-Help .\Clean-WSUS.ps1 for more information.
101#>
102
103<#
104.SYNOPSIS
105This is the last WSUS Script you will ever need. It cleans up WSUS and runs all the maintenance scripts to keep WSUS running at peak performance.
106
107.DESCRIPTION
108################################
109# Background Information #
110# on Streams #
111################################
112
113All my recommendations are set in -ScheduledRun.
114
115WSUSClean Remove WSUS Drivers Stream
116-----------------------------------------------------
117
118This stream will remove all WSUS Drivers Classifications from the WSUS database.
119This has 2 possible running methods - Run through PowerShell, or Run directly in SQL.
120The -FirstRun Switch will force the SQL method, but all other automatic runs will use the
121PowerShell method. I recommend this be done every quarter.
122
123You can use -RemoveWSUSDriversSQL or -RemoveWSUSDriversPS to run these manually from the command-line.
124
125WSUSClean Remove Declined WSUS Updates Stream
126-----------------------------------------------------
127
128This stream will remove any Declined WSUS updates from the WSUS Database. This is good if you are removing
129Specific products (Like Server 2003 / Windows XP updates) from the WSUS server under the Products and
130Classifications section. Since this will remove them from the database, if they are still valid,
131the next synchronizations will pick up the updates again. I recommend that this be done every quarter.
132This stream is NOT included on -FirstRun on purpose.
133
134You can use -RemoveDeclinedWSUSUpdates to run this manually from the command-line.
135
136WSUSClean Compress Update Revisions Stream
137-----------------------------------------------------
138
139This stream will use SQL code to execute pre-existing stored procedures that will return the update id
140of each update revision that needs compressing and then compress it. I recommend that this be done
141monthly.
142
143You can use -CompressUpdateRevisions to run this manually from the command-line.
144
145WSUSClean Remove Obsolete Updates Stream
146-----------------------------------------------------
147
148This stream will use SQL code to execute pre-existing stored procedures that will return the update id
149of each obsolete update in the database and then remove it. There is no magic number of obsolete updates
150that will cause the server to time-out. Running this stream can easily take several hours to delete the
151updates. While the process is running you might see WSUS synchronization errors. I recommend that this
152be done monthly.
153
154You can use -RemoveObsoleteUpdates to run this manually from the command-line.
155
156WSUSClean WSUS Database Maintenance Stream
157-----------------------------------------------------
158
159This stream will perform basic maintenance tasks on SUSDB, the WSUS Database. It will identify indexes
160that are fragmented and defragment them. For certain tables, a fill-factor is set in order to improve
161insert performance. It will then update potentially out-of-date table statistics. I recommend that this
162be done daily.
163
164You can use -WSUSDBMaintenance to run this manually from the command-line.
165
166WSUSClean Decline Superseded Updates Stream
167-----------------------------------------------------
168
169This stream will decline any update that is superseded and not yet declined. This is a BONUS cleanup for
170shrinking down the size of your WSUS Server. Any update that has been superseded but has not been declined
171is using extra space. This will save you GB of data in your WsusContent folder. I recommend that this be
172done every month.
173
174You can use -DeclineSupersededUpdates to run this manually from the command-line.
175
176### Please read the background information below for more details. ###
177
178The Server Cleanup Wizard (SCW) declines superseded updates, only if:
179
180 The newest update is approved, and
181 The superseded updates are Not Approved, and
182 The superseded update has not been reported as NotInstalled (i.e. Needed) by any computer in the previous 30 days.
183
184There is no feature in the product to automatically decline superseded updates on approval of the newer update,
185and in fact, you really do not want that feature. The "Best Practice" in dealing with this situation is:
186
1871. Approve the newer update.
1882. Verify that all systems have installed the newer update.
1893. Verify that all systems now report the superseded update as Not Applicable.
1904. THEN it is safe to decline the superseded update.
191
192To SEARCH for superseded updates, you need only enable the Superseded flag column in the All Updates view, and sort on that column.
193
194There will be four groups:
195
1961. Updates which have never been superseded (blank icon).
1972. Updates which have been superseded, but have never superseded another update (icon with blue square at bottom).
1983. Updates which have been superseded and have superseded another update (icon with blue square in middle).
1994. Updates which have superseded another update (icon with blue square at top).
200
201There's no way to filter based on the approval status of the updates in group #4, but if you've verified that all
202necessary/applicable updates in group #4 are approved and installed, then you'd be free to decline groups #2 and #3 en masse.
203
204If you decline superseded updates using the method described:
205
2061. Approve the newer update.
2072. Verify that all systems have installed the newer update.
2083. Verify that all systems now report the superseded update as Not Applicable.
2094. THEN it is safe to decline the superseded update.
210
211### THIS SCRIPT DOES NOT FOLLOW THE ABOVE GUIDELINES. IT WILL JUST DECLINE ANY SUPERSEDED UPDATES. ###
212
213WSUSClean Clean Up WSUS Synchronization Logs Stream
214-----------------------------------------------------
215
216This stream will remove all synchronization logs beyond a specified time period. WSUS is lacking the ability
217to remove synchronization logs through the GUI. Your WSUS server will become slower and slower loading up
218the synchronization logs view as the synchronization logs will just keep piling up over time. If you have
219your synchronization settings set to synchronize 4 times a day, it would take less than 3 months before you
220have over 300 logs that it has to load for the view. This is very time consuming and many just ignore this
221view and rarely go to it. When they accidentally click on it, they curse. I recommend that this be done daily.
222
223You can use -CleanUpWSUSSynchronizationLogs to run this manually from the command-line.
224
225WSUSClean Computer Object Cleanup Stream
226-----------------------------------------------------
227
228This stream will find all computers that have not syncronized with the server within a certain time period
229and remove them. This is usually done through the Server Cleanup Wizard (SCW), however the SCW has been
230hardcoded to 30 days. I've setup this stream to be configurable. You can also tell it not to delete any
231computer objects if you really want to. The default I've kept at 30 days. I recommend that this be done daily.
232
233You can use -ComputerObjectCleanup to run this manually from the command-line.
234
235WSUSClean Server Cleanup Wizard Stream
236-----------------------------------------------------
237
238The Server Cleanup Wizard (SCW) is integrated into the WSUS GUI, and can be used to help you manage your
239disk space. This runs the SCW through PowerShell which has the added bonus of not timing out as often
240the SCW GUI would.
241
242This wizard can do the following things:
243 - Remove unused updates and update revisions
244 The wizard will remove all older updates and update revisions that have not been approved.
245
246 - Delete computers not contacting the server
247 The wizard will delete all client computers that have not contacted the server in thirty days or more.
248
249 - Delete unneeded update files
250 The wizard will delete all update files that are not needed by updates or by downstream servers.
251
252 - Decline expired updates
253 The wizard will decline all updates that have been expired by Microsoft.
254
255 - Decline superseded updates
256 The wizard will decline all updates that meet all the following criteria:
257 The superseded update is not mandatory
258 The superseded update has been on the server for thirty days or more
259 The superseded update is not currently reported as needed by any client
260 The superseded update has not been explicitly deployed to a computer group for ninety days or more
261 The superseding update must be approved for install to a computer group
262
263I recommend that this be done daily.
264
265You can use -WSUSServerCleanupWizard to run this manually from the command-line.
266
267WSUSClean Application Pool Memory Configuration Stream
268-----------------------------------------------------
269Why does the WSUS Application pool crash and how can we fix it? The WSUS Application pool has a
270"private memory limit" setting that is configured by default to a low number based on RAM. The
271Application pool crashes because it can't keep up and the limit is reached. So why couldn't the WSUS
272Application pool keep up? This has to do with the larger number of updates in the Update Catalog
273(database) which continues to grow over time. WSUS does not handle an excessive number of updates well
274and as as the number increases, the load on the application pool increases causing it to slowly run out
275of memory until the limit is hit and WSUS crashes. I've seen it start having issues above the low
276number of 10,000 updates and above the high number of 100,000 updates. The number of updates can in
277part be due to obsolete updates that remain in the database and it varies in every system and
278implementation. In order to help alleviate this, we can increase the memory on the WSUS Application Pool.
279
280I recommend that this be done manually, only if necessary, by the command-line.
281
282-DisplayApplicationPoolMemory to display the current application pool memory.
283-IncreaseApplicationPoolMemory <number in MB> to increase the current private memory limit by the number specified.
284
285.NOTES
286Name: Clean-WSUS
287Author: The Internet
288
289This script has been tested on Server 2008 SP2, Server 2008 R2, Server 2012, and Server 2012 R2. This script should run
290fine on Server 2016 and others have ran it with success on 2016, but I have not had the ability to test it in production.
291
292################################
293# Version History & #
294# Release Notes #
295################################
296
297 Version 2.07 to 2.08 (Not Released)
298 - Re-adjusted all Write-Host to Write-Output.
299 - Changed $WSUSCleanScriptPath from split-path -parent $MyInvocation.MyCommand.Definition to Split-Path $script:MyInvocation.MyCommand.Path.
300 - Changed $VerbosePreference to "Continue" when executing -HelpMe.
301 - Added Begin, Process, End operators.
302 - Setup -HelpMe to change VerbosePreference to continue and change it back at the end to what it was before.
303 - Changed Test-Administrator to streamline the process.
304 - Changed SQL-Ping-Instance to Test-SQLConnection and cleaned up the code.
305 - Added some VERBOSE output throughout the script.
306 - Fixed a bug with $ExceptionError in the RemoveDeclinedWSUSUpdatesProceed function.
307 - Adjusted the prerequesites and added SQL Cmd line tools requirement with links.
308 - Fixed a bug with RemoveDeclinedWSUSUpdates, thanks to Nikolay Semov (Nikolay8159) from the Spiceworks forums.
309 - Added regions to each section for ease of modification and readability in PowerShell ISE and other editors.
310
311 Version 2.08 to 2.09 (Not Released)
312 - Added CompressUpdateRevisions and RemoveObsoleteUpdates SQL Scripts as MonthlyRun items along with FirstRun.
313 - Added Configuration for Mail Report and Save Report options.
314 - Added Donation links.
315 - Fixed bug with running script from a folder with a space.
316
317 Version 2.09 to 2.10 (Not Released)
318 - Added the WSUSClean Application Pool Memory Configuration Stream.
319 - Added information about Server 2016 at the prerequisites stage.
320 - Added Start-Transcript to -HelpMe stream as now everything is outputted to objects, not just Write-Host.
321 - Added Show-MyFunctions and Show-MyVariables and added them to the -HelpMe output instead of the contstraint to just WSUSClean Variables.
322 - Removed the Clean Up Variables section at the end. Once the script finishes running all variables within the script are destroyed
323 as none are set globally.
324 - Changed $WSUSCleanWSUSServer to auto-populate vs manual entry, along with changing it to lowercase within the script.
325 - Added comments for Gmail settings.
326 - Added comments in the SQL Server Variable section for the auto-detect issue on Server 2008 (R2).
327 - Added an SBS Section to the prerequisites.
328 - Created a function for Connect-WSUSServer.
329 - Created a function for -InstallTask and a check for powershell version 4 or higher within. Adjusted the Instructions at the top.
330
331 Version 2.10 to 2.11
332 - Re-organized Run switches.
333 - Added a Restart of the Application pool if you increase the application pool to make sure the settings take effect.
334 - Added logic to not connect to the WSUS server if not actually running something that requires it (Display & Increase the
335 application pool and InstallTask).
336 - Added a configuration value for $WSUSCleanScheduledTaskTime.
337 - Added a ComputerObjectCleanup Stream for more control over computer object removals.
338 - Cleaned up old commented code and spacing.
339 - Added and changed some commented code to verbose output.
340 - Removed all the version history except what is different from the last released version and put it on my website instead.
341
342.EXAMPLE
343Clean-WSUS -FirstRun
344Description: Run the routines that are recommended for running this script for the first time.
345
346.EXAMPLE
347Clean-WSUS -InstallTask
348Description: Install the Scheduled task to run this script at 8AM daily with the -ScheduledRun switch.
349
350.EXAMPLE
351Clean-WSUS -HelpMe
352Description: Run the HelpMe stream to create a transcript of the session and provide troubleshooting information in a log file.
353
354.EXAMPLE
355Clean-WSUS -DisplayApplicationPoolMemory
356Description: Display the current Private Memory Limit for the WSUS Application Pool
357
358.EXAMPLE
359Clean-WSUS -IncreaseApplicationPoolMemory 2048
360Description: Increase the current Private Memory Limit for the WSUS Application Pool by 2048 MB (2GB)
361
362.EXAMPLE
363Clean-WSUS -DailyRun
364Description: Run the recommended daily routines.
365
366.EXAMPLE
367Clean-WSUS -MonthlyRun
368Description: Run the recommended monthly routines.
369
370.EXAMPLE
371Clean-WSUS -QuarterlyRun
372Description: Run the recommended quarterly routines.
373
374.EXAMPLE
375Clean-WSUS -ScheduledRun
376Description: Run the recommended routines on a schedule having the script take care of all timetables.
377
378.EXAMPLE
379Clean-WSUS -RemoveWSUSDriversSQL -SaveReport TXT
380Description: Only Remove WSUS Drivers by way of SQL and save the output as TXT to the script's folder named with the date and time of execution.
381
382.EXAMPLE
383Clean-WSUS -RemoveWSUSDriversPS -MailReport HTML
384Description: Only Remove WSUS Drivers by way of PowerShell and email the output as HTML to the configured parties.
385
386.EXAMPLE
387Clean-WSUS -RemoveDeclinedWSUSUpdates -CleanUpWSUSSynchronizationLogs -WSUSDBMaintenance -WSUSServerCleanupWizard -SaveReport HTML -MailReport TXT
388Description: Remove Declined WSUS Updates, Clean Up WSUS Synchronization Logs based on the configuration variables, Run the SQL Maintenance, and run the Server Cleanup Wizard (SCW) and output to an HTML file in the scripts folder named with the date and time of execution, and then email the report in plain text to the configured parties.
389
390.EXAMPLE
391Clean-WSUS -DeclineSupersededUpdates -ComputerObjectCleanup -SaveReport TXT -MailReport HTML
392Description: Decline superseded updates, computer object cleanup, save the output as TXT to the script's folder, and email the output as HTML to the configured parties.
393
394.EXAMPLE
395Clean-WSUS -RemoveObsoleteUpdates -CompressUpdateRevisions -DeclineSupersededUpdates -SaveReport TXT -MailReport HTML
396Description: Remove Obsolte Updates, Compress Update Revisions, Decline superseded updates, save the output as TXT to the script's folder, and email the output as HTML to the configured parties.
397
398#>
399################################
400# Script Setup Parameters #
401# #
402# DO NOT EDIT!!! SCROLL DOWN #
403# TO FIND THE VARIABLES #
404# TO EDIT #
405################################
406[CmdletBinding()]
407param (
408 # Run the routines that are recommended for running this script for the first time.
409 [Switch]$FirstRun,
410 # Install the Scheduled Task for daily @ 8AM.
411 [Switch]$InstallTask,
412 # Run the troubleshooting HelpMe stream to copy and paste for getting support.
413 [Switch]$HelpMe,
414 # Display the Application Pool Memory Limit
415 [switch]$DisplayApplicationPoolMemory,
416 # Increase or display the Application Pool Memory Limit.
417 [ValidateRange([int]::MinValue,[int]::MaxValue)]
418 [Int16]$IncreaseApplicationPoolMemory,
419 # Run the recommended daily routines.
420 [Switch]$DailyRun,
421 # Run the recommended monthly routines.
422 [Switch]$MonthlyRun,
423 # Run the recommended quarterly routines.
424 [Switch]$QuarterlyRun,
425 # Run the recommended routines on a schedule having the script take care of all timetables.
426 [Switch]$ScheduledRun,
427 # Remove WSUS Drivers by way of SQL.
428 [Switch]$RemoveWSUSDriversSQL,
429 # Remove WSUS Drivers by way of PowerShell.
430 [Switch]$RemoveWSUSDriversPS,
431 # Compress Update Revisions by way of SQL.
432 [Switch]$CompressUpdateRevisions,
433 # Remove Obsolete Updates by way of SQL.
434 [Switch]$RemoveObsoleteUpdates,
435 # Remove Declined WSUS Updates.
436 [Switch]$RemoveDeclinedWSUSUpdates,
437 # Decline Superseded Updates.
438 [Switch]$DeclineSupersededUpdates,
439 # Clean Up WSUS Synchronization Logs based on the configuration variables.
440 [Switch]$CleanUpWSUSSynchronizationLogs,
441 # Clean Up WSUS Synchronization Logs based on the configuration variables.
442 [Switch]$ComputerObjectCleanup,
443 # Run the SQL Maintenance.
444 [Switch]$WSUSDBMaintenance,
445 # Run the Server Cleanup Wizard (SCW) through PowerShell rather than through a GUI.
446 [Switch]$WSUSServerCleanupWizard,
447 # Save the output report to a file named the date and time of execute in the script's folder. TXT or HTML are valid output types.
448 [ValidateSet(“TXT”,”HTML”)]
449 [String]$SaveReport,
450 # Email the output report to an email address based on the configuration variables. TXT or HTML are valid output types.
451 [ValidateSet(“TXT”,”HTML”)]
452 [String]$MailReport
453 )
454Begin {
455$WSUSCleanCurrentSystemFunctions = Get-ChildItem function:
456$WSUSCleanCurrentSystemVariables = Get-Variable
457if (-not $DailyRun -and -not $FirstRun -and -not $MonthlyRun -and -not $QuarterlyRun -and -not $ScheduledRun -and -not $HelpMe -and -not $InstallTask) {
458 Write-Verbose "Not using a pre-defined routine"
459 if (-not ($DisplayApplicationPoolMemory -or $IncreaseApplicationPoolMemory)) {
460 Write-Verbose "Not using a using the Application Pool commands or the InstallTask"
461 if ($SaveReport -eq '' -and $MailReport -eq '') {
462 Throw "You must use -SaveReport or -MailReport if you are not going to use the pre-defined routines (-FirstRun, -DailyRun, -MonthlyRun, -QuarterlyRun, -ScheduledRun) or the individual switches -HelpMe -DisplayApplicationPoolMemory and -IncreaseApplicationPoolMemory."
463 } else { Write-Verbose "SaveReport or MailReport have been specified. Continuing on." }
464 } else { Write-Verbose "`$DisplayApplicationPoolMemory -or `$IncreaseApplicationPoolMemory were specified." }
465}
466if ($HelpMe -eq $True) { $WSUSCleanOldVerbose = $VerbosePreference; $VerbosePreference = "continue"; Start-Transcript -Path "$(get-date -f "yyyy.MM.dd-HH.mm.ss")-HelpMe.txt" }
467
468#region Configuration Variables
469################################
470# WSUS Setup Variables #
471################################
472
473# Enter your FQDN of the WSUS server. Example: "$((Get-WmiObject win32_computersystem).DNSHostName).$((Get-WmiObject win32_computersystem).Domain)" or "$((Get-WmiObject win32_computersystem).DNSHostName)" or "server.domain.local"
474# WSUS does not play well with Aliases or CNAMEs and requires using the FQDN or the HostName in most cases.
475[string]$WSUSCleanWSUSServer = "$((Get-WmiObject win32_computersystem).DNSHostName).$((Get-WmiObject win32_computersystem).Domain)" # This should not be changed unless this doesn't work.
476
477# Use secure connection: $True or $False
478[boolean]$WSUSCleanWSUSServerUseSecureConnection = $True
479
480# What port number are you using for WSUS? Example: "80" or "443" if on Server 2008 or "8530" or "8531" if on Server 2012+
481[int32]$WSUSCleanWSUSServerPortNumber = "8531"
482
483################################
484# Mail Report Setup Variables #
485################################
486
487# From: address for email notifications (it doesn't have to be a real email address, but if you're sending through Gmail it must be
488# your Gmail address). Example: "WSUS@domain.com" or "email@gmail.com"
489[string]$WSUSCleanMailReportEmailFromAddress = "no-reply@example.com"
490
491# To: address for email notifications. Example: "firstname.lastname@domain.com"
492[string]$WSUSCleanMailReportEmailToAddress = "test@example.com"
493
494# Subject: of the results email
495[string]$WSUSCleanMailReportEmailSubject = "WSUS Cleanup Results"
496
497# Enter your SMTP server name. Example: "mailserver.domain.local" or "mail.domain.com" or "smtp.gmail.com"
498[string]$WSUSCleanMailReportSMTPServer = "localhost"
499
500# Enter your SMTP port number. Example: "25" or "465" (Usually for SSL) or "587" or "1025"
501[int32]$WSUSCleanMailReportSMTPPort = "25"
502
503# Do you want to enable SSL communication for your SMTP Server
504[boolean]$WSUSCleanMailReportSMTPServerEnableSSL = $False
505
506# Do you need to authenticate to the server? If not, leave blank.
507[string]$WSUSCleanMailReportSMTPServerUsername = ""
508[string]$WSUSCleanMailReportSMTPServerPassword = ""
509
510#Note Gmail Settings: smtp.gmail.com Port:587 SSL:Enabled User:user@gmail.com Password (if you use 2FA, make an app password).
511
512################################
513# Mail Report or Save Report #
514################################
515
516# Do you want to enable the Mail Report for every run?
517[boolean]$WSUSCleanMailReport = $True
518
519# Do you want the mailed report to be in HTML or plain text? (Valid options are "HTML" or "TXT")
520[string]$WSUSCleanMailReportType = "HTML"
521
522# Do you want to enable the save report for every run? (-FirstRun will save the report regardless)
523[boolean]$WSUSCleanSaveReport = $True
524
525# Do you want the saved report to be outputted in HTML or plain text? (Valid options are "HTML" or "TXT")
526[string]$WSUSCleanSaveReportType = "TXT"
527
528
529################################
530# WSUS Server Cleanup Wizard #
531# Parameters #
532# Set to $True or $False #
533################################
534
535# Decline updates that have not been approved for 30 days or more, are not currently needed by any clients, and are superseded by an approved update.
536[boolean]$WSUSCleanSCWSupersededUpdatesDeclined = $True
537
538# Decline updates that aren't approved and have been expired my Microsoft.
539[boolean]$WSUSCleanSCWExpiredUpdatesDeclined = $True
540
541# Delete updates that are expired and have not been approved for 30 days or more.
542[boolean]$WSUSCleanSCWObsoleteUpdatesDeleted = $True
543
544# Delete older update revisions that have not been approved for 30 days or more.
545[boolean]$WSUSCleanSCWUpdatesCompressed = $True
546
547# Delete computers that have not contacted the server in 30 days or more. Default: $False
548# This is taken care of by the Computer Object Cleanup Stream
549[boolean]$WSUSCleanSCWObsoleteComputersDeleted = $False
550
551# Delete update files that aren't needed by updates or downstream servers.
552[boolean]$WSUSCleanSCWUnneededContentFiles = $True
553
554################################
555# Computer Object Cleanup #
556# Variables #
557################################
558
559# Do you want to remove the computer objects from WSUS that have not synchronized in days?
560# This is good to keep your WSUS clean of previously removed computers.
561[boolean]$WSUSCleanComputerObjectCleanup = $False
562
563# If the above is set to $True, how many days of no synchronization do you want to remove
564# computer objects from the WSUS Server? Set this to 0 to remove all computer objects.
565[int]$WSUSCleanComputerObjectCleanupSearchDays = "30"
566
567################################
568# Scheduled Run Variables #
569################################
570
571# On what day do you wish to run the MonthlyRun and QuarterlyRun Stream? I recommend on the 1st-7th of the month.
572# This will give enough time for you to approve (if you approve manually) and your computers to receive the
573# superseding updates after patch Tuesday (second Tuesday of the month).
574# (Valid days are 1-31. February, April, June, September, and November have logic to set to the last day
575# of the month if this is set to a number greater than the amount of days in that month, including leap years.)
576[int]$WSUSCleanScheduledRunStreamsDay = "1"
577
578# What months would you like to run the QuarterlyRun Stream?
579# (Valid months are 1-12, comma separated for multiple months)
580[string]$WSUSCleanScheduledRunQuarterlyMonths = "1,4,7,10"
581
582# What time daily do you want to run the script using the scheduled task?
583[string]$WSUSCleanScheduledTaskTime = "5:00am"
584
585################################
586# Clean Up WSUS #
587# Synchronization Logs #
588# Variables #
589################################
590
591# Clean up the synchronization logs older than a consistency.
592
593# (Valid consistency number are whole numbers.)
594[int]$WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyNumber = "14"
595
596# Valid consistency time are "Day" or "Month"
597[String]$WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyTime = "Day"
598
599# Or remove all synchronization logs each time
600[boolean]$WSUSCleanCleanUpWSUSSynchronizationLogsAll = $False
601
602################################
603# SQL Server Variable #
604################################
605
606# ONLY uncomment and fill out if you are using a dedicated SQL Instance or if the script tells you to
607# otherwise leave this commented for auto-detection of the proper SQL Instance for the Windows Internal Database.
608# Example: "SERVER\INSTANCE" or "SERVER" (if using the Default Instance)
609
610# If you are using a Remote SQL connection, you will need to set the Scheduled Task to use the computer account
611# as the user that runs the script (Instead of searching for it, you must type it in the format of: DOMAIN\COMPUTER$)
612# or run the Scheduled Task as a user account saving credentials so that it can pass them through to the SQL Server.
613
614# If you are having issues with the auto-dection on Server 2008 (R2), it may be due to the timeout for testing of the
615# connection to the server. In this case, please specify this as 'np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query' as this
616# will increase the timeout to 60 seconds and it should connect within that time.
617
618[string]$WSUSCleanSQLServer = 'LOCALHOST'
619
620################################
621# Do not edit below this line #
622################################
623}
624#endregion
625
626Process {
627$WSUSCleanScriptTime = Get-Date
628$WSUSCleanWSUSServer = $WSUSCleanWSUSServer.ToLower()
629Write-verbose "Set the script's current working directory path"
630$WSUSCleanScriptPath = Split-Path $script:MyInvocation.MyCommand.Path
631Write-Verbose "`$WSUSCleanScriptPath = $WSUSCleanScriptPath"
632
633#region Test Elevation
634function Test-Administrator
635{
636 $CurrentUser = [Security.Principal.WindowsIdentity]::GetCurrent();
637 (New-Object Security.Principal.WindowsPrincipal $CurrentUser).IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator)
638}
639Write-Verbose "Testing to see if you are running this from an Elevated PowerShell Prompt."
640if ((Test-Administrator) -ne $True) {
641 Throw "ERROR: You must run this from an Elevated PowerShell Prompt on each WSUS Server in your environment. If this is done through scheduled tasks, you must check the box `"Run with the highest privileges`""
642}
643else {
644 Write-Verbose "Done. You are running this from an Elevated PowerShell Prompt"
645}
646#endregion Test Elevation
647
648if ($HelpMe -eq $True) {
649 $Script:HelpMeHeader = @"
650=============================
651 Clean-WSUS HelpMe Stream
652=============================
653
654This is the HelpMe Section for troubleshooting
655Please provide this information to get support
656
657
658
659"@
660$Script:HelpMeHeader
661}
662
663#region Test SQLConnection
664function Test-SQLConnection
665{
666 param (
667 [parameter(Mandatory = $true)][string] $ServerInstance,
668 [parameter(Mandatory = $false)][int] $TimeOut = 1
669 )
670
671 $SqlConnectionResult = $false
672
673 try
674 {
675 $SqlCatalog = "SUSDB"
676 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
677 $SqlConnection.ConnectionString = "Server = $ServerInstance; Database = $SqlCatalog; Integrated Security = True; Connection Timeout=$TimeOut"
678 $TimeOutVerbage = if ($TimeOut -gt "1") { "seconds" } else { "second" }
679 Write-Verbose "Initiating SQL Connection Testing to $ServerInstance with a timeout of $TimeOut $TimeOutVerbage"
680 $SqlConnection.Open()
681 Write-Verbose "Connected. Setting `$SqlConnectionResult to $($SqlConnection.State -eq "Open")"
682 $SqlConnectionResult = $SqlConnection.State -eq "Open"
683 }
684
685 catch
686 {
687 Write-Output "Connection Failed."
688 }
689
690 finally
691 {
692 $SqlConnection.Close()
693 }
694
695 return $SqlConnectionResult
696}
697
698[string]$WSUSCleanWID2008 = 'np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query'
699[string]$WSUSCleanWID2012 = 'np:\\.\pipe\MICROSOFT##WID\tsql\query'
700if (-not [string]::isnullorempty($WSUSCleanSQLServer)) {
701 Write-Verbose "Test to see if $WSUSCleanSQLServer is set and if it is, with something other than blank. Then test to see if it can connect."
702 if ((Test-SQLConnection $WSUSCleanSQLServer 60) -eq $False) {
703 # If it doesn't work, terminate the script erroring out with a reason.
704 Throw "I've tested the server `"$WSUSCleanSQLServer`" in the configuration but can't connect to that SQL Server Instance. Please check the spelling again. Don't forget to specify the SQL Instance if there is one."
705 }
706} elseif ((Test-SQLConnection $WSUSCleanWID2008) -eq $true) {
707 Write-Verbose "Setting `$WSUSCleanSQLServer for server 2008 & 2008 R2 Windows Internal Database"
708 $WSUSCleanSQLServer = $WSUSCleanWID2008
709} elseif ((Test-SQLConnection $WSUSCleanWID2012) -eq $true) {
710 Write-Verbose "Setting `$WSUSCleanSQLServer for server 2012 & 2012 R2 Windows Internal Database"
711 $WSUSCleanSQLServer = $WSUSCleanWID2012
712} else {
713 if ($HelpMe -ne $True) {
714 #Terminate the script erroring out with a reason.
715 Throw "I can't determine the SQL Server Instance. Please find the `"`$WSUSCleanSQLServer`" variable in the configuration and set it as your SERVER\INSTANCE for WSUS"
716 }
717 else { Write-Output "I can't connect to SQL, and you've asked for help. Connecting to the WSUS Server to get troubleshooting information." }
718}
719
720#Create the connection command variable.
721$WSUSCleanSQLConnectCommand = "sqlcmd -S $WSUSCleanSQLServer"
722#endregion Test SQLConnection
723
724#region Connect to the WSUS Server
725function Connect-WSUSServer {
726 [CmdletBinding()]
727 param
728 (
729 [Parameter(Position=0, Mandatory = $True)]
730 [Alias("Server")]
731 [string]$WSUSServer,
732
733 [Parameter(Position=1, Mandatory = $True)]
734 [Alias("Port")]
735 [int]$WSUSPort,
736
737 [Parameter(Position=2, Mandatory = $True)]
738 [Alias("SSL")]
739 [boolean]$WSUSEnableSSL
740 )
741 Write-Verbose "Load .NET assembly"
742 [void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration");
743
744 Write-Verbose "Connect to WSUS Server: $WSUSServer"
745 $Script:WSUSAdminProxy = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer();
746 If ($? -eq $False) {
747 Throw "ERROR Connecting to the WSUS Server: $WSUSServer. Please check your settings and try again."
748 } else {
749 $Script:WSUSCleanConnectedTime = Get-Date
750 $Script:WSUSCleanConnectedTXT = "Connected to the WSUS server $WSUSCleanWSUSServer @ $($WSUSCleanConnectedTime.ToString(`"yyyy.MM.dd hh:mm:ss tt zzz`"))`r`n`r`n"
751 $Script:WSUSCleanConnectedHTML = "<i>Connected to the WSUS server $WSUSCleanWSUSServer @ $($WSUSCleanConnectedTime.ToString(`"yyyy.MM.dd hh:mm:ss tt zzz`"))</i>`r`n`r`n"
752 Write-Output "Connected to the WSUS server $WSUSCleanWSUSServer"
753 }
754}
755
756if (($InstallTask -or $DisplayApplicationPoolMemory -or $IncreaseApplicationPoolMemory) -eq $False) {
757 Connect-WSUSServer -Server $WSUSCleanWSUSServer -Port $WSUSCleanWSUSServerPortNumber -SSL $WSUSCleanWSUSServerUseSecureConnection
758 $WSUSCleanWSUSServerAdminProxy = $Script:WSUSAdminProxy
759}
760#endregion Connect to the WSUS Server
761
762#region Get-DiskFree Function
763################################
764# Get-DiskFree #
765################################
766
767function Get-DiskFree
768# Taken from http://binarynature.blogspot.ca/2010/04/powershell-version-of-df-command.html
769{
770 [CmdletBinding()]
771 param
772 (
773 [Parameter(Position=0,
774 ValueFromPipeline=$true,
775 ValueFromPipelineByPropertyName=$true)]
776 [Alias('hostname')]
777 [Alias('cn')]
778 [string[]]$ComputerName = $env:COMPUTERNAME,
779
780 [Parameter(Position=1,
781 Mandatory=$false)]
782 [Alias('runas')]
783 [System.Management.Automation.Credential()]$Credential =
784 [System.Management.Automation.PSCredential]::Empty,
785
786 [Parameter(Position=2)]
787 [switch]$Format
788 )
789
790 BEGIN
791 {
792 function Format-HumanReadable
793 {
794 param ($size)
795 switch ($size)
796 {
797 {$_ -ge 1PB}{"{0:#.#'P'}" -f ($size / 1PB); break}
798 {$_ -ge 1TB}{"{0:#.#'T'}" -f ($size / 1TB); break}
799 {$_ -ge 1GB}{"{0:#.#'G'}" -f ($size / 1GB); break}
800 {$_ -ge 1MB}{"{0:#.#'M'}" -f ($size / 1MB); break}
801 {$_ -ge 1KB}{"{0:#'K'}" -f ($size / 1KB); break}
802 default {"{0}" -f ($size) + "B"}
803 }
804 }
805 $wmiq = 'SELECT * FROM Win32_LogicalDisk WHERE Size != Null AND DriveType >= 2'
806 }
807
808 PROCESS
809 {
810 foreach ($computer in $ComputerName)
811 {
812 try
813 {
814 if ($computer -eq $env:COMPUTERNAME)
815 {
816 $disks = Get-WmiObject -Query $wmiq `
817 -ComputerName $computer -ErrorAction Stop
818 }
819 else
820 {
821 $disks = Get-WmiObject -Query $wmiq `
822 -ComputerName $computer -Credential $Credential `
823 -ErrorAction Stop
824 }
825
826 if ($Format)
827 {
828 # Create array for $disk objects and then populate
829 $diskarray = @()
830 $disks | ForEach-Object { $diskarray += $_ }
831
832 $diskarray | Select-Object @{n='Name';e={$_.SystemName}},
833 @{n='Vol';e={$_.DeviceID}},
834 @{n='Size';e={Format-HumanReadable $_.Size}},
835 @{n='Used';e={Format-HumanReadable `
836 (($_.Size)-($_.FreeSpace))}},
837 @{n='Avail';e={Format-HumanReadable $_.FreeSpace}},
838 @{n='Use%';e={[int](((($_.Size)-($_.FreeSpace))`
839 /($_.Size) * 100))}},
840 @{n='FS';e={$_.FileSystem}},
841 @{n='Type';e={$_.Description}}
842 }
843 else
844 {
845 foreach ($disk in $disks)
846 {
847 $diskprops = @{'Volume'=$disk.DeviceID;
848 'Size'=$disk.Size;
849 'Used'=($disk.Size - $disk.FreeSpace);
850 'Available'=$disk.FreeSpace;
851 'FileSystem'=$disk.FileSystem;
852 'Type'=$disk.Description
853 'Computer'=$disk.SystemName;}
854
855 # Create custom PS object and apply type
856 $diskobj = New-Object -TypeName PSObject `
857 -Property $diskprops
858 $diskobj.PSObject.TypeNames.Insert(0,'BinaryNature.DiskFree')
859
860 Write-Output $diskobj
861 }
862 }
863 }
864 catch
865 {
866 # Check for common DCOM errors and display "friendly" output
867 switch ($_)
868 {
869 { $_.Exception.ErrorCode -eq 0x800706ba } `
870 { $err = 'Unavailable (Host Offline or Firewall)';
871 break; }
872 { $_.CategoryInfo.Reason -eq 'UnauthorizedAccessException' } `
873 { $err = 'Access denied (Check User Permissions)';
874 break; }
875 default { $err = $_.Exception.Message }
876 }
877 Write-Warning "$computer - $err"
878 }
879 }
880 }
881
882 END {}
883}
884#endregion Get-DiskFree Function
885
886#region Setup The Header
887################################
888# Setup the Header #
889################################
890
891function CreateWSUSCleanHeader {
892$Script:WSUSCleanBodyHeaderTXT = @"
893################################
894# #
895# WSUSClean Clean-WSUS #
896# Version 2.11 #
897# #
898# The last WSUS Script you #
899# will ever need! #
900# #
901################################
902
903
904"@
905$Script:WSUSCleanBodyHeaderHTML = @"
906 <table style="height: 0px; width: 0px;" border="0">
907 <tbody>
908 <tr>
909 <td colspan="3">
910 <span
911 style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span>
912 </td>
913 </tr>
914 <tr>
915 <td style="text-align: left;">#</td>
916 <td style="text-align: center;"> </td>
917 <td style="text-align: right;">#</td>
918 </tr>
919 <tr>
920 <td style="text-align: left;">#</td>
921 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">WSUSClean Clean-WSUS</span></td>
922 <td style="text-align: right;">#</td>
923 </tr>
924 <tr>
925 <td style="text-align: left;">#</td>
926 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Version 2.11</span></td>
927 <td style="text-align: right;">#</td>
928 </tr>
929 <tr>
930 <td style="text-align: left;">#</td>
931 <td> </td>
932 <td style="text-align: right;">#</td>
933 </tr>
934 <tr>
935 <td style="text-align: left;">#</td>
936 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">The last WSUS Script you</span></td>
937 <td style="text-align: right;">#</td>
938 </tr>
939 <tr>
940 <td style="text-align: left;">#</td>
941 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">will ever need!</span></td>
942 <td style="text-align: right;">#</td>
943 </tr>
944 <tr>
945 <td style="text-align: left;">#</td>
946 <td> </td>
947 <td style="text-align: right;">#</td>
948 </tr>
949 <tr>
950 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
951 </tr>
952 </tbody>
953 </table>
954"@
955}
956#endregion Setup The Header
957
958#region Setup The Footer
959################################
960# Setup the Footer #
961################################
962
963function CreateWSUSCleanFooter {
964$Script:WSUSCleanBodyFooterTXT = @"
965
966################################
967# End of the WSUS Cleanup #
968################################
969
970"@
971$Script:WSUSCleanBodyFooterHTML = @"
972 <table style="height: 0px; width: 0px;" border="0">
973 <tbody>
974 <tr>
975 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
976 </tr>
977 <tr>
978 <td style="text-align: left;">#</td>
979 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">End of the WSUS Cleanup</span></td>
980 <td style="text-align: right;">#</td>
981 </tr>
982 <tr>
983 <td colspan="3" rowspan="1"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
984 </tr>
985 <tr>
986 <td style="text-align: left;">#</td>
987 <td style="text-align: center;"> </td>
988 <td style="text-align: right;">#</td>
989 </tr>
990 <tr>
991 <td style="text-align: left;">#</td>
992 <td> </td>
993 <td style="text-align: right;">#</td>
994 </tr>
995 <tr>
996 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
997 </tr>
998 </tbody>
999 </table>
1000"@
1001}
1002#endregion Setup The Footer
1003
1004#region Show-My Functions
1005################################
1006# Show-My Functions Stream #
1007################################
1008
1009function Show-MyFunctions { Get-ChildItem function: | Where-Object { $WSUSCleanCurrentSystemFunctions -notcontains $_ } | Format-Table -AutoSize -Property CommandType,Name }
1010function Show-MyVariables { Get-Variable | Where-Object { $WSUSCleanCurrentSystemVariables -notcontains $_ } | Format-Table }
1011#endregion Show-My Functions
1012
1013#region Install-Task Function
1014################################
1015# Install-Task Configuration #
1016################################
1017
1018Function Install-Task {
1019 $Windows = [PSCustomObject]@{
1020 Caption = (Get-WmiObject -Class Win32_OperatingSystem).Caption
1021 Version = [Environment]::OSVersion.Version
1022 }
1023 if ($Windows.Version.Major -gt "6") { Write-Verbose "$($Windows.Caption) - Use Win8 Compatibility" ; $Compatibility = "Win8" }
1024 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -ge "2" ) { Write-Verbose "$($Windows.Caption) - Use Win8 Compatibility" ; $Compatibility = "Win8" }
1025 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -eq "1" ) { Write-Verbose "$($Windows.Caption) - Use Win7 Compatibility" ; $Compatibility = "Win7" }
1026 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -eq "0" ) { Write-Verbose "$($Windows.Caption) - Use Vista Compatibility" ; $Compatibility = "Vista" }
1027
1028 $Trigger = New-ScheduledTaskTrigger -At $WSUSCleanScheduledTaskTime -Daily #Trigger the task daily at $WSUSCleanScheduledTaskTime
1029 $User = "$env:USERDOMAIN\$env:USERNAME"
1030 $Principal = New-ScheduledTaskPrincipal -UserID "$env:USERDOMAIN\$env:USERNAME" -LogonType S4U -RunLevel Highest
1031 $TaskName = "WSUSClean Clean-WSUS"
1032 $Description = "This task will run the WSUSClean Clean-WSUS script with the -ScheduledRun parameter which takes care of everything for you according to my recommendations."
1033 $Action = New-ScheduledTaskAction -Execute "$((Get-Command powershell.exe).Definition)" -Argument "-ExecutionPolicy Bypass `"$($script:MyInvocation.MyCommand.Path) -ScheduledRun`""
1034 $Settings = New-ScheduledTaskSettingsSet -Compatibility $Compatibility
1035 Write-Verbose "Register the Scheduled task."
1036 Register-ScheduledTask -TaskName $TaskName -Description $Description -Action $Action -Trigger $Trigger -Settings $Settings -Principal $Principal -Force
1037}
1038$PowerShellMajorVersion = $($PSVersionTable.PSVersion.Major)
1039Write-Verbose "`$InstallTask is $InstallTask"
1040if ($InstallTask -eq $True) {
1041 $Version = @{}
1042 $Version.Add("Major", ((Get-CimInstance Win32_OperatingSystem).Version).Split(".")[0])
1043 $Version.Add("Minor", ((Get-CimInstance Win32_OperatingSystem).Version).Split(".")[1])
1044 #$Version.Add("Major", "5")
1045 #$Version.Add("Minor", "3")
1046 if ([int]$Version.Get_Item("Major") -ge "7" -or ([int]$Version.Get_Item("Major") -ge "6" -and [int]$Version.Get_Item("Minor") -ge "2")) {
1047 Write-Verbose "YES - OS Version $([int]$Version.Get_Item("Major")).$([int]$Version.Get_Item("Minor"))"
1048 Install-Task
1049 } else {
1050 Write-Verbose "NO - OS Version $([int]$Version.Get_Item("Major")).$([int]$Version.Get_Item("Minor"))"
1051 Write-Output ""
1052 Write-Output "You are not using Windows Server 2012 or higher. You will have to manually create the Scheduled Task"
1053 Write-Output ""
1054 $WSUSCleanManuallyCreateTaskInstructions = @"
1055To Create a Scheduled Task:
1056
1057 1. Open Task Scheduler and Create a new task (not a basic task)
1058 2. Go to the General Tab:
1059 3. Name: "WSUSClean Clean-WSUS"
1060 4. Under the section "Security Options" put the dot in "Run whether the user is logged on or not"
1061 5. Check "Do not store password. The task will only have access to local computer resources"
1062 6. Check "Run with highest privileges."
1063 7. Under the section "Configure for" - Choose the OS of the Server (e.g. Server 2012 R2)
1064 8. Go to the Triggers Tab:
1065 9. Click New at the bottom left.
106610. Under the section "Settings"
106711. Choose Daily. Choose $WSUSCleanScheduledTaskTime
106812. Confirm Enabled is checked, Press OK.
106913. Go to the Actions Tab:
107014. Click New at the bottom left.
107115. Action should be "Start a program"
107216. The "Program/script" should be set to
1073
1074 $((Get-Command powershell.exe).Definition)
1075
107617. The arguments line should be set to
1077
1078 -ExecutionPolicy Bypass `"$($script:MyInvocation.MyCommand.Path) -ScheduledRun`"
1079
108018. Go to the Settings Tab:
108119. Check "Allow task to be run on demand"
108220. Click OK
1083"@
1084 Write-Output $WSUSCleanManuallyCreateTaskInstructions
1085 }
1086}
1087#endregion Install-Task Function
1088
1089#region ApplicationPoolMemory Function
1090################################
1091# Application Pool Memory #
1092# Configuration Stream #
1093################################
1094function ApplicationPoolMemory {
1095 Param(
1096 [ValidateRange([int]::MinValue,[int]::MaxValue)]
1097 [Int]$IncreaseApplicationPoolBy
1098 )
1099 $DateNow = Get-Date
1100 Import-Module WebAdministration
1101 $applicationPoolsPath = "/system.applicationHost/applicationPools"
1102 $applicationPools = Get-WebConfiguration $applicationPoolsPath
1103 foreach ($appPool in $applicationPools.Collection) {
1104 if ($appPool.name -eq 'WsusPool') {
1105 $appPoolPath = "$applicationPoolsPath/add[@name='$($appPool.Name)']"
1106 $CurrentPrivateMemory = (Get-WebConfiguration "$appPoolPath/recycling/periodicRestart/@privateMemory").Value
1107 Write-Output "Current Private Memory Limit for $($appPool.name) is: $($CurrentPrivateMemory/1000) MB"
1108 if ($IncreaseApplicationPoolBy) {
1109 $IncreaseApplicationPoolBy=$IncreaseApplicationPoolBy * 1000
1110 $NewPrivateMemory = $CurrentPrivateMemory + $IncreaseApplicationPoolBy
1111 Write-Output "New Private Memory Limit for $($appPool.name) is: $($NewPrivateMemory/1000) MB"
1112 Set-WebConfiguration "$appPoolPath/recycling/periodicRestart/@privateMemory" -Value $NewPrivateMemory
1113 Write-Verbose "Restart the $($appPool.name) Application Pool to make the new settings take effect"
1114 Restart-WebAppPool -Name $($appPool.name)
1115 }
1116 }
1117 }
1118 $FinishedRunning = Get-Date
1119 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1120 $Duration = "{0:00}:{1:00}:{2:00}:{3:00}:{4:00}" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds, $_.Milliseconds})
1121 Write-Verbose "Application Pool Memory Stream Duration: $Duration"
1122}
1123#endregion ApplicationPoolMemory Function
1124
1125#region RemoveWSUSDrivers Function
1126################################
1127# WSUSClean Remove WSUS Drivers #
1128# Stream #
1129################################
1130
1131function RemoveWSUSDrivers {
1132 param (
1133 [Parameter()]
1134 [Switch] $SQL
1135 )
1136 function RemoveWSUSDriversSQL {
1137 $WSUSCleanRemoveWSUSDriversSQLScript = @"
1138/*
1139################################
1140# WSUSClean Delete Drivers #
1141# SQL Script #
1142# Version 1.0 #
1143# Taken from various sources #
1144# from the Internet. #
1145################################
1146
1147-- Originally taken from http://www.flexecom.com/how-to-delete-driver-updates-from-wsus-3-0/
1148-- Modified to be dynamic and more of a nice output
1149*/
1150USE SUSDB;
1151GO
1152
1153SET NOCOUNT ON;
1154DECLARE @tbrevisionlanguage nvarchar(255)
1155DECLARE @tbProperty nvarchar(255)
1156DECLARE @tbLocalizedPropertyForRevision nvarchar(255)
1157DECLARE @tbFileForRevision nvarchar(255)
1158DECLARE @tbInstalledUpdateSufficientForPrerequisite nvarchar(255)
1159DECLARE @tbPreRequisite nvarchar(255)
1160DECLARE @tbDeployment nvarchar(255)
1161DECLARE @tbXml nvarchar(255)
1162DECLARE @tbPreComputedLocalizedProperty nvarchar(255)
1163DECLARE @tbDriver nvarchar(255)
1164DECLARE @tbFlattenedRevisionInCategory nvarchar(255)
1165DECLARE @tbRevisionInCategory nvarchar(255)
1166DECLARE @tbMoreInfoURLForRevision nvarchar(255)
1167DECLARE @tbRevision nvarchar(255)
1168DECLARE @tbUpdateSummaryForAllComputers nvarchar(255)
1169DECLARE @tbUpdate nvarchar(255)
1170DECLARE @var1 nvarchar(255)
1171
1172/*
1173This query gives you the GUID that you will need to substitute in all subsequent queries. In my case, it is
1174D2CB599A-FA9F-4AE9-B346-94AD54EE0629. I saw this GUID in several WSUS databases so I think it does not change;
1175at least not between WSUS 3.0 SP2 servers. Either way, we are setting a variable for this so this will
1176dynamically reference the correct GUID.
1177*/
1178
1179SELECT @var1 = UpdateTypeID FROM tbUpdateType WHERE Name = 'Driver'
1180
1181/*
1182The bad news is that WSUS database has over 100 tables. The good news is that SQL allows to enforce referential
1183integrity in data model designs, which in this case can be used to essentially reverse engineer a procedure,
1184that as far as I know isn’t documented anywhere.
1185
1186The trick is to delete all driver type records from tbUpdate table – but FIRST we have to delete all records in
1187all other tables (revisions, languages, dependencies, files, reports…), which refer to driver rows in tbUpdate.
1188
1189Here’s how this is done, in 16 tables/queries.
1190*/
1191
1192delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1193SELECT @tbrevisionlanguage = @@ROWCOUNT
1194PRINT 'Delete records from tbrevisionlanguage: ' + @tbrevisionlanguage
1195
1196delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1197SELECT @tbProperty = @@ROWCOUNT
1198PRINT 'Delete records from tbProperty: ' + @tbProperty
1199
1200delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1201SELECT @tbLocalizedPropertyForRevision = @@ROWCOUNT
1202PRINT 'Delete records from tbLocalizedPropertyForRevision: ' + @tbLocalizedPropertyForRevision
1203
1204delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1205SELECT @tbFileForRevision = @@ROWCOUNT
1206PRINT 'Delete records from tbFileForRevision: ' + @tbFileForRevision
1207
1208delete from tbInstalledUpdateSufficientForPrerequisite where prerequisiteid in (select Prerequisiteid from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)))
1209SELECT @tbInstalledUpdateSufficientForPrerequisite = @@ROWCOUNT
1210PRINT 'Delete records from tbInstalledUpdateSufficientForPrerequisite: ' + @tbInstalledUpdateSufficientForPrerequisite
1211
1212delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1213SELECT @tbPreRequisite = @@ROWCOUNT
1214PRINT 'Delete records from tbPreRequisite: ' + @tbPreRequisite
1215
1216delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1217SELECT @tbDeployment = @@ROWCOUNT
1218PRINT 'Delete records from tbDeployment: ' + @tbDeployment
1219
1220delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1221SELECT @tbXml = @@ROWCOUNT
1222PRINT 'Delete records from tbXml: ' + @tbXml
1223
1224delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1225SELECT @tbPreComputedLocalizedProperty = @@ROWCOUNT
1226PRINT 'Delete records from tbPreComputedLocalizedProperty: ' + @tbPreComputedLocalizedProperty
1227
1228delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1229SELECT @tbDriver = @@ROWCOUNT
1230PRINT 'Delete records from tbDriver: ' + @tbDriver
1231
1232delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1233SELECT @tbFlattenedRevisionInCategory = @@ROWCOUNT
1234PRINT 'Delete records from tbFlattenedRevisionInCategory: ' + @tbFlattenedRevisionInCategory
1235
1236delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1237SELECT @tbRevisionInCategory = @@ROWCOUNT
1238PRINT 'Delete records from tbRevisionInCategory: ' + @tbRevisionInCategory
1239
1240delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1241SELECT @tbMoreInfoURLForRevision = @@ROWCOUNT
1242PRINT 'Delete records from tbMoreInfoURLForRevision: ' + @tbMoreInfoURLForRevision
1243
1244delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)
1245SELECT @tbRevision = @@ROWCOUNT
1246PRINT 'Delete records from tbRevision: ' + @tbRevision
1247
1248delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)
1249SELECT @tbUpdateSummaryForAllComputers = @@ROWCOUNT
1250PRINT 'Delete records from tbUpdateSummaryForAllComputers: ' + @tbUpdateSummaryForAllComputers
1251
1252PRINT CHAR(13)+CHAR(10) + 'This is the last query and this is really what we came here for.'
1253
1254delete from tbUpdate where UpdateTypeID = @var1
1255SELECT @tbUpdate = @@ROWCOUNT
1256PRINT 'Delete records from tbUpdate: ' + @tbUpdate
1257
1258/*
1259If at this point you get an error saying something about foreign key constraint, that will be most likely
1260due to the difference between which reports I ran in my WSUS installation and which reports were ran against
1261your particular installation. Fortunately, the error gives you exact location (table) where this constraint
1262is violated, so you can adjust one of the queries in the batch above to delete references in any other tables.
1263*/
1264"@
1265 Write-Verbose "Create a file with the content of the RemoveWSUSDrivers Script above in the same working directory as this PowerShell script is running."
1266 $WSUSCleanRemoveWSUSDriversSQLScriptFile = "$WSUSCleanScriptPath\WSUSCleanRemoveWSUSDrivers.sql"
1267 $WSUSCleanRemoveWSUSDriversSQLScript | Out-File "$WSUSCleanRemoveWSUSDriversSQLScriptFile"
1268 # Re-jig the $WSUSCleanSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1269 $WSUSCleanSQLConnectCommand = $WSUSCleanSQLConnectCommand.Replace('$','`$')
1270 Write-Verbose "Execute the SQL Script and store the results in a variable."
1271 $WSUSCleanRemoveWSUSDriversSQLScriptJobCommand = [scriptblock]::create("$WSUSCleanSQLConnectCommand -i `"$WSUSCleanRemoveWSUSDriversSQLScriptFile`" -I")
1272 Write-Verbose "`$WSUSCleanRemoveWSUSDriversSQLScriptJobCommand = $WSUSCleanRemoveWSUSDriversSQLScriptJobCommand"
1273 $WSUSCleanRemoveWSUSDriversSQLScriptJob = Start-Job -ScriptBlock $WSUSCleanRemoveWSUSDriversSQLScriptJobCommand
1274 Wait-Job $WSUSCleanRemoveWSUSDriversSQLScriptJob
1275 $WSUSCleanRemoveWSUSDriversSQLScriptJobOutput = Receive-Job $WSUSCleanRemoveWSUSDriversSQLScriptJob
1276 Remove-Job $WSUSCleanRemoveWSUSDriversSQLScriptJob
1277 Write-Verbose "Remove the SQL Script file."
1278 Remove-Item "$WSUSCleanRemoveWSUSDriversSQLScriptFile"
1279 $Script:WSUSCleanRemoveWSUSDriversSQLOutputTXT = $WSUSCleanRemoveWSUSDriversSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n`r`n"
1280 $Script:WSUSCleanRemoveWSUSDriversSQLOutputHTML = $WSUSCleanRemoveWSUSDriversSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1281
1282 # Variables Output
1283 # $WSUSCleanRemoveWSUSDriversSQLOutputTXT
1284 # $WSUSCleanRemoveWSUSDriversSQLOutputHTML
1285
1286 }
1287 function RemoveWSUSDriversPS {
1288 $Count = 0
1289 $WSUSCleanWSUSServerAdminProxy.GetUpdates() | Where-Object { $_.IsDeclined -eq $true -and $_.UpdateClassificationTitle -eq "Drivers" } | ForEach-Object {
1290 # Delete these updates
1291 $WSUSCleanWSUSServerAdminProxy.DeleteUpdate($_.Id.UpdateId.ToString())
1292 $DeleteDeclinedDriverTitle = $_.Title
1293 $Count++
1294 $WSUSCleanRemoveWSUSDriversPSDeleteOutputTXT += "$($Count). $($DeleteDeclinedDriverTitle)`n`n"
1295 $WSUSCleanRemoveWSUSDriversPSDeleteOutputHTML += "<li>$DeleteDeclinedDriverTitle</li>`n"
1296 }
1297 $WSUSCleanRemoveWSUSDriversPSDeleteOutputTXT += "`n`n"
1298 $WSUSCleanRemoveWSUSDriversPSDeleteOutputHTML += "</ol>`n"
1299
1300 $Script:WSUSCleanRemoveWSUSDriversPSOutputTXT += "`n`n"
1301 $Script:WSUSCleanRemoveWSUSDriversPSOutputHTML += "<ol>`n"
1302 $Script:WSUSCleanRemoveWSUSDriversPSOutputTXT += $WSUSCleanRemoveWSUSDriversPSDeleteOutputTXT
1303 $Script:WSUSCleanRemoveWSUSDriversPSOutputHTML += $WSUSCleanRemoveWSUSDriversPSDeleteOutputHTML
1304
1305 # Variables Output
1306 # $WSUSCleanRemoveWSUSDriversPSOutputTXT
1307 # $WSUSCleanRemoveWSUSDriversPSOutputHTML
1308 }
1309 # Process the appropriate internal function
1310 $DateNow = Get-Date
1311 if ($SQL -eq $True) { RemoveWSUSDriversSQL } else { RemoveWSUSDriversPS }
1312 $FinishedRunning = Get-Date
1313 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1314 # Create the output for the RemoveWSUSDrivers function
1315 $Script:WSUSCleanRemoveWSUSDriversOutputTXT += "WSUSClean Remove WSUS Drivers:`n`n"
1316 $Script:WSUSCleanRemoveWSUSDriversOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean Remove WSUS Drivers:</span></p>`n"
1317 if ($SQL -eq $True) {
1318 $Script:WSUSCleanRemoveWSUSDriversOutputTXT += $WSUSCleanRemoveWSUSDriversSQLOutputTXT
1319 $Script:WSUSCleanRemoveWSUSDriversOutputHTML += $WSUSCleanRemoveWSUSDriversSQLOutputHTML
1320 } else {
1321 $Script:WSUSCleanRemoveWSUSDriversOutputTXT += $WSUSCleanRemoveWSUSDriversPSOutputTXT
1322 $Script:WSUSCleanRemoveWSUSDriversOutputHTML += $WSUSCleanRemoveWSUSDriversPSOutputHTML
1323 }
1324 $Script:WSUSCleanRemoveWSUSDriversOutputTXT += "Remove WSUS Drivers Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1325 $Script:WSUSCleanRemoveWSUSDriversOutputHTML += "<p>Remove WSUS Drivers Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1326
1327 # Variables Output
1328 # $WSUSCleanRemoveWSUSDriversOutputTXT
1329 # $WSUSCleanRemoveWSUSDriversOutputHTML
1330}
1331#endregion RemoveWSUSDrivers Function
1332
1333#region RemoveDeclinedWSUSUpdates Function
1334################################
1335# WSUSClean Remove Declined WSUS #
1336# Updates Stream #
1337################################
1338
1339function RemoveDeclinedWSUSUpdates {
1340 param (
1341 [Switch]$Display,
1342 [Switch]$Proceed
1343 )
1344 # Log the date first
1345 $DateNow = Get-Date
1346 Write-Verbose "Create an update scope"
1347 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
1348 Write-Verbose "By default the update scope is created for any approval states"
1349 $UpdateScope.ApprovedStates = "Any"
1350 Write-Verbose "Get all updates that have been Superseded and not Declined"
1351 $WSUSCleanRemoveDeclinedWSUSUpdatesUpdates = $WSUSCleanWSUSServerAdminProxy.GetUpdates($UpdateScope) | Where { ($_.isDeclined) }
1352 function RemoveDeclinedWSUSUpdatesCountUpdates {
1353 Write-Verbose "First count how many updates will be removed that are already declined updates - just for fun. I like fun :)"
1354 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesCount = "{0:N0}" -f $WSUSCleanRemoveDeclinedWSUSUpdatesUpdates.Count
1355 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT += "The number of declined updates that would be removed from the database are: $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesCount.`r`n`r`n"
1356 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML += "<p>The number of declined updates that would be removed from the database are: $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesCount.</p>`n"
1357
1358 # Variables Output
1359 # $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT
1360 # $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML
1361 }
1362
1363 function RemoveDeclinedWSUSUpdatesDisplayUpdates {
1364 Write-Verbose "Display the titles of the declined updates that will be removed from the database - just for fun. I like fun :)"
1365 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "<ol>`n"
1366 $Count=0
1367 ForEach ($update in $WSUSCleanRemoveDeclinedWSUSUpdatesUpdates) {
1368 $Count++
1369 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputTXT += "$($Count). $($update.title) - https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`r`n"
1370 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "<li><a href=`"https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`">$($update.title)</a></li>`n"
1371 }
1372 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputTXT += "`r`n"
1373 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "</ol>`n"
1374
1375 # Variables Output
1376 # $WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputTXT
1377 # $WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputHTML
1378 }
1379
1380 function RemoveDeclinedWSUSUpdatesProceed {
1381 Write-Output "You've chosen to remove declined updates from the database. Removing $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates."
1382 Write-Output ""
1383 Write-Output "Please be patient, this may take a while."
1384 Write-Output ""
1385 Write-Output "It is not abnormal for this process to take minutes, hours, or days. It varies per install and per execution."
1386 Write-Output ""
1387 Write-Output "Any errors received are due to updates that are shared between systems. Eg. A Windows 7 update may share itself also with a Server 2008 update."
1388 Write-Output ""
1389 Write-Output "If you cancel this process (CTRL-C/Close the window), you will lose the documentation/log of what has happened thusfar, but it will resume where it left off when you run it again."
1390 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputTXT += "You've chosen to remove declined updates from the database. Removing $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates.`r`n`r`n"
1391 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputHTML += "<p>You've chosen to remove declined updates from the database. <strong>Removing $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates.</strong></p>`n"
1392 # Remove these updates
1393 $WSUSCleanRemoveDeclinedWSUSUpdatesUpdates | ForEach-Object {
1394 $DeleteID = $_.Id.UpdateId.ToString()
1395 Try {
1396 $WSUSCleanRemoveDeclinedWSUSUpdatesUpdateTitle = $($_.Title)
1397 Write-Output "Deleting" $WSUSCleanRemoveDeclinedWSUSUpdatesUpdateTitle
1398 $WSUSCleanWSUSServerAdminProxy.DeleteUpdate($DeleteId)
1399 }
1400 Catch {
1401 $ExceptionError = $_.Exception
1402 if ([string]::isnullorempty($WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsTXT)) { $WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsTXT = "" }
1403 if ([string]::isnullorempty($WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsHTML)) { $WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsHTML = "" }
1404 $WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsTXT += "Error: $WSUSCleanRemoveDeclinedWSUSUpdatesUpdateTitle`r`n`r`n$ExceptionError.InnerException`r`n`r`n"
1405 $WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsHTML += "<li><p>$WSUSCleanRemoveDeclinedWSUSUpdatesUpdateTitle</p>$ExceptionError.InnerException</li>"
1406 }
1407 Finally {
1408 if ($ExceptionError) {
1409 Write-Output "Errors:" $ExceptionError.Message
1410 Remove-Variable ExceptionError
1411 } else {
1412 Write-Verbose "Successful"
1413 }
1414 }
1415 }
1416 if (-not [string]::isnullorempty($WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsTXT)) {
1417 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputTXT += "*** Errors Removing Declined WSUS Updates ***`r`n"
1418 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputTXT += $WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsTXT
1419 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputTXT += "`r`n`r`n"
1420 }
1421 if (-not [string]::isnullorempty($WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsHTML)) {
1422 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputHTML += "<div class='error'><h1>Errors Removing Declined WSUS Updates</h1><ol start='1'>"
1423 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputHTML += $WSUSCleanRemoveDeclinedWSUSUpdatesProceedExceptionsHTML
1424 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputHTML += "</ol></div>"
1425 }
1426
1427 # Variables Output
1428 # $WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputTXT
1429 # $WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputHTML
1430 }
1431
1432 RemoveDeclinedWSUSUpdatesCountUpdates
1433 if ($Display -ne $False) { RemoveDeclinedWSUSUpdatesDisplayUpdates }
1434 if ($Proceed -ne $False) { RemoveDeclinedWSUSUpdatesProceed }
1435 $FinishedRunning = Get-Date
1436 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1437
1438 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputTXT += "WSUSClean Remove Declined WSUS Updates:`r`n`r`n"
1439 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean Remove Declined WSUS Updates:</span></p>`n<ol>`n"
1440 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputTXT += $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT
1441 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputHTML += $WSUSCleanRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML
1442 if ($Display -ne $False) {
1443 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputTXT += $WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputTXT
1444 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputHTML += $WSUSCleanRemoveDeclinedWSUSUpdatesDisplayOutputHTML
1445 }
1446 if ($Proceed -ne $False) {
1447 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputTXT += $WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputTXT
1448 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputHTML += $WSUSCleanRemoveDeclinedWSUSUpdatesProceedOutputHTML
1449 }
1450 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputTXT += "Remove Declined WSUS Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1451 $Script:WSUSCleanRemoveDeclinedWSUSUpdatesOutputHTML += "<p>Remove Declined WSUS Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1452
1453 # Variables Output
1454 # $WSUSCleanRemoveDeclinedWSUSUpdatesOutputTXT
1455 # $WSUSCleanRemoveDeclinedWSUSUpdatesOutputHTML
1456}
1457#endregion RemoveDeclinedWSUSUpdates Function
1458
1459#region CompressUpdateRevisions Function
1460################################
1461# WSUSClean Compress Update #
1462# Revisions Stream #
1463################################
1464
1465function CompressUpdateRevisions {
1466 Param (
1467 )
1468 $DateNow = Get-Date
1469 $WSUSCleanCompressUpdateRevisionsSQLScript = @"
1470USE SUSDB;
1471GO
1472-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
1473SET NOCOUNT ON
1474
1475DECLARE @var1 INT, @curitem INT, @totaltocompress INT
1476DECLARE @msg nvarchar(200)
1477
1478IF EXISTS (
1479 SELECT * FROM tempdb.dbo.sysobjects o
1480 WHERE o.xtype IN ('U')
1481 AND o.id = object_id(N'tempdb..#results')
1482)
1483DROP TABLE #results
1484CREATE TABLE #results (Col1 INT)
1485
1486-- Compress Update Revisions
1487INSERT INTO #results(Col1) EXEC spGetUpdatesToCompress
1488SET @totaltocompress = (SELECT COUNT(*) FROM #results)
1489SELECT @curitem=1
1490DECLARE WC Cursor FOR SELECT Col1 FROM #results;
1491OPEN WC
1492FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
1493BEGIN
1494 SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltocompress as varchar(5)) + ': Compressing ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
1495 RAISERROR(@msg,0,1) WITH NOWAIT
1496 EXEC spCompressUpdate @localUpdateID=@var1
1497 SET @curitem = @curitem +1
1498 FETCH NEXT FROM WC INTO @var1
1499END
1500CLOSE WC
1501DEALLOCATE WC
1502DROP TABLE #results
1503"@
1504 Write-Verbose "Create a file with the content of the CompressUpdateRevisions Script above in the same working directory as this PowerShell script is running."
1505 $WSUSCleanCompressUpdateRevisionsSQLScriptFile = "$WSUSCleanScriptPath\WSUSCleanCompressUpdateRevisions.sql"
1506 $WSUSCleanCompressUpdateRevisionsSQLScript | Out-File "$WSUSCleanCompressUpdateRevisionsSQLScriptFile"
1507
1508 # Re-jig the $WSUSCleanSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1509 $WSUSCleanSQLConnectCommand = $WSUSCleanSQLConnectCommand.Replace('$','`$')
1510 Write-Verbose "Execute the SQL Script and store the results in a variable."
1511 $WSUSCleanCompressUpdateRevisionsSQLScriptJobCommand = [scriptblock]::create("$WSUSCleanSQLConnectCommand -i `"$WSUSCleanCompressUpdateRevisionsSQLScriptFile`" -I")
1512 Write-Verbose "`$WSUSCleanCompressUpdateRevisionsSQLScriptJob = $WSUSCleanCompressUpdateRevisionsSQLScriptJobCommand"
1513 $WSUSCleanCompressUpdateRevisionsSQLScriptJob = Start-Job -ScriptBlock $WSUSCleanCompressUpdateRevisionsSQLScriptJobCommand
1514 Wait-Job $WSUSCleanCompressUpdateRevisionsSQLScriptJob
1515 $WSUSCleanCompressUpdateRevisionsSQLScriptJobOutput = Receive-Job $WSUSCleanCompressUpdateRevisionsSQLScriptJob
1516 Remove-Job $WSUSCleanCompressUpdateRevisionsSQLScriptJob
1517 Write-Verbose "Remove the SQL Script file."
1518 Remove-Item "$WSUSCleanCompressUpdateRevisionsSQLScriptFile"
1519 $FinishedRunning = Get-Date
1520 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1521 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1522 $Script:WSUSCleanCompressUpdateRevisionsOutputTXT += "WSUSClean Compress Update Revisions:`r`n`r`n"
1523 $Script:WSUSCleanCompressUpdateRevisionsOutputTXT += $WSUSCleanCompressUpdateRevisionsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1524 $Script:WSUSCleanCompressUpdateRevisionsOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean Compress Update Revisions:</span></p>`n`n"
1525 $Script:WSUSCleanCompressUpdateRevisionsOutputHTML += $WSUSCleanCompressUpdateRevisionsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1526 $Script:WSUSCleanCompressUpdateRevisionsOutputTXT += "WSUSClean Compress Update Revisions Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1527 $Script:WSUSCleanCompressUpdateRevisionsOutputHTML += "<p>WSUSClean Compress Update Revisions Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1528
1529 # Variables Output
1530 # $WSUSCleanCompressUpdateRevisionsOutputTXT
1531 # $WSUSCleanCompressUpdateRevisionsOutputHTML
1532}
1533#endregion CompressUpdateRevisions Function
1534
1535#region RemoveObsoleteUpdates Function
1536################################
1537# WSUSClean Remove Obsolete #
1538# Updates Stream #
1539################################
1540
1541function RemoveObsoleteUpdates {
1542 Param (
1543 )
1544 $DateNow = Get-Date
1545 $WSUSCleanRemoveObsoleteUpdatesSQLScript = @"
1546USE SUSDB;
1547GO
1548-- SET NOCOUNT ON added to prevent extra result sets from
1549-- interfering with SELECT statements.
1550SET NOCOUNT ON
1551
1552DECLARE @var1 INT, @curitem INT, @totaltoremove INT
1553DECLARE @msg nvarchar(200)
1554
1555IF EXISTS (
1556 SELECT * FROM tempdb.dbo.sysobjects o
1557 WHERE o.xtype IN ('U')
1558 AND o.id = object_id(N'tempdb..#results')
1559)
1560DROP TABLE #results
1561CREATE TABLE #results (Col1 INT)
1562
1563-- Remove Obsolete Updates
1564INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
1565SET @totaltoremove = (SELECT COUNT(*) FROM #results)
1566SELECT @curitem=1
1567DECLARE WC Cursor FOR SELECT Col1 FROM #results
1568OPEN WC
1569FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
1570BEGIN
1571 SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltoremove as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
1572 RAISERROR(@msg,0,1) WITH NOWAIT
1573 EXEC spDeleteUpdate @localUpdateID=@var1
1574 SET @curitem = @curitem +1
1575 FETCH NEXT FROM WC INTO @var1
1576END
1577CLOSE WC
1578DEALLOCATE WC
1579DROP TABLE #results
1580"@
1581 Write-Output ""
1582 Write-Output "Please be patient, this may take a while."
1583 Write-Output ""
1584 Write-Output "It is not abnormal for this process to take minutes, hours, or days. It varies per install and per execution."
1585 Write-Output ""
1586 Write-Output "If you cancel this process (CTRL-C/Close the window), you will lose the documentation/log of what has happened thusfar, but it will resume where it left off when you run it again."
1587 Write-Verbose "Create a file with the content of the RemoveObsoleteUpdates Script above in the same working directory as this PowerShell script is running."
1588 $WSUSCleanRemoveObsoleteUpdatesSQLScriptFile = "$WSUSCleanScriptPath\WSUSCleanRemoveObsoleteUpdates.sql"
1589 $WSUSCleanRemoveObsoleteUpdatesSQLScript | Out-File "$WSUSCleanRemoveObsoleteUpdatesSQLScriptFile"
1590 Write-Debug "Just wrote to script file"
1591 # Re-jig the $WSUSCleanSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1592 $WSUSCleanSQLConnectCommand = $WSUSCleanSQLConnectCommand.Replace('$','`$')
1593 Write-Verbose "Execute the SQL Script and store the results in a variable."
1594 $WSUSCleanRemoveObsoleteUpdatesSQLScriptJobCommand = [scriptblock]::create("$WSUSCleanSQLConnectCommand -i `"$WSUSCleanRemoveObsoleteUpdatesSQLScriptFile`" -I")
1595 Write-Verbose "`$WSUSCleanRemoveObsoleteUpdatesSQLScriptJobCommand = $WSUSCleanRemoveObsoleteUpdatesSQLScriptJobCommand"
1596 $WSUSCleanRemoveObsoleteUpdatesSQLScriptJob = Start-Job -ScriptBlock $WSUSCleanRemoveObsoleteUpdatesSQLScriptJobCommand
1597 Wait-Job $WSUSCleanRemoveObsoleteUpdatesSQLScriptJob
1598 $WSUSCleanRemoveObsoleteUpdatesSQLScriptJobOutput = Receive-Job $WSUSCleanRemoveObsoleteUpdatesSQLScriptJob
1599 Write-Debug "Just finished - check WSUSCleanRemoveObsoleteUpdatesSQLScriptJobOutput"
1600 Remove-Job $WSUSCleanRemoveObsoleteUpdatesSQLScriptJob
1601 Write-Verbose "Remove the SQL Script file."
1602 Remove-Item "$WSUSCleanRemoveObsoleteUpdatesSQLScriptFile"
1603 $FinishedRunning = Get-Date
1604 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1605 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1606 $Script:WSUSCleanRemoveObsoleteUpdatesOutputTXT += "WSUSClean Remove Obsolete Updates:`r`n`r`n"
1607 $Script:WSUSCleanRemoveObsoleteUpdatesOutputTXT += $WSUSCleanRemoveObsoleteUpdatesSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1608 $Script:WSUSCleanRemoveObsoleteUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean Remove Obsolete Updates:</span></p>`n`n"
1609 $Script:WSUSCleanRemoveObsoleteUpdatesOutputHTML += $WSUSCleanRemoveObsoleteUpdatesSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1610 $Script:WSUSCleanRemoveObsoleteUpdatesOutputTXT += "WSUSClean Remove Obsolete Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1611 $Script:WSUSCleanRemoveObsoleteUpdatesOutputHTML += "<p>WSUSClean Remove Obsolete Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1612
1613 # Variables Output
1614 # $WSUSCleanRemoveObsoleteUpdatesOutputTXT
1615 # $WSUSCleanRemoveObsoleteUpdatesOutputHTML
1616}
1617#endregion RemoveObsoleteUpdates Function
1618
1619#region WSUSDBMaintenance Function
1620################################
1621# WSUSClean WSUS DB Maintenance #
1622# Stream #
1623################################
1624
1625function WSUSDBMaintenance {
1626 Param (
1627 [Switch]$NoOutput
1628 )
1629 $DateNow = Get-Date
1630 $WSUSCleanWSUSDBMaintenanceSQLScript = @"
1631/*
1632################################
1633# WSUSClean DBMaintenance #
1634# SQL Script #
1635# Version 1.0 #
1636# Taken from TechNet #
1637# referenced below. #
1638################################
1639*/
1640-- Taken from https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61
1641
1642/******************************************************************************
1643This sample T-SQL script performs basic maintenance tasks on SUSDB
16441. Identifies indexes that are fragmented and defragments them. For certain
1645 tables, a fill-factor is set in order to improve insert performance.
1646 Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx
1647 and tailored for SUSDB requirements
16482. Updates potentially out-of-date table statistics.
1649******************************************************************************/
1650
1651USE SUSDB;
1652GO
1653SET NOCOUNT ON;
1654
1655-- Rebuild or reorganize indexes based on their fragmentation levels
1656DECLARE @work_to_do TABLE (
1657 objectid int
1658 , indexid int
1659 , pagedensity float
1660 , fragmentation float
1661 , numrows int
1662)
1663
1664DECLARE @objectid int;
1665DECLARE @indexid int;
1666DECLARE @schemaname nvarchar(130);
1667DECLARE @objectname nvarchar(130);
1668DECLARE @indexname nvarchar(130);
1669DECLARE @numrows int
1670DECLARE @density float;
1671DECLARE @fragmentation float;
1672DECLARE @command nvarchar(4000);
1673DECLARE @fillfactorset bit
1674DECLARE @numpages int
1675
1676-- Select indexes that need to be defragmented based on the following
1677-- * Page density is low
1678-- * External fragmentation is high in relation to index size
1679PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
1680INSERT @work_to_do
1681SELECT
1682 f.object_id
1683 , index_id
1684 , avg_page_space_used_in_percent
1685 , avg_fragmentation_in_percent
1686 , record_count
1687FROM
1688 sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
1689WHERE
1690 (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
1691 or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)
1692 or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)
1693
1694PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))
1695
1696PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)
1697
1698SELECT @numpages = sum(ps.used_page_count)
1699FROM
1700 @work_to_do AS fi
1701 INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
1702 INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
1703
1704-- Declare the cursor for the list of indexes to be processed.
1705DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
1706
1707-- Open the cursor.
1708OPEN curIndexes
1709
1710-- Loop through the indexes
1711WHILE (1=1)
1712BEGIN
1713 FETCH NEXT FROM curIndexes
1714 INTO @objectid, @indexid, @density, @fragmentation, @numrows;
1715 IF @@FETCH_STATUS < 0 BREAK;
1716
1717 SELECT
1718 @objectname = QUOTENAME(o.name)
1719 , @schemaname = QUOTENAME(s.name)
1720 FROM
1721 sys.objects AS o
1722 INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
1723 WHERE
1724 o.object_id = @objectid;
1725
1726 SELECT
1727 @indexname = QUOTENAME(name)
1728 , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
1729 FROM
1730 sys.indexes
1731 WHERE
1732 object_id = @objectid AND index_id = @indexid;
1733
1734 IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
1735 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
1736 ELSE IF @numrows >= 5000 AND @fillfactorset = 0
1737 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
1738 ELSE
1739 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
1740 PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
1741 EXEC (@command);
1742 PRINT convert(nvarchar, getdate(), 121) + N' Done.';
1743END
1744
1745-- Close and deallocate the cursor.
1746CLOSE curIndexes;
1747DEALLOCATE curIndexes;
1748
1749IF EXISTS (SELECT * FROM @work_to_do)
1750BEGIN
1751 PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
1752 SELECT @numpages = @numpages - sum(ps.used_page_count)
1753 FROM
1754 @work_to_do AS fi
1755 INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
1756 INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
1757 PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
1758END
1759GO
1760
1761--Update all statistics
1762PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
1763EXEC sp_updatestats
1764PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
1765GO
1766"@
1767 Write-Verbose "Create a file with the content of the WSUSDBMaintenance Script above in the same working directory as this PowerShell script is running."
1768 $WSUSCleanWSUSDBMaintenanceSQLScriptFile = "$WSUSCleanScriptPath\WSUSCleanWSUSDBMaintenance.sql"
1769 $WSUSCleanWSUSDBMaintenanceSQLScript | Out-File "$WSUSCleanWSUSDBMaintenanceSQLScriptFile"
1770
1771 # Re-jig the $WSUSCleanSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1772 $WSUSCleanSQLConnectCommand = $WSUSCleanSQLConnectCommand.Replace('$','`$')
1773 Write-Verbose "Execute the SQL Script and store the results in a variable."
1774 $WSUSCleanWSUSDBMaintenanceSQLScriptJobCommand = [scriptblock]::create("$WSUSCleanSQLConnectCommand -i `"$WSUSCleanWSUSDBMaintenanceSQLScriptFile`" -I")
1775 Write-Verbose "`$WSUSCleanWSUSDBMaintenanceSQLScriptJobCommand = $WSUSCleanWSUSDBMaintenanceSQLScriptJobCommand"
1776 $WSUSCleanWSUSDBMaintenanceSQLScriptJob = Start-Job -ScriptBlock $WSUSCleanWSUSDBMaintenanceSQLScriptJobCommand
1777 Wait-Job $WSUSCleanWSUSDBMaintenanceSQLScriptJob
1778 $WSUSCleanWSUSDBMaintenanceSQLScriptJobOutput = Receive-Job $WSUSCleanWSUSDBMaintenanceSQLScriptJob
1779 Remove-Job $WSUSCleanWSUSDBMaintenanceSQLScriptJob
1780 Write-Verbose "Remove the SQL Script file."
1781 Remove-Item "$WSUSCleanWSUSDBMaintenanceSQLScriptFile"
1782 $FinishedRunning = Get-Date
1783 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1784 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1785 if ($NoOutput -eq $False) {
1786 $Script:WSUSCleanWSUSDBMaintenanceOutputTXT += "WSUSClean WSUS DB Maintenance:`r`n`r`n"
1787 $Script:WSUSCleanWSUSDBMaintenanceOutputTXT += $WSUSCleanWSUSDBMaintenanceSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1788 $Script:WSUSCleanWSUSDBMaintenanceOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean WSUS DB Maintenance:</span></p>`n`n"
1789 $Script:WSUSCleanWSUSDBMaintenanceOutputHTML += $WSUSCleanWSUSDBMaintenanceSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1790 } else {
1791 $Script:WSUSCleanWSUSDBMaintenanceOutputTXT += "WSUSClean WSUS DB Maintenance:`r`n`r`n"
1792 $Script:WSUSCleanWSUSDBMaintenanceOutputTXT += "The WSUSClean WSUS DB Maintenance Stream was run with the -NoOutput switch.`r`n"
1793 $Script:WSUSCleanWSUSDBMaintenanceOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean WSUS DB Maintenance:</span></p>`n`n"
1794 $Script:WSUSCleanWSUSDBMaintenanceOutputHTML += "<p>The WSUSClean WSUS DB Maintenance Stream was run with the -NoOutput switch.</p>`n`n"
1795 }
1796 $Script:WSUSCleanWSUSDBMaintenanceOutputTXT += "WSUS DB Maintenance Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1797 $Script:WSUSCleanWSUSDBMaintenanceOutputHTML += "<p>WSUS DB Maintenance Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1798
1799 # Variables Output
1800 # $WSUSCleanWSUSDBMaintenanceOutputTXT
1801 # $WSUSCleanWSUSDBMaintenanceOutputHTML
1802}
1803#endregion WSUSDBMaintenance Function
1804
1805#region DeclineSupersededUpdates Function
1806################################
1807# WSUSClean Decline Superseded #
1808# Updates Stream #
1809################################
1810
1811function DeclineSupersededUpdates {
1812 param (
1813 [Switch]$Display,
1814 [Switch]$Proceed
1815 )
1816 # Log the date first
1817 $DateNow = Get-Date
1818 Write-Verbose "Create an update scope"
1819 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
1820 Write-Verbose "By default the update scope is created for any approval states"
1821 $UpdateScope.ApprovedStates = "Any"
1822 Write-Verbose "Get all updates that have been superseded and not declined"
1823 $WSUSCleanDeclineSupersededUpdatesUpdates = $WSUSCleanWSUSServerAdminProxy.GetUpdates($UpdateScope) | Where { ($_.IsSuperseded) -and -not ($_.isDeclined) }
1824 function DeclineSupersededUpdatesCountUpdates {
1825 Write-Verbose "First count how many updates will be declined by declining superseded Updates - just for fun. I like fun :)"
1826 $Script:WSUSCleanDeclineSupersededUpdatesCountUpdatesCount = "{0:N0}" -f $WSUSCleanDeclineSupersededUpdatesUpdates.Count
1827 $Script:WSUSCleanDeclineSupersededUpdatesCountUpdatesOutputTXT += "The number of superseded updates that would be declined is: $WSUSCleanDeclineSupersededUpdatesCountUpdatesCount.`r`n"
1828 $Script:WSUSCleanDeclineSupersededUpdatesCountUpdatesOutputHTML += "<p>The number of superseded updates that would be declined is: $WSUSCleanDeclineSupersededUpdatesCountUpdatesCount.</p>`n"
1829
1830 # Variables Output
1831 # $WSUSCleanDeclineSupersededUpdatesCountUpdatesOutputTXT
1832 # $WSUSCleanDeclineSupersededUpdatesCountUpdatesOutputTXT
1833 }
1834 function DeclineSupersededUpdatesDisplayUpdates {
1835 Write-Verbose "Display the titles of the Superseded updates that will be declined - just for fun. I like fun :)"
1836 $Script:WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "<ol>`n"
1837 $Count=0
1838 ForEach ($update in $WSUSCleanDeclineSupersededUpdatesUpdates) {
1839 $Count++
1840 $Script:WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputTXT += "$($Count). $($update.title) - https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`r`n"
1841 $Script:WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "<li><a href=`"https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`">$($update.title)</a></li>`n"
1842 }
1843 $Script:WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputTXT += "`r`n"
1844 $Script:WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "</ol>`n"
1845
1846 # Variables Output
1847 # $WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputTXT
1848 # $WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputHTML
1849 }
1850 function DeclineSupersededUpdatesProceed {
1851 $Script:WSUSCleanDeclineSupersededUpdatesProceedOutputTXT += "You've chosen to Decline Superseded Updates. Declining $WSUSCleanDeclineSupersededUpdatesCountUpdatesCount Superseded updates.`r`n`r`n"
1852 $Script:WSUSCleanDeclineSupersededUpdatesProceedOutputHTML += "<p>You've chosen to Decline Superseded Updates. <strong>Declining $WSUSCleanDeclineSupersededUpdatesCountUpdatesCount Superseded updates.</strong></p>`n"
1853 Write-Verbose "Decline these updates"
1854 $WSUSCleanDeclineSupersededUpdatesUpdates | ForEach-Object -Process { $_.Decline() }
1855
1856 # Variables Output
1857 # $WSUSCleanDeclineSupersededUpdatesProceedOutputTXT
1858 # $WSUSCleanDeclineSupersededUpdatesProceedOutputHTML
1859 }
1860
1861 DeclineSupersededUpdatesCountUpdates
1862 if ($Display -ne $False) { DeclineSupersededUpdatesDisplayUpdates }
1863 if ($Proceed -ne $False) { DeclineSupersededUpdatesProceed }
1864 $FinishedRunning = Get-Date
1865 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1866
1867 $Script:WSUSCleanDeclineSupersededUpdatesOutputTXT += "WSUSClean Decline Superseded Updates:`r`n"
1868 $Script:WSUSCleanDeclineSupersededUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean Decline Superseded Updates:</span><br>`n"
1869 $Script:WSUSCleanDeclineSupersededUpdatesOutputTXT += $WSUSCleanDeclineSupersededUpdatesCountUpdatesOutputTXT
1870 $Script:WSUSCleanDeclineSupersededUpdatesOutputHTML += $WSUSCleanDeclineSupersededUpdatesCountUpdatesOutputHTML
1871 if ($Display -ne $False) {
1872 $Script:WSUSCleanDeclineSupersededUpdatesOutputTXT += $WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputTXT
1873 $Script:WSUSCleanDeclineSupersededUpdatesOutputHTML += $WSUSCleanDeclineSupersededUpdatesUpdatesDisplayOutputHTML
1874 }
1875 if ($Proceed -ne $False) {
1876 $Script:WSUSCleanDeclineSupersededUpdatesOutputTXT += $WSUSCleanDeclineSupersededUpdatesProceedOutputTXT
1877 $Script:WSUSCleanDeclineSupersededUpdatesOutputHTML += $WSUSCleanDeclineSupersededUpdatesProceedOutputHTML
1878 }
1879 $Script:WSUSCleanDeclineSupersededUpdatesOutputTXT += "Decline Superseded Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1880 $Script:WSUSCleanDeclineSupersededUpdatesOutputHTML += "<p>Decline Superseded Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1881}
1882#endregion DeclineSupersededUpdates Function
1883
1884#region CleanUpWSUSSynchronizationLogs Function
1885################################
1886# Clean Up WSUS #
1887# Synchronization Logs Stream #
1888################################
1889
1890function CleanUpWSUSSynchronizationLogs {
1891 Param(
1892 [Int]$ConsistencyNumber,
1893 [String]$ConsistencyTime,
1894 [Switch]$All
1895 )
1896 $DateNow = Get-Date
1897 $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScript = @"
1898/*
1899################################
1900# WSUSClean Synchronization #
1901# Cleanup SQL Script #
1902# Version 1.0 #
1903# Taken from various sources #
1904# from the Internet. #
1905################################
1906*/
1907$(
1908 if ($ConsistencyNumber -ne "0") {
1909 $("
1910USE SUSDB
1911GO
1912DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389') AND DATEDIFF($($ConsistencyTime), TimeAtServer, CURRENT_TIMESTAMP) >= $($ConsistencyNumber);
1913GO")
1914}
1915elseif ($All -ne $False) {
1916$("USE SUSDB
1917GO
1918DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389')
1919GO")
1920}
1921)
1922"@
1923 Write-Verbose "Create a file with the content of the WSUSCleanCleanUpWSUSSynchronizationLogs Script above in the same working directory as this PowerShell script is running."
1924 $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptFile = "$WSUSCleanScriptPath\WSUSCleanCleanUpWSUSSynchronizationLogs.sql"
1925 $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScript | Out-File "$WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptFile"
1926 # Re-jig the $WSUSCleanSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1927 $WSUSCleanSQLConnectCommand = $WSUSCleanSQLConnectCommand.Replace('$','`$')
1928 Write-Verbose "Execute the SQL Script and store the results in a variable."
1929 $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJobCommand = [scriptblock]::create("$WSUSCleanSQLConnectCommand -i `"$WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptFile`" -I")
1930 Write-Verbose "`$WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJobCommand = $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJobCommand"
1931 $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJob = Start-Job -ScriptBlock $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJobCommand
1932 Wait-Job $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJob
1933 $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJobOutput = Receive-Job $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJob
1934 Remove-Job $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJob
1935 Write-Verbose "Remove the SQL Script file."
1936 Remove-Item "$WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptFile"
1937 $FinishedRunning = Get-Date
1938 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1939
1940 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1941 $Script:WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputTXT += "WSUSClean Clean Up WSUS Synchornization Logs:`r`n`r`n"
1942 $Script:WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputTXT += $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1943 $Script:WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputTXT += "Clean Up WSUS Synchronization Logs Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1944
1945 $Script:WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean Clean Up WSUS Synchornization Logs:</span></p>`r`n"
1946 $Script:WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputHTML += $WSUSCleanCleanUpWSUSSynchronizationLogsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1947 $Script:WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputHTML += "<p>Clean Up WSUS Synchronization Logs Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1948
1949 # Variables Output
1950 # $WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputTXT
1951 # $WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputHTML
1952
1953}
1954#endregion CleanUpWSUSSynchronizationLogs Function
1955
1956#region ComputerObjectCleanup Function
1957################################
1958# Computer Object Cleanup #
1959# Stream #
1960################################
1961
1962function ComputerObjectCleanup {
1963 $DateNow = Get-Date
1964 Write-Verbose "Create a new timespan using `$WSUSCleanComputerObjectCleanupSearchDays and find how many computers need to be cleaned up"
1965 $WSUSCleanComputerObjectCleanupSearchTimeSpan = New-Object timespan($WSUSCleanComputerObjectCleanupSearchDays,0,0,0)
1966 $WSUSCleanComputerObjectCleanupScope = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope
1967 $WSUSCleanComputerObjectCleanupScope.ToLastSyncTime = [DateTime]::UtcNow.Subtract($WSUSCleanComputerObjectCleanupSearchTimeSpan)
1968 $WSUSCleanComputerObjectCleanupSet = $WSUSCleanWSUSServerAdminProxy.GetComputerTargets($WSUSCleanComputerObjectCleanupScope) | Sort-Object FullDomainName
1969 Write-Verbose "Clean up $($WSUSCleanComputerObjectCleanupSet.Count) computer objects"
1970 $WSUSCleanWSUSServerAdminProxy.GetComputerTargets($WSUSCleanComputerObjectCleanupScope) | ForEach-Object { $_.Delete() }
1971
1972 $FinishedRunning = Get-Date
1973 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1974
1975 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1976 $Script:WSUSCleanComputerObjectCleanupOutputTXT += "WSUSClean Computer Object Cleanup:`r`n`r`n"
1977 if ($($WSUSCleanComputerObjectCleanupSet.Count) -gt "0") {
1978 $Script:WSUSCleanComputerObjectCleanupOutputTXT += "The following $($WSUSCleanComputerObjectCleanupSet.Count) $(if ($($WSUSCleanComputerObjectCleanupSet.Count) -eq "1") { "computer" } else { "computers" }) have been removed."
1979 $Script:WSUSCleanComputerObjectCleanupOutputTXT += $WSUSCleanComputerObjectCleanupSet | Select-Object FullDomainName,@{Expression=" "},LastSyncTime | Format-Table -AutoSize | Out-String
1980 } else { $Script:WSUSCleanComputerObjectCleanupOutputTXT += "There are no computers to clean up.`r`n" }
1981
1982 $Script:WSUSCleanComputerObjectCleanupOutputTXT += "WSUSClean Computer Object Cleanup Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1983 $Script:WSUSCleanComputerObjectCleanupOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean Computer Object Cleanup:</span></p>`r`n"
1984 if ($($WSUSCleanComputerObjectCleanupSet.Count) -gt "0") {
1985 $Script:WSUSCleanComputerObjectCleanupOutputHTML += "<p>The following $($WSUSCleanComputerObjectCleanupSet.Count) $(if ($($WSUSCleanComputerObjectCleanupSet.Count) -eq "1") { "computer" } else { "computers" }) have been removed.</p>"
1986 $Script:WSUSCleanComputerObjectCleanupOutputHTML += ($WSUSCleanComputerObjectCleanupSet | Select-Object FullDomainName,LastSyncTime | ConvertTo-Html -Fragment) -replace "\<table\>",'<table class="gridtable">'
1987 } else { $Script:WSUSCleanComputerObjectCleanupOutputHTML += "<p>There are no computers to clean up.</p>" }
1988 $Script:WSUSCleanComputerObjectCleanupOutputHTML += "<p>WSUSClean Computer Object Cleanup Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1989
1990 # Variables Output
1991 # $WSUSCleanComputerObjectCleanupOutputTXT
1992 # $WSUSCleanComputerObjectCleanupOutputHTML
1993}
1994
1995#endregion ComputerObjectCleanup Function
1996
1997#region WSUSServerCleanupWizard Function
1998################################
1999# WSUS Server Cleanup Wizard #
2000# Stream #
2001################################
2002
2003function WSUSServerCleanupWizard {
2004 $DateNow = Get-Date
2005 $WSUSServerCleanupWizardBody = "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUS Server Cleanup Wizard:</span></p>" | Out-String
2006 $CleanupManager = $WSUSCleanWSUSServerAdminProxy.GetCleanupManager();
2007 $CleanupScope = New-Object Microsoft.UpdateServices.Administration.CleanupScope ($WSUSCleanSCWSupersededUpdatesDeclined,$WSUSCleanSCWExpiredUpdatesDeclined,$WSUSCleanSCWObsoleteUpdatesDeleted,$WSUSCleanSCWUpdatesCompressed,$WSUSCleanSCWObsoleteComputersDeleted,$WSUSCleanSCWUnneededContentFiles);
2008 $WSUSCleanCleanupResults = $CleanupManager.PerformCleanup($CleanupScope)
2009 $FinishedRunning = Get-Date
2010 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
2011
2012 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "WSUSClean WSUS Server Cleanup Wizard:`r`n`r`n"
2013 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "$WSUSCleanWSUSServer`r`n"
2014 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "Version: $($WSUSCleanWSUSServerAdminProxy.Version)`r`n"
2015 #$Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "Started: $($DateNow.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))`r`n"
2016 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "SupersededUpdatesDeclined: $($WSUSCleanCleanupResults.SupersededUpdatesDeclined)`r`n"
2017 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "ExpiredUpdatesDeclined: $($WSUSCleanCleanupResults.ExpiredUpdatesDeclined)`r`n"
2018 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "ObsoleteUpdatesDeleted: $($WSUSCleanCleanupResults.ObsoleteUpdatesDeleted)`r`n"
2019 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "UpdatesCompressed: $($WSUSCleanCleanupResults.UpdatesCompressed)`r`n"
2020 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "ObsoleteComputersDeleted: $($WSUSCleanCleanupResults.ObsoleteComputersDeleted)`r`n"
2021 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "DiskSpaceFreed (MB): $([math]::round($WSUSCleanCleanupResults.DiskSpaceFreed/1MB, 2))`r`n"
2022 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "DiskSpaceFreed (GB): $([math]::round($WSUSCleanCleanupResults.DiskSpaceFreed/1GB, 2))`r`n"
2023 #$Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "Finished: $($FinishedRunning.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))`r`n"
2024 $Script:WSUSCleanWSUSServerCleanupWizardOutputTXT += "WSUS Server Cleanup Wizard Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2025
2026 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUSClean WSUS Server Cleanup Wizard:</span></p>`r`n"
2027 #$Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += $WSUSCleanCSSStyling + "`r`n"
2028 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<table class=`"gridtable`">`r`n"
2029 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tbody>`r`n"
2030 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><th colspan=`"2`" rowspan=`"1`">$WSUSCleanWSUSServer</th></tr>`r`n"
2031 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>Version:</td><td>$($WSUSCleanWSUSServerAdminProxy.Version)</td></tr>`r`n"
2032 #$Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>Started:</td><td>$($DateNow.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))</td></tr>`r`n"
2033 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>SupersededUpdatesDeclined:</td><td>$($WSUSCleanCleanupResults.SupersededUpdatesDeclined)</td></tr>`r`n"
2034 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>ExpiredUpdatesDeclined:</td><td>$($WSUSCleanCleanupResults.ExpiredUpdatesDeclined)</td></tr>`r`n"
2035 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>ObsoleteUpdatesDeleted:</td><td>$($WSUSCleanCleanupResults.ObsoleteUpdatesDeleted)</td></tr>`r`n"
2036 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>UpdatesCompressed:</td><td>$($WSUSCleanCleanupResults.UpdatesCompressed)</td></tr>`r`n"
2037 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>ObsoleteComputersDeleted:</td><td>$($WSUSCleanCleanupResults.ObsoleteComputersDeleted)</td></tr>`r`n"
2038 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>DiskSpaceFreed (MB):</td><td>$([math]::round($WSUSCleanCleanupResults.DiskSpaceFreed/1MB, 2))</td></tr>`r`n"
2039 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>DiskSpaceFreed (GB):</td><td>$([math]::round($WSUSCleanCleanupResults.DiskSpaceFreed/1GB, 2))</td></tr>`r`n"
2040 #$Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>Finished:</td><td>$($FinishedRunning.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))</td></tr>`r`n"
2041 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "<tr><td>WSUS Server Cleanup Wizard Duration:</td><td>{0:00}:{1:00}:{2:00}:{3:00}</td></tr>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2042 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "</tbody>`r`n"
2043 $Script:WSUSCleanWSUSServerCleanupWizardOutputHTML += "</table>`r`n"
2044
2045 # Variables Output
2046 # $WSUSCleanWSUSServerCleanupWizardOutputTXT
2047 # $WSUSCleanWSUSServerCleanupWizardOutputHTML
2048}
2049#endregion WSUSServerCleanupWizard Function
2050
2051#region WSUSCleanScriptDifferenceInTime Function
2052function WSUSCleanScriptDifferenceInTime {
2053 $WSUSCleanScriptFinishedRunning = Get-Date
2054 $Script:WSUSCleanScriptDifferenceInTime = New-TimeSpan –Start $WSUSCleanScriptTime –End $WSUSCleanScriptFinishedRunning
2055}
2056#endregion WSUSCleanScriptDifferenceInTime Function
2057
2058#region Create The CSS Styling
2059################################
2060# Create the CSS Styling #
2061################################
2062
2063$WSUSCleanCSSStyling =@"
2064<style type="text/css">
2065table.gridtable {
2066 font-family: verdana,arial,sans-serif;
2067 font-size:11px;
2068 color:#333333;
2069 border-width: 1px;
2070 border-color: #666666;
2071 border-collapse: collapse;
2072}
2073table.gridtable th {
2074 border-width: 1px;
2075 padding: 8px;
2076 border-style: solid;
2077 border-color: #666666;
2078 background-color: #dedede;
2079}
2080table.gridtable td {
2081 border-width: 1px;
2082 padding: 8px;
2083 border-style: solid;
2084 border-color: #666666;
2085 background-color: #ffffff;
2086}
2087.TFtable{
2088 border-collapse:collapse;
2089}
2090.TFtable td{
2091 padding:7px;
2092 border:#4e95f4 1px solid;
2093}
2094
2095/* provide some minimal visual accommodation for IE8 and below */
2096.TFtable tr{
2097 background: #b8d1f3;
2098}
2099/* Define the background color for all the ODD background rows */
2100.TFtable tr:nth-child(odd){
2101 background: #b8d1f3;
2102}
2103/* Define the background color for all the EVEN background rows */
2104.TFtable tr:nth-child(even){
2105 background: #dae5f4;
2106}
2107.error {
2108border: 2px solid;
2109margin: 10px 10px;
2110padding:15px 50px 15px 50px;
2111}
2112.error ol {
2113color: #D8000C;
2114}
2115.error ol li p {
2116color: #000;
2117background-color: transparent;
2118}
2119.error ol li {
2120background-color: #FFBABA;
2121margin: 10px 0;
2122}
2123</style>
2124"@
2125#endregion Create The CSS Styling
2126
2127#region Create The Output
2128################################
2129# Create the TXT output #
2130################################
2131
2132function CreateBodyTXT {
2133 $Script:WSUSCleanBodyTXT = "`n"
2134 $Script:WSUSCleanBodyTXT += $WSUSCleanBodyHeaderTXT
2135 $Script:WSUSCleanBodyTXT += $WSUSCleanConnectedTXT
2136 $Script:WSUSCleanBodyTXT += $WSUSCleanRemoveObsoleteUpdatesOutputTXT
2137 $Script:WSUSCleanBodyTXT += $WSUSCleanCompressUpdateRevisionsOutputTXT
2138 $Script:WSUSCleanBodyTXT += $WSUSCleanDeclineSupersededUpdatesOutputTXT
2139 $Script:WSUSCleanBodyTXT += $WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputTXT
2140 $Script:WSUSCleanBodyTXT += $WSUSCleanRemoveWSUSDriversOutputTXT
2141 $Script:WSUSCleanBodyTXT += $WSUSCleanRemoveDeclinedWSUSUpdatesOutputTXT
2142 $Script:WSUSCleanBodyTXT += $WSUSCleanComputerObjectCleanupOutputTXT
2143 $Script:WSUSCleanBodyTXT += $WSUSCleanWSUSDBMaintenanceOutputTXT
2144 $Script:WSUSCleanBodyTXT += $WSUSCleanWSUSServerCleanupWizardOutputTXT
2145 $Script:WSUSCleanBodyTXT += "Clean-WSUS Script Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($WSUSCleanScriptDifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2146 $Script:WSUSCleanBodyTXT += $WSUSCleanBodyFooterTXT
2147}
2148
2149################################
2150# Create the HTML output #
2151################################
2152
2153function CreateBodyHTML {
2154 $Script:WSUSCleanBodyHTML = "`n"
2155 $Script:WSUSCleanBodyHTML += $WSUSCleanCSSStyling
2156 $Script:WSUSCleanBodyHTML += $WSUSCleanBodyHeaderHTML
2157 $Script:WSUSCleanBodyHTML += $WSUSCleanConnectedHTML
2158 $Script:WSUSCleanBodyHTML += $WSUSCleanRemoveObsoleteUpdatesOutputHTML
2159 $Script:WSUSCleanBodyHTML += $WSUSCleanCompressUpdateRevisionsOutputHTML
2160 $Script:WSUSCleanBodyHTML += $WSUSCleanDeclineSupersededUpdatesOutputHTML
2161 $Script:WSUSCleanBodyHTML += $WSUSCleanCleanUpWSUSSynchronizationLogsSQLOutputHTML
2162 $Script:WSUSCleanBodyHTML += $WSUSCleanRemoveWSUSDriversOutputHTML
2163 $Script:WSUSCleanBodyHTML += $WSUSCleanRemoveDeclinedWSUSUpdatesOutputHTML
2164 $Script:WSUSCleanBodyHTML += $WSUSCleanComputerObjectCleanupOutputHTML
2165 $Script:WSUSCleanBodyHTML += $WSUSCleanWSUSDBMaintenanceOutputHTML
2166 $Script:WSUSCleanBodyHTML += $WSUSCleanWSUSServerCleanupWizardOutputHTML
2167 $Script:WSUSCleanBodyHTML += "<p>Clean-WSUS Script Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($WSUSCleanScriptDifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2168 $Script:WSUSCleanBodyHTML += $WSUSCleanBodyFooterHTML
2169}
2170#endregion Create The Output
2171
2172#region SaveReport
2173################################
2174# Save the Report #
2175################################
2176
2177function SaveReport {
2178 Param(
2179 [ValidateSet("TXT","HTML")]
2180 [String]$ReportType = "TXT"
2181 )
2182 if ($ReportType -eq "HTML") {
2183 $WSUSCleanBodyHTML | Out-File -FilePath "$WSUSCleanScriptPath\$(get-date -f "yyyy.MM.dd-HH.mm.ss").htm"
2184 } else {
2185 $WSUSCleanBodyTXT | Out-File -FilePath "$WSUSCleanScriptPath\$(get-date -f "yyyy.MM.dd-HH.mm.ss").txt"
2186 }
2187}
2188#endregion SaveReport
2189
2190#region MailReport
2191################################
2192# Mail the Report #
2193################################
2194
2195function MailReport {
2196 param (
2197 [ValidateSet("TXT","HTML")]
2198 [String] $MessageContentType = "HTML"
2199 )
2200 $message = New-Object System.Net.Mail.MailMessage
2201 $mailer = New-Object System.Net.Mail.SmtpClient ($WSUSCleanMailReportSMTPServer, $WSUSCleanMailReportSMTPPort)
2202 $mailer.EnableSSL = $WSUSCleanMailReportSMTPServerEnableSSL
2203 if ($WSUSCleanMailReportSMTPServerUsername -ne "") {
2204 $mailer.Credentials = New-Object System.Net.NetworkCredential($WSUSCleanMailReportSMTPServerUsername, $WSUSCleanMailReportSMTPServerPassword)
2205 }
2206 $message.From = $WSUSCleanMailReportEmailFromAddress
2207 $message.To.Add($WSUSCleanMailReportEmailToAddress)
2208 $message.Subject = $WSUSCleanMailReportEmailSubject
2209 $message.Body = if ($MessageContentType -eq "HTML") { $WSUSCleanBodyHTML } else { $WSUSCleanBodyTXT }
2210 $message.IsBodyHtml = if ($MessageContentType -eq "HTML") { $True } else { $False }
2211 $mailer.send(($message))
2212}
2213#endregion MailReport
2214
2215#region HelpMe
2216################################
2217# Help Me #
2218################################
2219
2220function HelpMe {
2221 ((Get-CimInstance Win32_OperatingSystem) | Format-List @{Name="OS Name";Expression={$_.Caption}}, @{Name="OS Architecture";Expression={$_.OSArchitecture}}, @{Name="Version";Expression={$_.Version}}, @{Name="ServicePackMajorVersion";Expression={$_.ServicePackMajorVersion}}, @{Name="ServicePackMinorVersion";Expression={$_.ServicePackMinorVersion}} | Out-String).Trim()
2222 Write-Output "PowerShell Version: $($PSVersionTable.PSVersion.ToString())"
2223 Write-Output "WSUS Version: $($WSUSCleanWSUSServerAdminProxy.Version)"
2224 Write-Output "Replica Server: $($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer)"
2225 Write-Output "The path to the WSUS Content folder is: $($WSUSCleanWSUSServerAdminProxy.GetConfiguration().LocalContentCachePath)"
2226 Write-Output "Free Space on the WSUS Content folder Volume is: $((Get-DiskFree -Format | ? { $_.Type -like '*fixed*' } | Where-Object { ($_.Vol -eq ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().LocalContentCachePath).split("\")[0]) }).Avail)"
2227 Write-Output "All Volumes on the WSUS Server:"
2228 (Get-DiskFree -Format | Out-String).Trim()
2229 Write-Output ".NET Installed Versions"
2230 (Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -Recurse | Get-ItemProperty -Name Version -EA 0 | Where { $_.PSChildName -Match '^(?!S)\p{L}'} | Format-Table PSChildName, Version -AutoSize | Out-String).Trim()
2231 Write-Output "============================="
2232 Write-Output "All My Functions"
2233 Write-Output "============================="
2234 Show-MyFunctions
2235 Write-Output "============================="
2236 Write-Output "All My Variables"
2237 Write-Output "============================="
2238 Show-MyVariables
2239 Write-Output "============================="
2240 Write-Output " End of HelpMe Stream"
2241 Write-Output "============================="
2242
2243}
2244#endregion HelpMe
2245
2246#region Process The Functions
2247################################
2248# Process the Functions #
2249################################
2250
2251if ($FirstRun -eq $True) {
2252 CreateWSUSCleanHeader
2253 Write-Output "Executing RemoveWSUSDrivers"
2254 RemoveWSUSDrivers -SQL
2255 Write-Output "Executing RemoveObsoleteUpdates"
2256 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { RemoveObsoleteUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't remove obsolete updates from a replica server. Skipping this stream."}
2257 Write-Output "Executing CompressUpdateRevisions"
2258 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { CompressUpdateRevisions } else { Write-Output "This WSUS Server is a Replica Server. You can't compress update revisions from a replica server. Skipping this stream."}
2259 Write-Output "Executing DeclineSupersededUpdates"
2260 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Output "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
2261 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
2262 if ($WSUSCleanCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyTime }
2263 if ($WSUSCleanComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
2264 Write-Output "Executing WSUSDBMaintenance"
2265 WSUSDBMaintenance
2266 Write-Output "Executing WSUSServerCleanupWizard"
2267 WSUSServerCleanupWizard
2268 CreateWSUSCleanFooter
2269 WSUSCleanScriptDifferenceInTime
2270 CreateBodyTXT
2271 CreateBodyHTML
2272 if ($WSUSCleanMailReport -eq $True) { MailReport $WSUSCleanMailReportType }
2273 SaveReport
2274}
2275if ($MonthlyRun -eq $True) {
2276 CreateWSUSCleanHeader
2277 Write-Output "Executing RemoveObsoleteUpdates"
2278 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { RemoveObsoleteUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't remove obsolete updates from a replica server. Skipping this stream."}
2279 Write-Output "Executing CompressUpdateRevisions"
2280 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { CompressUpdateRevisions } else { Write-Output "This WSUS Server is a Replica Server. You can't compress update revisions from a replica server. Skipping this stream."}
2281 Write-Output "Executing DeclineSupersededUpdates"
2282 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Output "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
2283 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
2284 if ($WSUSCleanCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyTime }
2285 if ($WSUSCleanComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
2286 Write-Output "Executing WSUSDBMaintenance"
2287 WSUSDBMaintenance
2288 Write-Output "Executing WSUSServerCleanupWizard"
2289 WSUSServerCleanupWizard
2290 CreateWSUSCleanFooter
2291 WSUSCleanScriptDifferenceInTime
2292 CreateBodyTXT
2293 CreateBodyHTML
2294 if ($WSUSCleanMailReport -eq $True) { MailReport $WSUSCleanMailReportType }
2295 if ($WSUSCleanSaveReport -eq $True) { SaveReport $WSUSCleanSaveReportType }
2296}
2297if ($QuarterlyRun -eq $True) {
2298 CreateWSUSCleanHeader
2299 Write-Output "Executing RemoveObsoleteUpdates"
2300 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { RemoveObsoleteUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't remove obsolete updates from a replica server. Skipping this stream."}
2301 Write-Output "Executing CompressUpdateRevisions"
2302 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { CompressUpdateRevisions } else { Write-Output "This WSUS Server is a Replica Server. You can't compress update revisions from a replica server. Skipping this stream."}
2303 Write-Output "Executing DeclineSupersededUpdates"
2304 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Output "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
2305 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
2306 if ($WSUSCleanCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyTime }
2307 Write-Output "Executing RemoveWSUSDrivers"
2308 RemoveWSUSDrivers
2309 Write-Output "Executing RemoveDeclinedWSUSUpdates"
2310 RemoveDeclinedWSUSUpdates -Display -Proceed
2311 if ($WSUSCleanComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
2312 Write-Output "Executing WSUSDBMaintenance"
2313 WSUSDBMaintenance
2314 Write-Output "Executing WSUSServerCleanupWizard"
2315 WSUSServerCleanupWizard
2316 CreateWSUSCleanFooter
2317 WSUSCleanScriptDifferenceInTime
2318 CreateBodyTXT
2319 CreateBodyHTML
2320 if ($WSUSCleanMailReport -eq $True) { MailReport $WSUSCleanMailReportType }
2321 if ($WSUSCleanSaveReport -eq $True) { SaveReport $WSUSCleanSaveReportType }
2322}
2323if ($ScheduledRun -eq $True) {
2324 $DateNow = Get-Date
2325 CreateWSUSCleanHeader
2326 if ($WSUSCleanScheduledRunStreamsDay -gt 31 -or $WSUSCleanScheduledRunStreamsDay -eq 0) { Write-Output 'You failed to set a valid value for $WSUSCleanScheduledRunStreamsDay. Setting to 31'; $WSUSCleanScheduledRunStreamsDay = 31 }
2327 if ($WSUSCleanScheduledRunStreamsDay -eq $DateNow.Day) { Write-Output "Executing RemoveObsoleteUpdates"; if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { RemoveObsoleteUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't remove obsolete updates from a replica server. Skipping this stream."} }
2328 if ($WSUSCleanScheduledRunStreamsDay -eq $DateNow.Day) { Write-Output "Executing CompressUpdateRevisions"; if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { CompressUpdateRevisions } else { Write-Output "This WSUS Server is a Replica Server. You can't compress update revisions from a replica server. Skipping this stream."} }
2329 Write-Output "Executing DeclineSupersededUpdates"
2330 if ($WSUSCleanScheduledRunStreamsDay -eq $DateNow.Day) { if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Output "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."} } else { if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."} }
2331 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
2332 if ($WSUSCleanCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyTime }
2333 $WSUSCleanScheduledRunQuarterlyMonths.Split(",") | ForEach-Object {
2334 if ($_ -eq $DateNow.Month) {
2335 if ($_ -eq 2) {
2336 if ($WSUSCleanScheduledRunStreamsDay -gt 28 -and [System.DateTime]::isleapyear($DateNow.Year) -eq $True) { $WSUSCleanScheduledRunStreamsDay = 29 }
2337 else { $WSUSCleanScheduledRunStreamsDay = 28 }
2338 }
2339 if (4,6,9,11 -contains $_ -and $WSUSCleanScheduledRunStreamsDay -gt 30) { $WSUSCleanScheduledRunStreamsDay = 30 }
2340 if ($WSUSCleanScheduledRunStreamsDay -eq $DateNow.Day) {
2341 Write-Output "Executing RemoveWSUSDrivers"
2342 RemoveWSUSDrivers
2343 Write-Output "Executing RemoveDeclinedWSUSUpdates"
2344 RemoveDeclinedWSUSUpdates -Display -Proceed
2345 }
2346 }
2347 }
2348 if ($WSUSCleanComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
2349 Write-Output "Executing WSUSDBMaintenance"
2350 if ($WSUSCleanScheduledRunStreamsDay -eq $DateNow.Day) { WSUSDBMaintenance } else { WSUSDBMaintenance -NoOutput }
2351 Write-Output "Executing WSUSServerCleanupWizard"
2352 WSUSServerCleanupWizard
2353 CreateWSUSCleanFooter
2354 WSUSCleanScriptDifferenceInTime
2355 CreateBodyTXT
2356 CreateBodyHTML
2357 if ($WSUSCleanMailReport -eq $True) { MailReport $WSUSCleanMailReportType }
2358 if ($WSUSCleanSaveReport -eq $True) { SaveReport $WSUSCleanSaveReportType }
2359}
2360
2361if ($DailyRun -eq $True) {
2362 CreateWSUSCleanHeader
2363 Write-Output "Executing DeclineSupersededUpdates"
2364 if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
2365 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
2366 if ($WSUSCleanCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyTime }
2367 if ($WSUSCleanComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
2368 Write-Output "Executing WSUSDBMaintenance"
2369 WSUSDBMaintenance -NoOutput
2370 Write-Output "Executing WSUSServerCleanupWizard"
2371 WSUSServerCleanupWizard
2372 CreateWSUSCleanFooter
2373 WSUSCleanScriptDifferenceInTime
2374 CreateBodyTXT
2375 CreateBodyHTML
2376 if ($WSUSCleanMailReport -eq $True) { MailReport $WSUSCleanMailReportType }
2377 if ($WSUSCleanSaveReport -eq $True) { SaveReport $WSUSCleanSaveReportType }
2378}
2379
2380if (-not $FirstRun -and -not $MonthlyRun -and -not $QuarterlyRun -and -not $ScheduledRun -and -not $DailyRun) {
2381 Write-Verbose "All pre-defined routines (-FirstRun, -DailyRun, -MonthlyRun, -QuarterlyRun, -ScheduledRun) were not specified"
2382 CreateWSUSCleanHeader
2383 if ($RemoveWSUSDriversSQL -eq $True) { Write-Output "Executing RemoveWSUSDrivers using SQL"; RemoveWSUSDrivers -SQL }
2384 if ($RemoveWSUSDriversPS -eq $True) { Write-Output "Executing RemoveWSUSDrivers using Powershell"; RemoveWSUSDrivers }
2385 if ($RemoveObsoleteUpdates -eq $True) { Write-Output "Executing RemoveObsoleteUpdates using SQL"; if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { RemoveObsoleteUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't remove obsolete updates from a replica server. Skipping this stream." } }
2386 if ($CompressUpdateRevisions -eq $True) { Write-Output "Executing CompressUpdateRevisions using SQL"; if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { CompressUpdateRevisions } else { Write-Output "This WSUS Server is a Replica Server. You can't compress update revisions from a replica server. Skipping this stream." } }
2387 if ($RemoveDeclinedWSUSUpdates -eq $True) { Write-Output "Executing RemoveDeclinedWSUSUpdates"; RemoveDeclinedWSUSUpdates -Display -Proceed }
2388 if ($WSUSDBMaintenance -eq $True) { Write-Output "Executing WSUSDBMaintenance"; WSUSDBMaintenance }
2389 if ($DeclineSupersededUpdates -eq $True) { Write-Output "Executing DeclineSupersededUpdates"; if ($WSUSCleanWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Output "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream." } }
2390 if ($CleanUpWSUSSynchronizationLogs -eq $True) { Write-Output "Executing CleanUpWSUSSynchronizationLogs"; if ($WSUSCleanCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $WSUSCleanCleanUpWSUSSynchronizationLogsConsistencyTime } }
2391 if ($ComputerObjectCleanup -eq $True -and $WSUSCleanComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
2392 if ($WSUSServerCleanupWizard -eq $True) { Write-Output "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard }
2393 CreateWSUSCleanFooter
2394 WSUSCleanScriptDifferenceInTime
2395 CreateBodyTXT
2396 CreateBodyHTML
2397 if ($SaveReport -eq "TXT") { SaveReport }
2398 if ($SaveReport -eq "HTML") { SaveReport -ReportType "HTML" }
2399 if ($MailReport -eq "HTML") { MailReport }
2400 if ($MailReport -eq "TXT") { MailReport -MessageContentType "TXT" }
2401}
2402
2403if ($HelpMe -eq $True) {
2404 HelpMe
2405}
2406if ($DisplayApplicationPoolMemory -eq $True) {
2407 ApplicationPoolMemory
2408}
2409if ($IncreaseApplicationPoolMemory) {
2410 ApplicationPoolMemory -IncreaseApplicationPoolBy $IncreaseApplicationPoolMemory
2411}
2412
2413#endregion ProcessTheFunctions
2414<#
2415# All Possible Function Calls
2416
2417CreateWSUSCleanHeader
2418RemoveWSUSDrivers -SQL
2419 RemoveWSUSDriversSQL
2420 RemoveWSUSDriversPS
2421RemoveDeclinedWSUSUpdates -Display -Proceed
2422 RemoveDeclinedWSUSUpdatesProceed
2423 RemoveDeclinedWSUSUpdatesDisplayUpdates
2424 RemoveDeclinedWSUSUpdatesCountUpdates
2425CompressUpdateRevisions
2426RemoveObsoleteUpdates
2427WSUSDBMaintenance -NoOutput
2428DeclineSupersededUpdates -Display -Proceed
2429 DeclineSupersededUpdatesProceed
2430 DeclineSupersededUpdatesDisplayUpdates
2431 DeclineSupersededUpdatesCountUpdates
2432CleanUpWSUSSynchronizationLogs -ConsistencyNumber "14" -ConsistencyTime "Day" -All
2433ComputerObjectCleanup
2434WSUSServerCleanupWizard
2435CreateWSUSCleanFooter
2436CreateBodyTXT
2437CreateBodyHTML
2438SaveReport -ReportType
2439MailReport -MessageContentType
2440HelpMe
2441ApplicationPoolMemory -IncreaseApplicationPoolBy 1024
2442Install-Task
2443#>
2444}
2445
2446End {
2447 if ($HelpMe -eq $True) { $VerbosePreference = $WSUSCleanOldVerbose; Stop-Transcript }
2448 Write-Verbose "End Of Code"
2449}
2450################################
2451# End Of Code #
2452################################
2453#EOF