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