· 7 years ago · Feb 23, 2019, 04:26 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
2513RAW Paste Data
2514#Requires –Version 3.0
2515################################
2516# Adamj Clean-WSUS #
2517# Version 2.11 #
2518# #
2519# The last WSUS Script you #
2520# will ever need! #
2521# #
2522# Taken from various sources #
2523# from the Internet. #
2524# #
2525# Modified By: Adam Marshall #
2526# http://www.adamj.org #
2527################################
2528<#
2529################################
2530# Prerequisites #
2531################################
2532
25331. This script has to be saved as plain text in ANSI format. If you use Notepad++, you must
2534 change the encoding to ANSI (Encoding > 'Encode in ANSI' or Encode > 'Convert to ANSI').
2535 An easy way to tell if it is saved in plain text (ANSI) format is that there is a #Requires
2536 statement at the top of the script. Make sure that there is a hyphen before the word
2537 "Version" and you shouldn't have a problem with executing it. If you end up with an error
2538 like below, it is due to the encoding of the file as you can tell by the – characters
2539 before the word Version.
2540
2541 At C:\Scripts\Clean-WSUS.ps1:1 char:13
2542 + #Requires –Version 3.0
2543
25442. You must run this on the WSUS Server itself and any downstream WSUS servers you may have.
2545
25463. On the WSUS Server, you must install the SQL Server Management Studio (SSMS) from Microsoft
2547 so that you have the SQLCMD utility. The SSMS is not a requirement but rather a good tool for
2548 troubleshooting if needed. The bare minimum requirement is the Microsoft Command Line
2549 Utilities for SQL Server at whatever version yours is.
2550
25514. You must have Powershell 3.0 or higher installed. I recommend version 4.0 or higher.
2552
2553 Prerequesite Downloads
2554 ----------------------
2555
2556 - For Server 2008 SP2:
2557 - Install Windows Powershell from Server Manager - Features
2558 - Install .NET 3.5 SP1 from - https://www.microsoft.com/en-ca/download/details.aspx?id=25150
2559 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
2560 You want to choose SQLManagementStudio_x64_ENU.exe
2561 - Install .NET 4.0 - https://www.microsoft.com/en-us/download/details.aspx?id=17718
2562 - Install Powershell 2.0 & WinRM 2.0 from https://www.microsoft.com/en-ca/download/details.aspx?id=20430
2563 - Install Windows Management Framework 3.0 from https://www.microsoft.com/en-us/download/confirmation.aspx?id=34595
2564
2565 - For Server 2008 R2:
2566 - Install .NET 4.5.2 from https://www.microsoft.com/en-ca/download/details.aspx?id=42642
2567 - Install Windows Management Framework 4.0 and reboot from https://www.microsoft.com/en-ca/download/details.aspx?id=40855
2568 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
2569 You want to choose SQLManagementStudio_x64_ENU.exe
2570
2571 - For SBS:
2572 - This script WILL work on SBS - you must install the pre-requisites above depending on your underlying OS. .NET 4 is
2573 backwards compatible and I have a lot of users who have installed it on SBS and use the script.
2574
2575 - For Server 2012 & 2012 R2
2576 - Install SQL Server Management Studio from https://www.microsoft.com/en-us/download/details.aspx?id=29062
2577 You want to choose the ENU\x64\SQLManagementStudio_x64_ENU.exe
2578
2579 - For Server 2016
2580 - I've not personally tested this on server 2016, however many people have run it without issues on Server 2016.
2581 I don't think Microsoft has changed much between 2012 R2 WSUS and 2016 WSUS.
2582 - Install SQL Server Management Studio from https://msdn.microsoft.com/library/mt238290.aspx
2583
2584 IF YOU DON'T WANT TO INSTALL SQL SERVER MANAGEMENT STUDIO:
2585 Microsoft Command Line Utilities for SQL Server (Minimum requirement instead of SQL Server Management Studio)
2586 SQL 2008/2008R2 - https://www.microsoft.com/en-ca/download/details.aspx?id=16978
2587 SQL 2012/2014 - Version 11 - https://www.microsoft.com/en-us/download/details.aspx?id=36433
2588 SQL 2016 - Version 13 - https://www.microsoft.com/en-us/download/details.aspx?id=53591
2589
2590################################
2591# Instructions #
2592################################
2593
2594 1. Edit the variables below to match your environment.
2595 2. Open PowerShell using "Run As Administrator" on the WSUS Server.
2596 3. Because you downloaded this script from the internet, you cannot initially run it directly
2597 as the ExecutionPolicy is default set to "Restricted" (Server 2008, Server 2008 R2, and
2598 Server 2012) or "RemoteSigned" (Server 2012 R2). You must change your ExecutionPolicy to
2599 Bypass. You can do this with Set-ExecutionPolicy, however that will change it globally for
2600 the server, which is not recommended. Instead, launch another PowerShell.exe with the
2601 ExecutionPolicy set to bypass for just that session. At your current PowerShell prompt,
2602 type in the following and then press enter:
2603
2604 PowerShell.exe -ExecutionPolicy Bypass
2605
2606 3. Run the script using -FirstRun.
2607
2608 .\Clean-WSUS.ps1 -FirstRun
2609
2610 4. Run the script using -InstallTask to install the Scheduled Task.
2611
2612 .\Clean-WSUS.ps1 -InstallTask
2613
2614You can use Get-Help .\Clean-WSUS.ps1 for more information.
2615#>
2616
2617<#
2618.SYNOPSIS
2619This 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.
2620
2621.DESCRIPTION
2622################################
2623# Background Information #
2624# on Streams #
2625################################
2626
2627All my recommendations are set in -ScheduledRun.
2628
2629Adamj Remove WSUS Drivers Stream
2630-----------------------------------------------------
2631
2632This stream will remove all WSUS Drivers Classifications from the WSUS database.
2633This has 2 possible running methods - Run through PowerShell, or Run directly in SQL.
2634The -FirstRun Switch will force the SQL method, but all other automatic runs will use the
2635PowerShell method. I recommend this be done every quarter.
2636
2637You can use -RemoveWSUSDriversSQL or -RemoveWSUSDriversPS to run these manually from the command-line.
2638
2639Adamj Remove Declined WSUS Updates Stream
2640-----------------------------------------------------
2641
2642This stream will remove any Declined WSUS updates from the WSUS Database. This is good if you are removing
2643Specific products (Like Server 2003 / Windows XP updates) from the WSUS server under the Products and
2644Classifications section. Since this will remove them from the database, if they are still valid,
2645the next synchronizations will pick up the updates again. I recommend that this be done every quarter.
2646This stream is NOT included on -FirstRun on purpose.
2647
2648You can use -RemoveDeclinedWSUSUpdates to run this manually from the command-line.
2649
2650Adamj Compress Update Revisions Stream
2651-----------------------------------------------------
2652
2653This stream will use SQL code to execute pre-existing stored procedures that will return the update id
2654of each update revision that needs compressing and then compress it. I recommend that this be done
2655monthly.
2656
2657You can use -CompressUpdateRevisions to run this manually from the command-line.
2658
2659Adamj Remove Obsolete Updates Stream
2660-----------------------------------------------------
2661
2662This stream will use SQL code to execute pre-existing stored procedures that will return the update id
2663of each obsolete update in the database and then remove it. There is no magic number of obsolete updates
2664that will cause the server to time-out. Running this stream can easily take several hours to delete the
2665updates. While the process is running you might see WSUS synchronization errors. I recommend that this
2666be done monthly.
2667
2668You can use -RemoveObsoleteUpdates to run this manually from the command-line.
2669
2670Adamj WSUS Database Maintenance Stream
2671-----------------------------------------------------
2672
2673This stream will perform basic maintenance tasks on SUSDB, the WSUS Database. It will identify indexes
2674that are fragmented and defragment them. For certain tables, a fill-factor is set in order to improve
2675insert performance. It will then update potentially out-of-date table statistics. I recommend that this
2676be done daily.
2677
2678You can use -WSUSDBMaintenance to run this manually from the command-line.
2679
2680Adamj Decline Superseded Updates Stream
2681-----------------------------------------------------
2682
2683This stream will decline any update that is superseded and not yet declined. This is a BONUS cleanup for
2684shrinking down the size of your WSUS Server. Any update that has been superseded but has not been declined
2685is using extra space. This will save you GB of data in your WsusContent folder. I recommend that this be
2686done every month.
2687
2688You can use -DeclineSupersededUpdates to run this manually from the command-line.
2689
2690### Please read the background information below for more details. ###
2691
2692The Server Cleanup Wizard (SCW) declines superseded updates, only if:
2693
2694 The newest update is approved, and
2695 The superseded updates are Not Approved, and
2696 The superseded update has not been reported as NotInstalled (i.e. Needed) by any computer in the previous 30 days.
2697
2698There is no feature in the product to automatically decline superseded updates on approval of the newer update,
2699and in fact, you really do not want that feature. The "Best Practice" in dealing with this situation is:
2700
27011. Approve the newer update.
27022. Verify that all systems have installed the newer update.
27033. Verify that all systems now report the superseded update as Not Applicable.
27044. THEN it is safe to decline the superseded update.
2705
2706To SEARCH for superseded updates, you need only enable the Superseded flag column in the All Updates view, and sort on that column.
2707
2708There will be four groups:
2709
27101. Updates which have never been superseded (blank icon).
27112. Updates which have been superseded, but have never superseded another update (icon with blue square at bottom).
27123. Updates which have been superseded and have superseded another update (icon with blue square in middle).
27134. Updates which have superseded another update (icon with blue square at top).
2714
2715There's no way to filter based on the approval status of the updates in group #4, but if you've verified that all
2716necessary/applicable updates in group #4 are approved and installed, then you'd be free to decline groups #2 and #3 en masse.
2717
2718If you decline superseded updates using the method described:
2719
27201. Approve the newer update.
27212. Verify that all systems have installed the newer update.
27223. Verify that all systems now report the superseded update as Not Applicable.
27234. THEN it is safe to decline the superseded update.
2724
2725### THIS SCRIPT DOES NOT FOLLOW THE ABOVE GUIDELINES. IT WILL JUST DECLINE ANY SUPERSEDED UPDATES. ###
2726
2727Adamj Clean Up WSUS Synchronization Logs Stream
2728-----------------------------------------------------
2729
2730This stream will remove all synchronization logs beyond a specified time period. WSUS is lacking the ability
2731to remove synchronization logs through the GUI. Your WSUS server will become slower and slower loading up
2732the synchronization logs view as the synchronization logs will just keep piling up over time. If you have
2733your synchronization settings set to synchronize 4 times a day, it would take less than 3 months before you
2734have over 300 logs that it has to load for the view. This is very time consuming and many just ignore this
2735view and rarely go to it. When they accidentally click on it, they curse. I recommend that this be done daily.
2736
2737You can use -CleanUpWSUSSynchronizationLogs to run this manually from the command-line.
2738
2739Adamj Computer Object Cleanup Stream
2740-----------------------------------------------------
2741
2742This stream will find all computers that have not syncronized with the server within a certain time period
2743and remove them. This is usually done through the Server Cleanup Wizard (SCW), however the SCW has been
2744hardcoded to 30 days. I've setup this stream to be configurable. You can also tell it not to delete any
2745computer objects if you really want to. The default I've kept at 30 days. I recommend that this be done daily.
2746
2747You can use -ComputerObjectCleanup to run this manually from the command-line.
2748
2749Adamj Server Cleanup Wizard Stream
2750-----------------------------------------------------
2751
2752The Server Cleanup Wizard (SCW) is integrated into the WSUS GUI, and can be used to help you manage your
2753disk space. This runs the SCW through PowerShell which has the added bonus of not timing out as often
2754the SCW GUI would.
2755
2756This wizard can do the following things:
2757 - Remove unused updates and update revisions
2758 The wizard will remove all older updates and update revisions that have not been approved.
2759
2760 - Delete computers not contacting the server
2761 The wizard will delete all client computers that have not contacted the server in thirty days or more.
2762
2763 - Delete unneeded update files
2764 The wizard will delete all update files that are not needed by updates or by downstream servers.
2765
2766 - Decline expired updates
2767 The wizard will decline all updates that have been expired by Microsoft.
2768
2769 - Decline superseded updates
2770 The wizard will decline all updates that meet all the following criteria:
2771 The superseded update is not mandatory
2772 The superseded update has been on the server for thirty days or more
2773 The superseded update is not currently reported as needed by any client
2774 The superseded update has not been explicitly deployed to a computer group for ninety days or more
2775 The superseding update must be approved for install to a computer group
2776
2777I recommend that this be done daily.
2778
2779You can use -WSUSServerCleanupWizard to run this manually from the command-line.
2780
2781Adamj Application Pool Memory Configuration Stream
2782-----------------------------------------------------
2783Why does the WSUS Application pool crash and how can we fix it? The WSUS Application pool has a
2784"private memory limit" setting that is configured by default to a low number based on RAM. The
2785Application pool crashes because it can't keep up and the limit is reached. So why couldn't the WSUS
2786Application pool keep up? This has to do with the larger number of updates in the Update Catalog
2787(database) which continues to grow over time. WSUS does not handle an excessive number of updates well
2788and as as the number increases, the load on the application pool increases causing it to slowly run out
2789of memory until the limit is hit and WSUS crashes. I've seen it start having issues above the low
2790number of 10,000 updates and above the high number of 100,000 updates. The number of updates can in
2791part be due to obsolete updates that remain in the database and it varies in every system and
2792implementation. In order to help alleviate this, we can increase the memory on the WSUS Application Pool.
2793
2794I recommend that this be done manually, only if necessary, by the command-line.
2795
2796-DisplayApplicationPoolMemory to display the current application pool memory.
2797-IncreaseApplicationPoolMemory <number in MB> to increase the current private memory limit by the number specified.
2798
2799.NOTES
2800Name: Clean-WSUS
2801Author: Adam Marshall
2802Website: http://www.adamj.org
2803Donations Accepted: http://www.adamj.org/clean-wsus/donate.html
2804
2805This script has been tested on Server 2008 SP2, Server 2008 R2, Server 2012, and Server 2012 R2. This script should run
2806fine on Server 2016 and others have ran it with success on 2016, but I have not had the ability to test it in production.
2807
2808################################
2809# Version History & #
2810# Release Notes #
2811################################
2812
2813Previous Version History - http://www.adamj.org/clean-wsus/release-notes.html
2814
2815 Version 2.07 to 2.08 (Not Released)
2816 - Re-adjusted all Write-Host to Write-Output.
2817 - Changed $AdamjScriptPath from split-path -parent $MyInvocation.MyCommand.Definition to Split-Path $script:MyInvocation.MyCommand.Path.
2818 - Changed $VerbosePreference to "Continue" when executing -HelpMe.
2819 - Added Begin, Process, End operators.
2820 - Setup -HelpMe to change VerbosePreference to continue and change it back at the end to what it was before.
2821 - Changed Test-Administrator to streamline the process.
2822 - Changed SQL-Ping-Instance to Test-SQLConnection and cleaned up the code.
2823 - Added some VERBOSE output throughout the script.
2824 - Fixed a bug with $ExceptionError in the RemoveDeclinedWSUSUpdatesProceed function.
2825 - Adjusted the prerequesites and added SQL Cmd line tools requirement with links.
2826 - Fixed a bug with RemoveDeclinedWSUSUpdates, thanks to Nikolay Semov (Nikolay8159) from the Spiceworks forums.
2827 - Added regions to each section for ease of modification and readability in PowerShell ISE and other editors.
2828
2829 Version 2.08 to 2.09 (Not Released)
2830 - Added CompressUpdateRevisions and RemoveObsoleteUpdates SQL Scripts as MonthlyRun items along with FirstRun.
2831 - Added Configuration for Mail Report and Save Report options.
2832 - Added Donation links.
2833 - Fixed bug with running script from a folder with a space.
2834
2835 Version 2.09 to 2.10 (Not Released)
2836 - Added the Adamj Application Pool Memory Configuration Stream.
2837 - Added information about Server 2016 at the prerequisites stage.
2838 - Added Start-Transcript to -HelpMe stream as now everything is outputted to objects, not just Write-Host.
2839 - Added Show-MyFunctions and Show-MyVariables and added them to the -HelpMe output instead of the contstraint to just Adamj Variables.
2840 - Removed the Clean Up Variables section at the end. Once the script finishes running all variables within the script are destroyed
2841 as none are set globally.
2842 - Changed $AdamjWSUSServer to auto-populate vs manual entry, along with changing it to lowercase within the script.
2843 - Added comments for Gmail settings.
2844 - Added comments in the SQL Server Variable section for the auto-detect issue on Server 2008 (R2).
2845 - Added an SBS Section to the prerequisites.
2846 - Created a function for Connect-WSUSServer.
2847 - Created a function for -InstallTask and a check for powershell version 4 or higher within. Adjusted the Instructions at the top.
2848
2849 Version 2.10 to 2.11
2850 - Re-organized Run switches.
2851 - Added a Restart of the Application pool if you increase the application pool to make sure the settings take effect.
2852 - Added logic to not connect to the WSUS server if not actually running something that requires it (Display & Increase the
2853 application pool and InstallTask).
2854 - Added a configuration value for $AdamjScheduledTaskTime.
2855 - Added a ComputerObjectCleanup Stream for more control over computer object removals.
2856 - Cleaned up old commented code and spacing.
2857 - Added and changed some commented code to verbose output.
2858 - Removed all the version history except what is different from the last released version and put it on my website instead.
2859
2860.EXAMPLE
2861Clean-WSUS -FirstRun
2862Description: Run the routines that are recommended for running this script for the first time.
2863
2864.EXAMPLE
2865Clean-WSUS -InstallTask
2866Description: Install the Scheduled task to run this script at 8AM daily with the -ScheduledRun switch.
2867
2868.EXAMPLE
2869Clean-WSUS -HelpMe
2870Description: Run the HelpMe stream to create a transcript of the session and provide troubleshooting information in a log file.
2871
2872.EXAMPLE
2873Clean-WSUS -DisplayApplicationPoolMemory
2874Description: Display the current Private Memory Limit for the WSUS Application Pool
2875
2876.EXAMPLE
2877Clean-WSUS -IncreaseApplicationPoolMemory 2048
2878Description: Increase the current Private Memory Limit for the WSUS Application Pool by 2048 MB (2GB)
2879
2880.EXAMPLE
2881Clean-WSUS -DailyRun
2882Description: Run the recommended daily routines.
2883
2884.EXAMPLE
2885Clean-WSUS -MonthlyRun
2886Description: Run the recommended monthly routines.
2887
2888.EXAMPLE
2889Clean-WSUS -QuarterlyRun
2890Description: Run the recommended quarterly routines.
2891
2892.EXAMPLE
2893Clean-WSUS -ScheduledRun
2894Description: Run the recommended routines on a schedule having the script take care of all timetables.
2895
2896.EXAMPLE
2897Clean-WSUS -RemoveWSUSDriversSQL -SaveReport TXT
2898Description: 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.
2899
2900.EXAMPLE
2901Clean-WSUS -RemoveWSUSDriversPS -MailReport HTML
2902Description: Only Remove WSUS Drivers by way of PowerShell and email the output as HTML to the configured parties.
2903
2904.EXAMPLE
2905Clean-WSUS -RemoveDeclinedWSUSUpdates -CleanUpWSUSSynchronizationLogs -WSUSDBMaintenance -WSUSServerCleanupWizard -SaveReport HTML -MailReport TXT
2906Description: 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.
2907
2908.EXAMPLE
2909Clean-WSUS -DeclineSupersededUpdates -ComputerObjectCleanup -SaveReport TXT -MailReport HTML
2910Description: 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.
2911
2912.EXAMPLE
2913Clean-WSUS -RemoveObsoleteUpdates -CompressUpdateRevisions -DeclineSupersededUpdates -SaveReport TXT -MailReport HTML
2914Description: 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.
2915
2916.LINK
2917http://www.adamj.org
2918http://community.spiceworks.com/scripts/show/2998-adamj-wsus-cleanup
2919http://www.adamj.org/clean-wsus/donate.html
2920#>
2921################################
2922# Script Setup Parameters #
2923# #
2924# DO NOT EDIT!!! SCROLL DOWN #
2925# TO FIND THE VARIABLES #
2926# TO EDIT #
2927################################
2928[CmdletBinding()]
2929param (
2930 # Run the routines that are recommended for running this script for the first time.
2931 [Switch]$FirstRun,
2932 # Install the Scheduled Task for daily @ 8AM.
2933 [Switch]$InstallTask,
2934 # Run the troubleshooting HelpMe stream to copy and paste for getting support.
2935 [Switch]$HelpMe,
2936 # Display the Application Pool Memory Limit
2937 [switch]$DisplayApplicationPoolMemory,
2938 # Increase or display the Application Pool Memory Limit.
2939 [ValidateRange([int]::MinValue,[int]::MaxValue)]
2940 [Int16]$IncreaseApplicationPoolMemory,
2941 # Run the recommended daily routines.
2942 [Switch]$DailyRun,
2943 # Run the recommended monthly routines.
2944 [Switch]$MonthlyRun,
2945 # Run the recommended quarterly routines.
2946 [Switch]$QuarterlyRun,
2947 # Run the recommended routines on a schedule having the script take care of all timetables.
2948 [Switch]$ScheduledRun,
2949 # Remove WSUS Drivers by way of SQL.
2950 [Switch]$RemoveWSUSDriversSQL,
2951 # Remove WSUS Drivers by way of PowerShell.
2952 [Switch]$RemoveWSUSDriversPS,
2953 # Compress Update Revisions by way of SQL.
2954 [Switch]$CompressUpdateRevisions,
2955 # Remove Obsolete Updates by way of SQL.
2956 [Switch]$RemoveObsoleteUpdates,
2957 # Remove Declined WSUS Updates.
2958 [Switch]$RemoveDeclinedWSUSUpdates,
2959 # Decline Superseded Updates.
2960 [Switch]$DeclineSupersededUpdates,
2961 # Clean Up WSUS Synchronization Logs based on the configuration variables.
2962 [Switch]$CleanUpWSUSSynchronizationLogs,
2963 # Clean Up WSUS Synchronization Logs based on the configuration variables.
2964 [Switch]$ComputerObjectCleanup,
2965 # Run the SQL Maintenance.
2966 [Switch]$WSUSDBMaintenance,
2967 # Run the Server Cleanup Wizard (SCW) through PowerShell rather than through a GUI.
2968 [Switch]$WSUSServerCleanupWizard,
2969 # 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.
2970 [ValidateSet(“TXTâ€,â€HTMLâ€)]
2971 [String]$SaveReport,
2972 # Email the output report to an email address based on the configuration variables. TXT or HTML are valid output types.
2973 [ValidateSet(“TXTâ€,â€HTMLâ€)]
2974 [String]$MailReport
2975 )
2976Begin {
2977$AdamjCurrentSystemFunctions = Get-ChildItem function:
2978$AdamjCurrentSystemVariables = Get-Variable
2979if (-not $DailyRun -and -not $FirstRun -and -not $MonthlyRun -and -not $QuarterlyRun -and -not $ScheduledRun -and -not $HelpMe -and -not $InstallTask) {
2980 Write-Verbose "Not using a pre-defined routine"
2981 if (-not ($DisplayApplicationPoolMemory -or $IncreaseApplicationPoolMemory)) {
2982 Write-Verbose "Not using a using the Application Pool commands or the InstallTask"
2983 if ($SaveReport -eq '' -and $MailReport -eq '') {
2984 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."
2985 } else { Write-Verbose "SaveReport or MailReport have been specified. Continuing on." }
2986 } else { Write-Verbose "`$DisplayApplicationPoolMemory -or `$IncreaseApplicationPoolMemory were specified." }
2987}
2988if ($HelpMe -eq $True) { $AdamjOldVerbose = $VerbosePreference; $VerbosePreference = "continue"; Start-Transcript -Path "$(get-date -f "yyyy.MM.dd-HH.mm.ss")-HelpMe.txt" }
2989
2990#region Configuration Variables
2991################################
2992# WSUS Setup Variables #
2993################################
2994
2995# 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"
2996# WSUS does not play well with Aliases or CNAMEs and requires using the FQDN or the HostName in most cases.
2997[string]$AdamjWSUSServer = "$((Get-WmiObject win32_computersystem).DNSHostName).$((Get-WmiObject win32_computersystem).Domain)" # This should not be changed unless this doesn't work.
2998
2999# Use secure connection: $True or $False
3000[boolean]$AdamjWSUSServerUseSecureConnection = $False
3001
3002# What port number are you using for WSUS? Example: "80" or "443" if on Server 2008 or "8530" or "8531" if on Server 2012+
3003[int32]$AdamjWSUSServerPortNumber = "8530"
3004
3005################################
3006# Mail Report Setup Variables #
3007################################
3008
3009# 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
3010# your Gmail address). Example: "WSUS@domain.com" or "email@gmail.com"
3011[string]$AdamjMailReportEmailFromAddress = "wsus@domain.com"
3012
3013# To: address for email notifications. Example: "firstname.lastname@domain.com"
3014[string]$AdamjMailReportEmailToAddress = "name@domain.com"
3015
3016# Subject: of the results email
3017[string]$AdamjMailReportEmailSubject = "WSUS Cleanup Results"
3018
3019# Enter your SMTP server name. Example: "mailserver.domain.local" or "mail.domain.com" or "smtp.gmail.com"
3020[string]$AdamjMailReportSMTPServer = "192.168.0.1"
3021
3022# Enter your SMTP port number. Example: "25" or "465" (Usually for SSL) or "587" or "1025"
3023[int32]$AdamjMailReportSMTPPort = "25"
3024
3025# Do you want to enable SSL communication for your SMTP Server
3026[boolean]$AdamjMailReportSMTPServerEnableSSL = $False
3027
3028# Do you need to authenticate to the server? If not, leave blank.
3029[string]$AdamjMailReportSMTPServerUsername = ""
3030[string]$AdamjMailReportSMTPServerPassword = ""
3031
3032#Note Gmail Settings: smtp.gmail.com Port:587 SSL:Enabled User:user@gmail.com Password (if you use 2FA, make an app password).
3033
3034################################
3035# Mail Report or Save Report #
3036################################
3037
3038# Do you want to enable the Mail Report for every run?
3039[boolean]$AdamjMailReport = $True
3040
3041# Do you want the mailed report to be in HTML or plain text? (Valid options are "HTML" or "TXT")
3042[string]$AdamjMailReportType = "HTML"
3043
3044# Do you want to enable the save report for every run? (-FirstRun will save the report regardless)
3045[boolean]$AdamjSaveReport = $False
3046
3047# Do you want the saved report to be outputted in HTML or plain text? (Valid options are "HTML" or "TXT")
3048[string]$AdamjSaveReportType = "TXT"
3049
3050
3051################################
3052# WSUS Server Cleanup Wizard #
3053# Parameters #
3054# Set to $True or $False #
3055################################
3056
3057# 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.
3058[boolean]$AdamjSCWSupersededUpdatesDeclined = $True
3059
3060# Decline updates that aren't approved and have been expired my Microsoft.
3061[boolean]$AdamjSCWExpiredUpdatesDeclined = $True
3062
3063# Delete updates that are expired and have not been approved for 30 days or more.
3064[boolean]$AdamjSCWObsoleteUpdatesDeleted = $True
3065
3066# Delete older update revisions that have not been approved for 30 days or more.
3067[boolean]$AdamjSCWUpdatesCompressed = $True
3068
3069# Delete computers that have not contacted the server in 30 days or more. Default: $False
3070# This is taken care of by the Computer Object Cleanup Stream
3071[boolean]$AdamjSCWObsoleteComputersDeleted = $False
3072
3073# Delete update files that aren't needed by updates or downstream servers.
3074[boolean]$AdamjSCWUnneededContentFiles = $True
3075
3076################################
3077# Computer Object Cleanup #
3078# Variables #
3079################################
3080
3081# Do you want to remove the computer objects from WSUS that have not synchronized in days?
3082# This is good to keep your WSUS clean of previously removed computers.
3083[boolean]$AdamjComputerObjectCleanup = $True
3084
3085# If the above is set to $True, how many days of no synchronization do you want to remove
3086# computer objects from the WSUS Server? Set this to 0 to remove all computer objects.
3087[int]$AdamjComputerObjectCleanupSearchDays = "30"
3088
3089################################
3090# Scheduled Run Variables #
3091################################
3092
3093# On what day do you wish to run the MonthlyRun and QuarterlyRun Stream? I recommend on the 1st-7th of the month.
3094# This will give enough time for you to approve (if you approve manually) and your computers to receive the
3095# superseding updates after patch Tuesday (second Tuesday of the month).
3096# (Valid days are 1-31. February, April, June, September, and November have logic to set to the last day
3097# of the month if this is set to a number greater than the amount of days in that month, including leap years.)
3098[int]$AdamjScheduledRunStreamsDay = "1"
3099
3100# What months would you like to run the QuarterlyRun Stream?
3101# (Valid months are 1-12, comma separated for multiple months)
3102[string]$AdamjScheduledRunQuarterlyMonths = "1,4,7,10"
3103
3104# What time daily do you want to run the script using the scheduled task?
3105[string]$AdamjScheduledTaskTime = "8:00am"
3106
3107################################
3108# Clean Up WSUS #
3109# Synchronization Logs #
3110# Variables #
3111################################
3112
3113# Clean up the synchronization logs older than a consistency.
3114
3115# (Valid consistency number are whole numbers.)
3116[int]$AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber = "14"
3117
3118# Valid consistency time are "Day" or "Month"
3119[String]$AdamjCleanUpWSUSSynchronizationLogsConsistencyTime = "Day"
3120
3121# Or remove all synchronization logs each time
3122[boolean]$AdamjCleanUpWSUSSynchronizationLogsAll = $False
3123
3124################################
3125# SQL Server Variable #
3126################################
3127
3128# ONLY uncomment and fill out if you are using a dedicated SQL Instance or if the script tells you to
3129# otherwise leave this commented for auto-detection of the proper SQL Instance for the Windows Internal Database.
3130# Example: "SERVER\INSTANCE" or "SERVER" (if using the Default Instance)
3131
3132# If you are using a Remote SQL connection, you will need to set the Scheduled Task to use the computer account
3133# as the user that runs the script (Instead of searching for it, you must type it in the format of: DOMAIN\COMPUTER$)
3134# or run the Scheduled Task as a user account saving credentials so that it can pass them through to the SQL Server.
3135
3136# If you are having issues with the auto-dection on Server 2008 (R2), it may be due to the timeout for testing of the
3137# connection to the server. In this case, please specify this as 'np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query' as this
3138# will increase the timeout to 60 seconds and it should connect within that time.
3139
3140#[string]$AdamjSQLServer = ''
3141
3142################################
3143# Do not edit below this line #
3144################################
3145}
3146#endregion
3147
3148Process {
3149$AdamjScriptTime = Get-Date
3150$AdamjWSUSServer = $AdamjWSUSServer.ToLower()
3151Write-verbose "Set the script's current working directory path"
3152$AdamjScriptPath = Split-Path $script:MyInvocation.MyCommand.Path
3153Write-Verbose "`$AdamjScriptPath = $AdamjScriptPath"
3154
3155#region Test Elevation
3156function Test-Administrator
3157{
3158 $CurrentUser = [Security.Principal.WindowsIdentity]::GetCurrent();
3159 (New-Object Security.Principal.WindowsPrincipal $CurrentUser).IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator)
3160}
3161Write-Verbose "Testing to see if you are running this from an Elevated PowerShell Prompt."
3162if ((Test-Administrator) -ne $True) {
3163 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`""
3164}
3165else {
3166 Write-Verbose "Done. You are running this from an Elevated PowerShell Prompt"
3167}
3168#endregion Test Elevation
3169
3170if ($HelpMe -eq $True) {
3171 $Script:HelpMeHeader = @"
3172=============================
3173 Clean-WSUS HelpMe Stream
3174=============================
3175
3176This is the HelpMe Section for troubleshooting
3177Please provide this information to get support
3178
3179
3180
3181"@
3182$Script:HelpMeHeader
3183}
3184
3185#region Test SQLConnection
3186function Test-SQLConnection
3187{
3188 param (
3189 [parameter(Mandatory = $true)][string] $ServerInstance,
3190 [parameter(Mandatory = $false)][int] $TimeOut = 1
3191 )
3192
3193 $SqlConnectionResult = $false
3194
3195 try
3196 {
3197 $SqlCatalog = "SUSDB"
3198 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
3199 $SqlConnection.ConnectionString = "Server = $ServerInstance; Database = $SqlCatalog; Integrated Security = True; Connection Timeout=$TimeOut"
3200 $TimeOutVerbage = if ($TimeOut -gt "1") { "seconds" } else { "second" }
3201 Write-Verbose "Initiating SQL Connection Testing to $ServerInstance with a timeout of $TimeOut $TimeOutVerbage"
3202 $SqlConnection.Open()
3203 Write-Verbose "Connected. Setting `$SqlConnectionResult to $($SqlConnection.State -eq "Open")"
3204 $SqlConnectionResult = $SqlConnection.State -eq "Open"
3205 }
3206
3207 catch
3208 {
3209 Write-Output "Connection Failed."
3210 }
3211
3212 finally
3213 {
3214 $SqlConnection.Close()
3215 }
3216
3217 return $SqlConnectionResult
3218}
3219
3220[string]$AdamjWID2008 = 'np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query'
3221[string]$AdamjWID2012 = 'np:\\.\pipe\MICROSOFT##WID\tsql\query'
3222if (-not [string]::isnullorempty($AdamJSQLServer)) {
3223 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."
3224 if ((Test-SQLConnection $AdamjSQLServer 60) -eq $False) {
3225 # If it doesn't work, terminate the script erroring out with a reason.
3226 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."
3227 }
3228} elseif ((Test-SQLConnection $AdamjWID2008) -eq $true) {
3229 Write-Verbose "Setting `$AdamjSQLServer for server 2008 & 2008 R2 Windows Internal Database"
3230 $AdamjSQLServer = $AdamjWID2008
3231} elseif ((Test-SQLConnection $AdamjWID2012) -eq $true) {
3232 Write-Verbose "Setting `$AdamjSQLServer for server 2012 & 2012 R2 Windows Internal Database"
3233 $AdamjSQLServer = $AdamjWID2012
3234} else {
3235 if ($HelpMe -ne $True) {
3236 #Terminate the script erroring out with a reason.
3237 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"
3238 }
3239 else { Write-Output "I can't connect to SQL, and you've asked for help. Connecting to the WSUS Server to get troubleshooting information." }
3240}
3241
3242#Create the connection command variable.
3243$AdamjSQLConnectCommand = "sqlcmd -S $AdamjSQLServer"
3244#endregion Test SQLConnection
3245
3246#region Connect to the WSUS Server
3247function Connect-WSUSServer {
3248 [CmdletBinding()]
3249 param
3250 (
3251 [Parameter(Position=0, Mandatory = $True)]
3252 [Alias("Server")]
3253 [string]$WSUSServer,
3254
3255 [Parameter(Position=1, Mandatory = $True)]
3256 [Alias("Port")]
3257 [int]$WSUSPort,
3258
3259 [Parameter(Position=2, Mandatory = $True)]
3260 [Alias("SSL")]
3261 [boolean]$WSUSEnableSSL
3262 )
3263 Write-Verbose "Load .NET assembly"
3264 [void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration");
3265
3266 Write-Verbose "Connect to WSUS Server: $WSUSServer"
3267 $Script:WSUSAdminProxy = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($WSUSServer,$WSUSEnableSSL,$WSUSPort);
3268 If ($? -eq $False) {
3269 Throw "ERROR Connecting to the WSUS Server: $WSUSServer. Please check your settings and try again."
3270 } else {
3271 $Script:AdamjConnectedTime = Get-Date
3272 $Script:AdamjConnectedTXT = "Connected to the WSUS server $AdamjWSUSServer @ $($AdamjConnectedTime.ToString(`"yyyy.MM.dd hh:mm:ss tt zzz`"))`r`n`r`n"
3273 $Script:AdamjConnectedHTML = "<i>Connected to the WSUS server $AdamjWSUSServer @ $($AdamjConnectedTime.ToString(`"yyyy.MM.dd hh:mm:ss tt zzz`"))</i>`r`n`r`n"
3274 Write-Output "Connected to the WSUS server $AdamjWSUSServer"
3275 }
3276}
3277
3278if (($InstallTask -or $DisplayApplicationPoolMemory -or $IncreaseApplicationPoolMemory) -eq $False) {
3279 Connect-WSUSServer -Server $AdamjWSUSServer -Port $AdamjWSUSServerPortNumber -SSL $AdamjWSUSServerUseSecureConnection
3280 $AdamjWSUSServerAdminProxy = $Script:WSUSAdminProxy
3281}
3282#endregion Connect to the WSUS Server
3283
3284#region Get-DiskFree Function
3285################################
3286# Get-DiskFree #
3287################################
3288
3289function Get-DiskFree
3290# Taken from http://binarynature.blogspot.ca/2010/04/powershell-version-of-df-command.html
3291{
3292 [CmdletBinding()]
3293 param
3294 (
3295 [Parameter(Position=0,
3296 ValueFromPipeline=$true,
3297 ValueFromPipelineByPropertyName=$true)]
3298 [Alias('hostname')]
3299 [Alias('cn')]
3300 [string[]]$ComputerName = $env:COMPUTERNAME,
3301
3302 [Parameter(Position=1,
3303 Mandatory=$false)]
3304 [Alias('runas')]
3305 [System.Management.Automation.Credential()]$Credential =
3306 [System.Management.Automation.PSCredential]::Empty,
3307
3308 [Parameter(Position=2)]
3309 [switch]$Format
3310 )
3311
3312 BEGIN
3313 {
3314 function Format-HumanReadable
3315 {
3316 param ($size)
3317 switch ($size)
3318 {
3319 {$_ -ge 1PB}{"{0:#.#'P'}" -f ($size / 1PB); break}
3320 {$_ -ge 1TB}{"{0:#.#'T'}" -f ($size / 1TB); break}
3321 {$_ -ge 1GB}{"{0:#.#'G'}" -f ($size / 1GB); break}
3322 {$_ -ge 1MB}{"{0:#.#'M'}" -f ($size / 1MB); break}
3323 {$_ -ge 1KB}{"{0:#'K'}" -f ($size / 1KB); break}
3324 default {"{0}" -f ($size) + "B"}
3325 }
3326 }
3327 $wmiq = 'SELECT * FROM Win32_LogicalDisk WHERE Size != Null AND DriveType >= 2'
3328 }
3329
3330 PROCESS
3331 {
3332 foreach ($computer in $ComputerName)
3333 {
3334 try
3335 {
3336 if ($computer -eq $env:COMPUTERNAME)
3337 {
3338 $disks = Get-WmiObject -Query $wmiq `
3339 -ComputerName $computer -ErrorAction Stop
3340 }
3341 else
3342 {
3343 $disks = Get-WmiObject -Query $wmiq `
3344 -ComputerName $computer -Credential $Credential `
3345 -ErrorAction Stop
3346 }
3347
3348 if ($Format)
3349 {
3350 # Create array for $disk objects and then populate
3351 $diskarray = @()
3352 $disks | ForEach-Object { $diskarray += $_ }
3353
3354 $diskarray | Select-Object @{n='Name';e={$_.SystemName}},
3355 @{n='Vol';e={$_.DeviceID}},
3356 @{n='Size';e={Format-HumanReadable $_.Size}},
3357 @{n='Used';e={Format-HumanReadable `
3358 (($_.Size)-($_.FreeSpace))}},
3359 @{n='Avail';e={Format-HumanReadable $_.FreeSpace}},
3360 @{n='Use%';e={[int](((($_.Size)-($_.FreeSpace))`
3361 /($_.Size) * 100))}},
3362 @{n='FS';e={$_.FileSystem}},
3363 @{n='Type';e={$_.Description}}
3364 }
3365 else
3366 {
3367 foreach ($disk in $disks)
3368 {
3369 $diskprops = @{'Volume'=$disk.DeviceID;
3370 'Size'=$disk.Size;
3371 'Used'=($disk.Size - $disk.FreeSpace);
3372 'Available'=$disk.FreeSpace;
3373 'FileSystem'=$disk.FileSystem;
3374 'Type'=$disk.Description
3375 'Computer'=$disk.SystemName;}
3376
3377 # Create custom PS object and apply type
3378 $diskobj = New-Object -TypeName PSObject `
3379 -Property $diskprops
3380 $diskobj.PSObject.TypeNames.Insert(0,'BinaryNature.DiskFree')
3381
3382 Write-Output $diskobj
3383 }
3384 }
3385 }
3386 catch
3387 {
3388 # Check for common DCOM errors and display "friendly" output
3389 switch ($_)
3390 {
3391 { $_.Exception.ErrorCode -eq 0x800706ba } `
3392 { $err = 'Unavailable (Host Offline or Firewall)';
3393 break; }
3394 { $_.CategoryInfo.Reason -eq 'UnauthorizedAccessException' } `
3395 { $err = 'Access denied (Check User Permissions)';
3396 break; }
3397 default { $err = $_.Exception.Message }
3398 }
3399 Write-Warning "$computer - $err"
3400 }
3401 }
3402 }
3403
3404 END {}
3405}
3406#endregion Get-DiskFree Function
3407
3408#region Setup The Header
3409################################
3410# Setup the Header #
3411################################
3412
3413function CreateAdamjHeader {
3414$Script:AdamjBodyHeaderTXT = @"
3415################################
3416# #
3417# Adamj Clean-WSUS #
3418# Version 2.11 #
3419# #
3420# The last WSUS Script you #
3421# will ever need! #
3422# #
3423################################
3424
3425
3426"@
3427$Script:AdamjBodyHeaderHTML = @"
3428 <table style="height: 0px; width: 0px;" border="0">
3429 <tbody>
3430 <tr>
3431 <td colspan="3">
3432 <span
3433 style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span>
3434 </td>
3435 </tr>
3436 <tr>
3437 <td style="text-align: left;">#</td>
3438 <td style="text-align: center;"> </td>
3439 <td style="text-align: right;">#</td>
3440 </tr>
3441 <tr>
3442 <td style="text-align: left;">#</td>
3443 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Adamj Clean-WSUS</span></td>
3444 <td style="text-align: right;">#</td>
3445 </tr>
3446 <tr>
3447 <td style="text-align: left;">#</td>
3448 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Version 2.11</span></td>
3449 <td style="text-align: right;">#</td>
3450 </tr>
3451 <tr>
3452 <td style="text-align: left;">#</td>
3453 <td> </td>
3454 <td style="text-align: right;">#</td>
3455 </tr>
3456 <tr>
3457 <td style="text-align: left;">#</td>
3458 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">The last WSUS Script you</span></td>
3459 <td style="text-align: right;">#</td>
3460 </tr>
3461 <tr>
3462 <td style="text-align: left;">#</td>
3463 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">will ever need!</span></td>
3464 <td style="text-align: right;">#</td>
3465 </tr>
3466 <tr>
3467 <td style="text-align: left;">#</td>
3468 <td> </td>
3469 <td style="text-align: right;">#</td>
3470 </tr>
3471 <tr>
3472 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
3473 </tr>
3474 </tbody>
3475 </table>
3476"@
3477}
3478#endregion Setup The Header
3479
3480#region Setup The Footer
3481################################
3482# Setup the Footer #
3483################################
3484
3485function CreateAdamjFooter {
3486$Script:AdamjBodyFooterTXT = @"
3487
3488################################
3489# End of the WSUS Cleanup #
3490################################
3491# #
3492# Adam Marshall #
3493# http://www.adamj.org #
3494# Donations Accepted #
3495# #
3496# Latest version available #
3497# from Spiceworks #
3498# #
3499################################
3500
3501http://community.spiceworks.com/scripts/show/2998-adamj-clean-wsus
3502Donations Accepted: http://www.adamj.org/clean-wsus/donate.html
3503"@
3504$Script:AdamjBodyFooterHTML = @"
3505 <table style="height: 0px; width: 0px;" border="0">
3506 <tbody>
3507 <tr>
3508 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
3509 </tr>
3510 <tr>
3511 <td style="text-align: left;">#</td>
3512 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">End of the WSUS Cleanup</span></td>
3513 <td style="text-align: right;">#</td>
3514 </tr>
3515 <tr>
3516 <td colspan="3" rowspan="1"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
3517 </tr>
3518 <tr>
3519 <td style="text-align: left;">#</td>
3520 <td style="text-align: center;"> </td>
3521 <td style="text-align: right;">#</td>
3522 </tr>
3523 <tr>
3524 <td style="text-align: left;">#</td>
3525 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Adam Marshall</span></td>
3526 <td style="text-align: right;">#</td>
3527 </tr>
3528 <tr>
3529 <td style="text-align: left;">#</td>
3530 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">http://www.adamj.org</span></td>
3531 <td style="text-align: right;">#</td>
3532 </tr>
3533 <tr>
3534 <td style="text-align: left;">#</td>
3535 <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>
3536 <td style="text-align: right;">#</td>
3537 </tr>
3538 <tr>
3539 <td style="text-align: left;">#</td>
3540 <td> </td>
3541 <td style="text-align: right;">#</td>
3542 </tr>
3543 <tr>
3544 <td style="text-align: left;">#</td>
3545 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Latest version available</span></td>
3546 <td style="text-align: right;">#</td>
3547 </tr>
3548 <tr>
3549 <td style="text-align: left;">#</td>
3550 <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>
3551 <td style="text-align: right;">#</td>
3552 </tr>
3553 <tr>
3554 <td style="text-align: left;">#</td>
3555 <td> </td>
3556 <td style="text-align: right;">#</td>
3557 </tr>
3558 <tr>
3559 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
3560 </tr>
3561 </tbody>
3562 </table>
3563"@
3564}
3565#endregion Setup The Footer
3566
3567#region Show-My Functions
3568################################
3569# Show-My Functions Stream #
3570################################
3571
3572function Show-MyFunctions { Get-ChildItem function: | Where-Object { $AdamjCurrentSystemFunctions -notcontains $_ } | Format-Table -AutoSize -Property CommandType,Name }
3573function Show-MyVariables { Get-Variable | Where-Object { $AdamjCurrentSystemVariables -notcontains $_ } | Format-Table }
3574#endregion Show-My Functions
3575
3576#region Install-Task Function
3577################################
3578# Install-Task Configuration #
3579################################
3580
3581Function Install-Task {
3582 $Windows = [PSCustomObject]@{
3583 Caption = (Get-WmiObject -Class Win32_OperatingSystem).Caption
3584 Version = [Environment]::OSVersion.Version
3585 }
3586 if ($Windows.Version.Major -gt "6") { Write-Verbose "$($Windows.Caption) - Use Win8 Compatibility" ; $Compatibility = "Win8" }
3587 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -ge "2" ) { Write-Verbose "$($Windows.Caption) - Use Win8 Compatibility" ; $Compatibility = "Win8" }
3588 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -eq "1" ) { Write-Verbose "$($Windows.Caption) - Use Win7 Compatibility" ; $Compatibility = "Win7" }
3589 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -eq "0" ) { Write-Verbose "$($Windows.Caption) - Use Vista Compatibility" ; $Compatibility = "Vista" }
3590
3591 $Trigger = New-ScheduledTaskTrigger -At $AdamjScheduledTaskTime -Daily #Trigger the task daily at $AdamjScheduledTaskTime
3592 $User = "$env:USERDOMAIN\$env:USERNAME"
3593 $Principal = New-ScheduledTaskPrincipal -UserID "$env:USERDOMAIN\$env:USERNAME" -LogonType S4U -RunLevel Highest
3594 $TaskName = "Adamj Clean-WSUS"
3595 $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."
3596 $Action = New-ScheduledTaskAction -Execute "$((Get-Command powershell.exe).Definition)" -Argument "-ExecutionPolicy Bypass `"$($script:MyInvocation.MyCommand.Path) -ScheduledRun`""
3597 $Settings = New-ScheduledTaskSettingsSet -Compatibility $Compatibility
3598 Write-Verbose "Register the Scheduled task."
3599 Register-ScheduledTask -TaskName $TaskName -Description $Description -Action $Action -Trigger $Trigger -Settings $Settings -Principal $Principal -Force
3600}
3601$PowerShellMajorVersion = $($PSVersionTable.PSVersion.Major)
3602Write-Verbose "`$InstallTask is $InstallTask"
3603if ($InstallTask -eq $True) {
3604 $Version = @{}
3605 $Version.Add("Major", ((Get-CimInstance Win32_OperatingSystem).Version).Split(".")[0])
3606 $Version.Add("Minor", ((Get-CimInstance Win32_OperatingSystem).Version).Split(".")[1])
3607 #$Version.Add("Major", "5")
3608 #$Version.Add("Minor", "3")
3609 if ([int]$Version.Get_Item("Major") -ge "7" -or ([int]$Version.Get_Item("Major") -ge "6" -and [int]$Version.Get_Item("Minor") -ge "2")) {
3610 Write-Verbose "YES - OS Version $([int]$Version.Get_Item("Major")).$([int]$Version.Get_Item("Minor"))"
3611 Install-Task
3612 } else {
3613 Write-Verbose "NO - OS Version $([int]$Version.Get_Item("Major")).$([int]$Version.Get_Item("Minor"))"
3614 Write-Output ""
3615 Write-Output "You are not using Windows Server 2012 or higher. You will have to manually create the Scheduled Task"
3616 Write-Output ""
3617 $AdamjManuallyCreateTaskInstructions = @"
3618To Create a Scheduled Task:
3619
3620 1. Open Task Scheduler and Create a new task (not a basic task)
3621 2. Go to the General Tab:
3622 3. Name: "Adamj Clean-WSUS"
3623 4. Under the section "Security Options" put the dot in "Run whether the user is logged on or not"
3624 5. Check "Do not store password. The task will only have access to local computer resources"
3625 6. Check "Run with highest privileges."
3626 7. Under the section "Configure for" - Choose the OS of the Server (e.g. Server 2012 R2)
3627 8. Go to the Triggers Tab:
3628 9. Click New at the bottom left.
362910. Under the section "Settings"
363011. Choose Daily. Choose $AdamjScheduledTaskTime
363112. Confirm Enabled is checked, Press OK.
363213. Go to the Actions Tab:
363314. Click New at the bottom left.
363415. Action should be "Start a program"
363516. The "Program/script" should be set to
3636
3637 $((Get-Command powershell.exe).Definition)
3638
363917. The arguments line should be set to
3640
3641 -ExecutionPolicy Bypass `"$($script:MyInvocation.MyCommand.Path) -ScheduledRun`"
3642
364318. Go to the Settings Tab:
364419. Check "Allow task to be run on demand"
364520. Click OK
3646"@
3647 Write-Output $AdamjManuallyCreateTaskInstructions
3648 }
3649}
3650#endregion Install-Task Function
3651
3652#region ApplicationPoolMemory Function
3653################################
3654# Application Pool Memory #
3655# Configuration Stream #
3656################################
3657function ApplicationPoolMemory {
3658 Param(
3659 [ValidateRange([int]::MinValue,[int]::MaxValue)]
3660 [Int]$IncreaseApplicationPoolBy
3661 )
3662 $DateNow = Get-Date
3663 Import-Module WebAdministration
3664 $applicationPoolsPath = "/system.applicationHost/applicationPools"
3665 $applicationPools = Get-WebConfiguration $applicationPoolsPath
3666 foreach ($appPool in $applicationPools.Collection) {
3667 if ($appPool.name -eq 'WsusPool') {
3668 $appPoolPath = "$applicationPoolsPath/add[@name='$($appPool.Name)']"
3669 $CurrentPrivateMemory = (Get-WebConfiguration "$appPoolPath/recycling/periodicRestart/@privateMemory").Value
3670 Write-Output "Current Private Memory Limit for $($appPool.name) is: $($CurrentPrivateMemory/1000) MB"
3671 if ($IncreaseApplicationPoolBy) {
3672 $IncreaseApplicationPoolBy=$IncreaseApplicationPoolBy * 1000
3673 $NewPrivateMemory = $CurrentPrivateMemory + $IncreaseApplicationPoolBy
3674 Write-Output "New Private Memory Limit for $($appPool.name) is: $($NewPrivateMemory/1000) MB"
3675 Set-WebConfiguration "$appPoolPath/recycling/periodicRestart/@privateMemory" -Value $NewPrivateMemory
3676 Write-Verbose "Restart the $($appPool.name) Application Pool to make the new settings take effect"
3677 Restart-WebAppPool -Name $($appPool.name)
3678 }
3679 }
3680 }
3681 $FinishedRunning = Get-Date
3682 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
3683 $Duration = "{0:00}:{1:00}:{2:00}:{3:00}:{4:00}" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds, $_.Milliseconds})
3684 Write-Verbose "Application Pool Memory Stream Duration: $Duration"
3685}
3686#endregion ApplicationPoolMemory Function
3687
3688#region RemoveWSUSDrivers Function
3689################################
3690# Adamj Remove WSUS Drivers #
3691# Stream #
3692################################
3693
3694function RemoveWSUSDrivers {
3695 param (
3696 [Parameter()]
3697 [Switch] $SQL
3698 )
3699 function RemoveWSUSDriversSQL {
3700 $AdamjRemoveWSUSDriversSQLScript = @"
3701/*
3702################################
3703# Adamj WSUS Delete Drivers #
3704# SQL Script #
3705# Version 1.0 #
3706# Taken from various sources #
3707# from the Internet. #
3708# #
3709# Modified By: Adam Marshall #
3710# http://www.adamj.org #
3711################################
3712
3713-- Originally taken from http://www.flexecom.com/how-to-delete-driver-updates-from-wsus-3-0/
3714-- Modified to be dynamic and more of a nice output
3715*/
3716USE SUSDB;
3717GO
3718
3719SET NOCOUNT ON;
3720DECLARE @tbrevisionlanguage nvarchar(255)
3721DECLARE @tbProperty nvarchar(255)
3722DECLARE @tbLocalizedPropertyForRevision nvarchar(255)
3723DECLARE @tbFileForRevision nvarchar(255)
3724DECLARE @tbInstalledUpdateSufficientForPrerequisite nvarchar(255)
3725DECLARE @tbPreRequisite nvarchar(255)
3726DECLARE @tbDeployment nvarchar(255)
3727DECLARE @tbXml nvarchar(255)
3728DECLARE @tbPreComputedLocalizedProperty nvarchar(255)
3729DECLARE @tbDriver nvarchar(255)
3730DECLARE @tbFlattenedRevisionInCategory nvarchar(255)
3731DECLARE @tbRevisionInCategory nvarchar(255)
3732DECLARE @tbMoreInfoURLForRevision nvarchar(255)
3733DECLARE @tbRevision nvarchar(255)
3734DECLARE @tbUpdateSummaryForAllComputers nvarchar(255)
3735DECLARE @tbUpdate nvarchar(255)
3736DECLARE @var1 nvarchar(255)
3737
3738/*
3739This query gives you the GUID that you will need to substitute in all subsequent queries. In my case, it is
3740D2CB599A-FA9F-4AE9-B346-94AD54EE0629. I saw this GUID in several WSUS databases so I think it does not change;
3741at least not between WSUS 3.0 SP2 servers. Either way, we are setting a variable for this so this will
3742dynamically reference the correct GUID.
3743*/
3744
3745SELECT @var1 = UpdateTypeID FROM tbUpdateType WHERE Name = 'Driver'
3746
3747/*
3748The bad news is that WSUS database has over 100 tables. The good news is that SQL allows to enforce referential
3749integrity in data model designs, which in this case can be used to essentially reverse engineer a procedure,
3750that as far as I know isn’t documented anywhere.
3751
3752The trick is to delete all driver type records from tbUpdate table – but FIRST we have to delete all records in
3753all other tables (revisions, languages, dependencies, files, reports…), which refer to driver rows in tbUpdate.
3754
3755Here’s how this is done, in 16 tables/queries.
3756*/
3757
3758delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3759SELECT @tbrevisionlanguage = @@ROWCOUNT
3760PRINT 'Delete records from tbrevisionlanguage: ' + @tbrevisionlanguage
3761
3762delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3763SELECT @tbProperty = @@ROWCOUNT
3764PRINT 'Delete records from tbProperty: ' + @tbProperty
3765
3766delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3767SELECT @tbLocalizedPropertyForRevision = @@ROWCOUNT
3768PRINT 'Delete records from tbLocalizedPropertyForRevision: ' + @tbLocalizedPropertyForRevision
3769
3770delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3771SELECT @tbFileForRevision = @@ROWCOUNT
3772PRINT 'Delete records from tbFileForRevision: ' + @tbFileForRevision
3773
3774delete 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)))
3775SELECT @tbInstalledUpdateSufficientForPrerequisite = @@ROWCOUNT
3776PRINT 'Delete records from tbInstalledUpdateSufficientForPrerequisite: ' + @tbInstalledUpdateSufficientForPrerequisite
3777
3778delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3779SELECT @tbPreRequisite = @@ROWCOUNT
3780PRINT 'Delete records from tbPreRequisite: ' + @tbPreRequisite
3781
3782delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3783SELECT @tbDeployment = @@ROWCOUNT
3784PRINT 'Delete records from tbDeployment: ' + @tbDeployment
3785
3786delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3787SELECT @tbXml = @@ROWCOUNT
3788PRINT 'Delete records from tbXml: ' + @tbXml
3789
3790delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3791SELECT @tbPreComputedLocalizedProperty = @@ROWCOUNT
3792PRINT 'Delete records from tbPreComputedLocalizedProperty: ' + @tbPreComputedLocalizedProperty
3793
3794delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3795SELECT @tbDriver = @@ROWCOUNT
3796PRINT 'Delete records from tbDriver: ' + @tbDriver
3797
3798delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3799SELECT @tbFlattenedRevisionInCategory = @@ROWCOUNT
3800PRINT 'Delete records from tbFlattenedRevisionInCategory: ' + @tbFlattenedRevisionInCategory
3801
3802delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3803SELECT @tbRevisionInCategory = @@ROWCOUNT
3804PRINT 'Delete records from tbRevisionInCategory: ' + @tbRevisionInCategory
3805
3806delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
3807SELECT @tbMoreInfoURLForRevision = @@ROWCOUNT
3808PRINT 'Delete records from tbMoreInfoURLForRevision: ' + @tbMoreInfoURLForRevision
3809
3810delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)
3811SELECT @tbRevision = @@ROWCOUNT
3812PRINT 'Delete records from tbRevision: ' + @tbRevision
3813
3814delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)
3815SELECT @tbUpdateSummaryForAllComputers = @@ROWCOUNT
3816PRINT 'Delete records from tbUpdateSummaryForAllComputers: ' + @tbUpdateSummaryForAllComputers
3817
3818PRINT CHAR(13)+CHAR(10) + 'This is the last query and this is really what we came here for.'
3819
3820delete from tbUpdate where UpdateTypeID = @var1
3821SELECT @tbUpdate = @@ROWCOUNT
3822PRINT 'Delete records from tbUpdate: ' + @tbUpdate
3823
3824/*
3825If at this point you get an error saying something about foreign key constraint, that will be most likely
3826due to the difference between which reports I ran in my WSUS installation and which reports were ran against
3827your particular installation. Fortunately, the error gives you exact location (table) where this constraint
3828is violated, so you can adjust one of the queries in the batch above to delete references in any other tables.
3829*/
3830"@
3831 Write-Verbose "Create a file with the content of the RemoveWSUSDrivers Script above in the same working directory as this PowerShell script is running."
3832 $AdamjRemoveWSUSDriversSQLScriptFile = "$AdamjScriptPath\AdamjRemoveWSUSDrivers.sql"
3833 $AdamjRemoveWSUSDriversSQLScript | Out-File "$AdamjRemoveWSUSDriversSQLScriptFile"
3834 # Re-jig the $AdamjSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
3835 $AdamjSQLConnectCommand = $AdamjSQLConnectCommand.Replace('$','`$')
3836 Write-Verbose "Execute the SQL Script and store the results in a variable."
3837 $AdamjRemoveWSUSDriversSQLScriptJobCommand = [scriptblock]::create("$AdamjSQLConnectCommand -i `"$AdamjRemoveWSUSDriversSQLScriptFile`" -I")
3838 Write-Verbose "`$AdamjRemoveWSUSDriversSQLScriptJobCommand = $AdamjRemoveWSUSDriversSQLScriptJobCommand"
3839 $AdamjRemoveWSUSDriversSQLScriptJob = Start-Job -ScriptBlock $AdamjRemoveWSUSDriversSQLScriptJobCommand
3840 Wait-Job $AdamjRemoveWSUSDriversSQLScriptJob
3841 $AdamjRemoveWSUSDriversSQLScriptJobOutput = Receive-Job $AdamjRemoveWSUSDriversSQLScriptJob
3842 Remove-Job $AdamjRemoveWSUSDriversSQLScriptJob
3843 Write-Verbose "Remove the SQL Script file."
3844 Remove-Item "$AdamjRemoveWSUSDriversSQLScriptFile"
3845 $Script:AdamjRemoveWSUSDriversSQLOutputTXT = $AdamjRemoveWSUSDriversSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n`r`n"
3846 $Script:AdamjRemoveWSUSDriversSQLOutputHTML = $AdamjRemoveWSUSDriversSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
3847
3848 # Variables Output
3849 # $AdamjRemoveWSUSDriversSQLOutputTXT
3850 # $AdamjRemoveWSUSDriversSQLOutputHTML
3851
3852 }
3853 function RemoveWSUSDriversPS {
3854 $Count = 0
3855 $AdamjWSUSServerAdminProxy.GetUpdates() | Where-Object { $_.IsDeclined -eq $true -and $_.UpdateClassificationTitle -eq "Drivers" } | ForEach-Object {
3856 # Delete these updates
3857 $AdamjWSUSServerAdminProxy.DeleteUpdate($_.Id.UpdateId.ToString())
3858 $DeleteDeclinedDriverTitle = $_.Title
3859 $Count++
3860 $AdamjRemoveWSUSDriversPSDeleteOutputTXT += "$($Count). $($DeleteDeclinedDriverTitle)`n`n"
3861 $AdamjRemoveWSUSDriversPSDeleteOutputHTML += "<li>$DeleteDeclinedDriverTitle</li>`n"
3862 }
3863 $AdamjRemoveWSUSDriversPSDeleteOutputTXT += "`n`n"
3864 $AdamjRemoveWSUSDriversPSDeleteOutputHTML += "</ol>`n"
3865
3866 $Script:AdamjRemoveWSUSDriversPSOutputTXT += "`n`n"
3867 $Script:AdamjRemoveWSUSDriversPSOutputHTML += "<ol>`n"
3868 $Script:AdamjRemoveWSUSDriversPSOutputTXT += $AdamjRemoveWSUSDriversPSDeleteOutputTXT
3869 $Script:AdamjRemoveWSUSDriversPSOutputHTML += $AdamjRemoveWSUSDriversPSDeleteOutputHTML
3870
3871 # Variables Output
3872 # $AdamjRemoveWSUSDriversPSOutputTXT
3873 # $AdamjRemoveWSUSDriversPSOutputHTML
3874 }
3875 # Process the appropriate internal function
3876 $DateNow = Get-Date
3877 if ($SQL -eq $True) { RemoveWSUSDriversSQL } else { RemoveWSUSDriversPS }
3878 $FinishedRunning = Get-Date
3879 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
3880 # Create the output for the RemoveWSUSDrivers function
3881 $Script:AdamjRemoveWSUSDriversOutputTXT += "Adamj Remove WSUS Drivers:`n`n"
3882 $Script:AdamjRemoveWSUSDriversOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Remove WSUS Drivers:</span></p>`n"
3883 if ($SQL -eq $True) {
3884 $Script:AdamjRemoveWSUSDriversOutputTXT += $AdamjRemoveWSUSDriversSQLOutputTXT
3885 $Script:AdamjRemoveWSUSDriversOutputHTML += $AdamjRemoveWSUSDriversSQLOutputHTML
3886 } else {
3887 $Script:AdamjRemoveWSUSDriversOutputTXT += $AdamjRemoveWSUSDriversPSOutputTXT
3888 $Script:AdamjRemoveWSUSDriversOutputHTML += $AdamjRemoveWSUSDriversPSOutputHTML
3889 }
3890 $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})
3891 $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})
3892
3893 # Variables Output
3894 # $AdamjRemoveWSUSDriversOutputTXT
3895 # $AdamjRemoveWSUSDriversOutputHTML
3896}
3897#endregion RemoveWSUSDrivers Function
3898
3899#region RemoveDeclinedWSUSUpdates Function
3900################################
3901# Adamj Remove Declined WSUS #
3902# Updates Stream #
3903################################
3904
3905function RemoveDeclinedWSUSUpdates {
3906 param (
3907 [Switch]$Display,
3908 [Switch]$Proceed
3909 )
3910 # Log the date first
3911 $DateNow = Get-Date
3912 Write-Verbose "Create an update scope"
3913 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
3914 Write-Verbose "By default the update scope is created for any approval states"
3915 $UpdateScope.ApprovedStates = "Any"
3916 Write-Verbose "Get all updates that have been Superseded and not Declined"
3917 $AdamjRemoveDeclinedWSUSUpdatesUpdates = $AdamjWSUSServerAdminProxy.GetUpdates($UpdateScope) | Where { ($_.isDeclined) }
3918 function RemoveDeclinedWSUSUpdatesCountUpdates {
3919 Write-Verbose "First count how many updates will be removed that are already declined updates - just for fun. I like fun :)"
3920 $Script:AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount = "{0:N0}" -f $AdamjRemoveDeclinedWSUSUpdatesUpdates.Count
3921 $Script:AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT += "The number of declined updates that would be removed from the database are: $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount.`r`n`r`n"
3922 $Script:AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML += "<p>The number of declined updates that would be removed from the database are: $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount.</p>`n"
3923
3924 # Variables Output
3925 # $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT
3926 # $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML
3927 }
3928
3929 function RemoveDeclinedWSUSUpdatesDisplayUpdates {
3930 Write-Verbose "Display the titles of the declined updates that will be removed from the database - just for fun. I like fun :)"
3931 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "<ol>`n"
3932 $Count=0
3933 ForEach ($update in $AdamjRemoveDeclinedWSUSUpdatesUpdates) {
3934 $Count++
3935 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputTXT += "$($Count). $($update.title) - https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`r`n"
3936 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "<li><a href=`"https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`">$($update.title)</a></li>`n"
3937 }
3938 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputTXT += "`r`n"
3939 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "</ol>`n"
3940
3941 # Variables Output
3942 # $AdamjRemoveDeclinedWSUSUpdatesDisplayOutputTXT
3943 # $AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML
3944 }
3945
3946 function RemoveDeclinedWSUSUpdatesProceed {
3947 Write-Output "You've chosen to remove declined updates from the database. Removing $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates."
3948 Write-Output ""
3949 Write-Output "Please be patient, this may take a while."
3950 Write-Output ""
3951 Write-Output "It is not abnormal for this process to take minutes, hours, or days. It varies per install and per execution."
3952 Write-Output ""
3953 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."
3954 Write-Output ""
3955 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."
3956 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT += "You've chosen to remove declined updates from the database. Removing $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates.`r`n`r`n"
3957 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += "<p>You've chosen to remove declined updates from the database. <strong>Removing $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates.</strong></p>`n"
3958 # Remove these updates
3959 $AdamjRemoveDeclinedWSUSUpdatesUpdates | ForEach-Object {
3960 $DeleteID = $_.Id.UpdateId.ToString()
3961 Try {
3962 $AdamjRemoveDeclinedWSUSUpdatesUpdateTitle = $($_.Title)
3963 Write-Output "Deleting" $AdamjRemoveDeclinedWSUSUpdatesUpdateTitle
3964 $AdamjWSUSServerAdminProxy.DeleteUpdate($DeleteId)
3965 }
3966 Catch {
3967 $ExceptionError = $_.Exception
3968 if ([string]::isnullorempty($AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT)) { $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT = "" }
3969 if ([string]::isnullorempty($AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML)) { $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML = "" }
3970 $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT += "Error: $AdamjRemoveDeclinedWSUSUpdatesUpdateTitle`r`n`r`n$ExceptionError.InnerException`r`n`r`n"
3971 $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML += "<li><p>$AdamjRemoveDeclinedWSUSUpdatesUpdateTitle</p>$ExceptionError.InnerException</li>"
3972 }
3973 Finally {
3974 if ($ExceptionError) {
3975 Write-Output "Errors:" $ExceptionError.Message
3976 Remove-Variable ExceptionError
3977 } else {
3978 Write-Verbose "Successful"
3979 }
3980 }
3981 }
3982 if (-not [string]::isnullorempty($AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT)) {
3983 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT += "*** Errors Removing Declined WSUS Updates ***`r`n"
3984 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT += $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT
3985 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT += "`r`n`r`n"
3986 }
3987 if (-not [string]::isnullorempty($AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML)) {
3988 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += "<div class='error'><h1>Errors Removing Declined WSUS Updates</h1><ol start='1'>"
3989 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML
3990 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += "</ol></div>"
3991 }
3992
3993 # Variables Output
3994 # $AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT
3995 # $AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML
3996 }
3997
3998 RemoveDeclinedWSUSUpdatesCountUpdates
3999 if ($Display -ne $False) { RemoveDeclinedWSUSUpdatesDisplayUpdates }
4000 if ($Proceed -ne $False) { RemoveDeclinedWSUSUpdatesProceed }
4001 $FinishedRunning = Get-Date
4002 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
4003
4004 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += "Adamj Remove Declined WSUS Updates:`r`n`r`n"
4005 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Remove Declined WSUS Updates:</span></p>`n<ol>`n"
4006 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT
4007 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML
4008 if ($Display -ne $False) {
4009 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += $AdamjRemoveDeclinedWSUSUpdatesDisplayOutputTXT
4010 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += $AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML
4011 }
4012 if ($Proceed -ne $False) {
4013 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += $AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT
4014 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += $AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML
4015 }
4016 $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})
4017 $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})
4018
4019 # Variables Output
4020 # $AdamjRemoveDeclinedWSUSUpdatesOutputTXT
4021 # $AdamjRemoveDeclinedWSUSUpdatesOutputHTML
4022}
4023#endregion RemoveDeclinedWSUSUpdates Function
4024
4025#region CompressUpdateRevisions Function
4026################################
4027# Adamj Compress Update #
4028# Revisions Stream #
4029################################
4030
4031function CompressUpdateRevisions {
4032 Param (
4033 )
4034 $DateNow = Get-Date
4035 $AdamjCompressUpdateRevisionsSQLScript = @"
4036USE SUSDB;
4037GO
4038-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
4039SET NOCOUNT ON
4040
4041DECLARE @var1 INT, @curitem INT, @totaltocompress INT
4042DECLARE @msg nvarchar(200)
4043
4044IF EXISTS (
4045 SELECT * FROM tempdb.dbo.sysobjects o
4046 WHERE o.xtype IN ('U')
4047 AND o.id = object_id(N'tempdb..#results')
4048)
4049DROP TABLE #results
4050CREATE TABLE #results (Col1 INT)
4051
4052-- Compress Update Revisions
4053INSERT INTO #results(Col1) EXEC spGetUpdatesToCompress
4054SET @totaltocompress = (SELECT COUNT(*) FROM #results)
4055SELECT @curitem=1
4056DECLARE WC Cursor FOR SELECT Col1 FROM #results;
4057OPEN WC
4058FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
4059BEGIN
4060 SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltocompress as varchar(5)) + ': Compressing ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
4061 RAISERROR(@msg,0,1) WITH NOWAIT
4062 EXEC spCompressUpdate @localUpdateID=@var1
4063 SET @curitem = @curitem +1
4064 FETCH NEXT FROM WC INTO @var1
4065END
4066CLOSE WC
4067DEALLOCATE WC
4068DROP TABLE #results
4069"@
4070 Write-Verbose "Create a file with the content of the CompressUpdateRevisions Script above in the same working directory as this PowerShell script is running."
4071 $AdamjCompressUpdateRevisionsSQLScriptFile = "$AdamjScriptPath\AdamjCompressUpdateRevisions.sql"
4072 $AdamjCompressUpdateRevisionsSQLScript | Out-File "$AdamjCompressUpdateRevisionsSQLScriptFile"
4073
4074 # Re-jig the $AdamjSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
4075 $AdamjSQLConnectCommand = $AdamjSQLConnectCommand.Replace('$','`$')
4076 Write-Verbose "Execute the SQL Script and store the results in a variable."
4077 $AdamjCompressUpdateRevisionsSQLScriptJobCommand = [scriptblock]::create("$AdamjSQLConnectCommand -i `"$AdamjCompressUpdateRevisionsSQLScriptFile`" -I")
4078 Write-Verbose "`$AdamjCompressUpdateRevisionsSQLScriptJob = $AdamjCompressUpdateRevisionsSQLScriptJobCommand"
4079 $AdamjCompressUpdateRevisionsSQLScriptJob = Start-Job -ScriptBlock $AdamjCompressUpdateRevisionsSQLScriptJobCommand
4080 Wait-Job $AdamjCompressUpdateRevisionsSQLScriptJob
4081 $AdamjCompressUpdateRevisionsSQLScriptJobOutput = Receive-Job $AdamjCompressUpdateRevisionsSQLScriptJob
4082 Remove-Job $AdamjCompressUpdateRevisionsSQLScriptJob
4083 Write-Verbose "Remove the SQL Script file."
4084 Remove-Item "$AdamjCompressUpdateRevisionsSQLScriptFile"
4085 $FinishedRunning = Get-Date
4086 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
4087 # Setup variables to store the output to be added at the very end of the script for logging purposes.
4088 $Script:AdamjCompressUpdateRevisionsOutputTXT += "Adamj Compress Update Revisions:`r`n`r`n"
4089 $Script:AdamjCompressUpdateRevisionsOutputTXT += $AdamjCompressUpdateRevisionsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
4090 $Script:AdamjCompressUpdateRevisionsOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Compress Update Revisions:</span></p>`n`n"
4091 $Script:AdamjCompressUpdateRevisionsOutputHTML += $AdamjCompressUpdateRevisionsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
4092 $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})
4093 $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})
4094
4095 # Variables Output
4096 # $AdamjCompressUpdateRevisionsOutputTXT
4097 # $AdamjCompressUpdateRevisionsOutputHTML
4098}
4099#endregion CompressUpdateRevisions Function
4100
4101#region RemoveObsoleteUpdates Function
4102################################
4103# Adamj Remove Obsolete #
4104# Updates Stream #
4105################################
4106
4107function RemoveObsoleteUpdates {
4108 Param (
4109 )
4110 $DateNow = Get-Date
4111 $AdamjRemoveObsoleteUpdatesSQLScript = @"
4112USE SUSDB;
4113GO
4114-- SET NOCOUNT ON added to prevent extra result sets from
4115-- interfering with SELECT statements.
4116SET NOCOUNT ON
4117
4118DECLARE @var1 INT, @curitem INT, @totaltoremove INT
4119DECLARE @msg nvarchar(200)
4120
4121IF EXISTS (
4122 SELECT * FROM tempdb.dbo.sysobjects o
4123 WHERE o.xtype IN ('U')
4124 AND o.id = object_id(N'tempdb..#results')
4125)
4126DROP TABLE #results
4127CREATE TABLE #results (Col1 INT)
4128
4129-- Remove Obsolete Updates
4130INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
4131SET @totaltoremove = (SELECT COUNT(*) FROM #results)
4132SELECT @curitem=1
4133DECLARE WC Cursor FOR SELECT Col1 FROM #results
4134OPEN WC
4135FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
4136BEGIN
4137 SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltoremove as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
4138 RAISERROR(@msg,0,1) WITH NOWAIT
4139 EXEC spDeleteUpdate @localUpdateID=@var1
4140 SET @curitem = @curitem +1
4141 FETCH NEXT FROM WC INTO @var1
4142END
4143CLOSE WC
4144DEALLOCATE WC
4145DROP TABLE #results
4146"@
4147 Write-Output ""
4148 Write-Output "Please be patient, this may take a while."
4149 Write-Output ""
4150 Write-Output "It is not abnormal for this process to take minutes, hours, or days. It varies per install and per execution."
4151 Write-Output ""
4152 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."
4153 Write-Verbose "Create a file with the content of the RemoveObsoleteUpdates Script above in the same working directory as this PowerShell script is running."
4154 $AdamjRemoveObsoleteUpdatesSQLScriptFile = "$AdamjScriptPath\AdamjRemoveObsoleteUpdates.sql"
4155 $AdamjRemoveObsoleteUpdatesSQLScript | Out-File "$AdamjRemoveObsoleteUpdatesSQLScriptFile"
4156 Write-Debug "Just wrote to script file"
4157 # Re-jig the $AdamjSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
4158 $AdamjSQLConnectCommand = $AdamjSQLConnectCommand.Replace('$','`$')
4159 Write-Verbose "Execute the SQL Script and store the results in a variable."
4160 $AdamjRemoveObsoleteUpdatesSQLScriptJobCommand = [scriptblock]::create("$AdamjSQLConnectCommand -i `"$AdamjRemoveObsoleteUpdatesSQLScriptFile`" -I")
4161 Write-Verbose "`$AdamjRemoveObsoleteUpdatesSQLScriptJobCommand = $AdamjRemoveObsoleteUpdatesSQLScriptJobCommand"
4162 $AdamjRemoveObsoleteUpdatesSQLScriptJob = Start-Job -ScriptBlock $AdamjRemoveObsoleteUpdatesSQLScriptJobCommand
4163 Wait-Job $AdamjRemoveObsoleteUpdatesSQLScriptJob
4164 $AdamjRemoveObsoleteUpdatesSQLScriptJobOutput = Receive-Job $AdamjRemoveObsoleteUpdatesSQLScriptJob
4165 Write-Debug "Just finished - check AdamjRemoveObsoleteUpdatesSQLScriptJobOutput"
4166 Remove-Job $AdamjRemoveObsoleteUpdatesSQLScriptJob
4167 Write-Verbose "Remove the SQL Script file."
4168 Remove-Item "$AdamjRemoveObsoleteUpdatesSQLScriptFile"
4169 $FinishedRunning = Get-Date
4170 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
4171 # Setup variables to store the output to be added at the very end of the script for logging purposes.
4172 $Script:AdamjRemoveObsoleteUpdatesOutputTXT += "Adamj Remove Obsolete Updates:`r`n`r`n"
4173 $Script:AdamjRemoveObsoleteUpdatesOutputTXT += $AdamjRemoveObsoleteUpdatesSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
4174 $Script:AdamjRemoveObsoleteUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Remove Obsolete Updates:</span></p>`n`n"
4175 $Script:AdamjRemoveObsoleteUpdatesOutputHTML += $AdamjRemoveObsoleteUpdatesSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
4176 $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})
4177 $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})
4178
4179 # Variables Output
4180 # $AdamjRemoveObsoleteUpdatesOutputTXT
4181 # $AdamjRemoveObsoleteUpdatesOutputHTML
4182}
4183#endregion RemoveObsoleteUpdates Function
4184
4185#region WSUSDBMaintenance Function
4186################################
4187# Adamj WSUS DB Maintenance #
4188# Stream #
4189################################
4190
4191function WSUSDBMaintenance {
4192 Param (
4193 [Switch]$NoOutput
4194 )
4195 $DateNow = Get-Date
4196 $AdamjWSUSDBMaintenanceSQLScript = @"
4197/*
4198################################
4199# Adamj WSUSDBMaintenance #
4200# SQL Script #
4201# Version 1.0 #
4202# Taken from TechNet #
4203# referenced below. #
4204# #
4205# Adam Marshall #
4206# http://www.adamj.org #
4207################################
4208*/
4209-- Taken from https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61
4210
4211/******************************************************************************
4212This sample T-SQL script performs basic maintenance tasks on SUSDB
42131. Identifies indexes that are fragmented and defragments them. For certain
4214 tables, a fill-factor is set in order to improve insert performance.
4215 Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx
4216 and tailored for SUSDB requirements
42172. Updates potentially out-of-date table statistics.
4218******************************************************************************/
4219
4220USE SUSDB;
4221GO
4222SET NOCOUNT ON;
4223
4224-- Rebuild or reorganize indexes based on their fragmentation levels
4225DECLARE @work_to_do TABLE (
4226 objectid int
4227 , indexid int
4228 , pagedensity float
4229 , fragmentation float
4230 , numrows int
4231)
4232
4233DECLARE @objectid int;
4234DECLARE @indexid int;
4235DECLARE @schemaname nvarchar(130);
4236DECLARE @objectname nvarchar(130);
4237DECLARE @indexname nvarchar(130);
4238DECLARE @numrows int
4239DECLARE @density float;
4240DECLARE @fragmentation float;
4241DECLARE @command nvarchar(4000);
4242DECLARE @fillfactorset bit
4243DECLARE @numpages int
4244
4245-- Select indexes that need to be defragmented based on the following
4246-- * Page density is low
4247-- * External fragmentation is high in relation to index size
4248PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
4249INSERT @work_to_do
4250SELECT
4251 f.object_id
4252 , index_id
4253 , avg_page_space_used_in_percent
4254 , avg_fragmentation_in_percent
4255 , record_count
4256FROM
4257 sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
4258WHERE
4259 (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
4260 or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)
4261 or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)
4262
4263PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))
4264
4265PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)
4266
4267SELECT @numpages = sum(ps.used_page_count)
4268FROM
4269 @work_to_do AS fi
4270 INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
4271 INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
4272
4273-- Declare the cursor for the list of indexes to be processed.
4274DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
4275
4276-- Open the cursor.
4277OPEN curIndexes
4278
4279-- Loop through the indexes
4280WHILE (1=1)
4281BEGIN
4282 FETCH NEXT FROM curIndexes
4283 INTO @objectid, @indexid, @density, @fragmentation, @numrows;
4284 IF @@FETCH_STATUS < 0 BREAK;
4285
4286 SELECT
4287 @objectname = QUOTENAME(o.name)
4288 , @schemaname = QUOTENAME(s.name)
4289 FROM
4290 sys.objects AS o
4291 INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
4292 WHERE
4293 o.object_id = @objectid;
4294
4295 SELECT
4296 @indexname = QUOTENAME(name)
4297 , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
4298 FROM
4299 sys.indexes
4300 WHERE
4301 object_id = @objectid AND index_id = @indexid;
4302
4303 IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
4304 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
4305 ELSE IF @numrows >= 5000 AND @fillfactorset = 0
4306 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
4307 ELSE
4308 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
4309 PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
4310 EXEC (@command);
4311 PRINT convert(nvarchar, getdate(), 121) + N' Done.';
4312END
4313
4314-- Close and deallocate the cursor.
4315CLOSE curIndexes;
4316DEALLOCATE curIndexes;
4317
4318IF EXISTS (SELECT * FROM @work_to_do)
4319BEGIN
4320 PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
4321 SELECT @numpages = @numpages - sum(ps.used_page_count)
4322 FROM
4323 @work_to_do AS fi
4324 INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
4325 INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
4326 PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
4327END
4328GO
4329
4330--Update all statistics
4331PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
4332EXEC sp_updatestats
4333PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
4334GO
4335"@
4336 Write-Verbose "Create a file with the content of the WSUSDBMaintenance Script above in the same working directory as this PowerShell script is running."
4337 $AdamjWSUSDBMaintenanceSQLScriptFile = "$AdamjScriptPath\AdamjWSUSDBMaintenance.sql"
4338 $AdamjWSUSDBMaintenanceSQLScript | Out-File "$AdamjWSUSDBMaintenanceSQLScriptFile"
4339
4340 # Re-jig the $AdamjSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
4341 $AdamjSQLConnectCommand = $AdamjSQLConnectCommand.Replace('$','`$')
4342 Write-Verbose "Execute the SQL Script and store the results in a variable."
4343 $AdamjWSUSDBMaintenanceSQLScriptJobCommand = [scriptblock]::create("$AdamjSQLConnectCommand -i `"$AdamjWSUSDBMaintenanceSQLScriptFile`" -I")
4344 Write-Verbose "`$AdamjWSUSDBMaintenanceSQLScriptJobCommand = $AdamjWSUSDBMaintenanceSQLScriptJobCommand"
4345 $AdamjWSUSDBMaintenanceSQLScriptJob = Start-Job -ScriptBlock $AdamjWSUSDBMaintenanceSQLScriptJobCommand
4346 Wait-Job $AdamjWSUSDBMaintenanceSQLScriptJob
4347 $AdamjWSUSDBMaintenanceSQLScriptJobOutput = Receive-Job $AdamjWSUSDBMaintenanceSQLScriptJob
4348 Remove-Job $AdamjWSUSDBMaintenanceSQLScriptJob
4349 Write-Verbose "Remove the SQL Script file."
4350 Remove-Item "$AdamjWSUSDBMaintenanceSQLScriptFile"
4351 $FinishedRunning = Get-Date
4352 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
4353 # Setup variables to store the output to be added at the very end of the script for logging purposes.
4354 if ($NoOutput -eq $False) {
4355 $Script:AdamjWSUSDBMaintenanceOutputTXT += "Adamj WSUS DB Maintenance:`r`n`r`n"
4356 $Script:AdamjWSUSDBMaintenanceOutputTXT += $AdamjWSUSDBMaintenanceSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
4357 $Script:AdamjWSUSDBMaintenanceOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj WSUS DB Maintenance:</span></p>`n`n"
4358 $Script:AdamjWSUSDBMaintenanceOutputHTML += $AdamjWSUSDBMaintenanceSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
4359 } else {
4360 $Script:AdamjWSUSDBMaintenanceOutputTXT += "Adamj WSUS DB Maintenance:`r`n`r`n"
4361 $Script:AdamjWSUSDBMaintenanceOutputTXT += "The Adamj WSUS DB Maintenance Stream was run with the -NoOutput switch.`r`n"
4362 $Script:AdamjWSUSDBMaintenanceOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj WSUS DB Maintenance:</span></p>`n`n"
4363 $Script:AdamjWSUSDBMaintenanceOutputHTML += "<p>The Adamj WSUS DB Maintenance Stream was run with the -NoOutput switch.</p>`n`n"
4364 }
4365 $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})
4366 $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})
4367
4368 # Variables Output
4369 # $AdamjWSUSDBMaintenanceOutputTXT
4370 # $AdamjWSUSDBMaintenanceOutputHTML
4371}
4372#endregion WSUSDBMaintenance Function
4373
4374#region DeclineSupersededUpdates Function
4375################################
4376# Adamj Decline Superseded #
4377# Updates Stream #
4378################################
4379
4380function DeclineSupersededUpdates {
4381 param (
4382 [Switch]$Display,
4383 [Switch]$Proceed
4384 )
4385 # Log the date first
4386 $DateNow = Get-Date
4387 Write-Verbose "Create an update scope"
4388 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
4389 Write-Verbose "By default the update scope is created for any approval states"
4390 $UpdateScope.ApprovedStates = "Any"
4391 Write-Verbose "Get all updates that have been superseded and not declined"
4392 $AdamjDeclineSupersededUpdatesUpdates = $AdamjWSUSServerAdminProxy.GetUpdates($UpdateScope) | Where { ($_.IsSuperseded) -and -not ($_.isDeclined) }
4393 function DeclineSupersededUpdatesCountUpdates {
4394 Write-Verbose "First count how many updates will be declined by declining superseded Updates - just for fun. I like fun :)"
4395 $Script:AdamjDeclineSupersededUpdatesCountUpdatesCount = "{0:N0}" -f $AdamjDeclineSupersededUpdatesUpdates.Count
4396 $Script:AdamjDeclineSupersededUpdatesCountUpdatesOutputTXT += "The number of superseded updates that would be declined is: $AdamjDeclineSupersededUpdatesCountUpdatesCount.`r`n"
4397 $Script:AdamjDeclineSupersededUpdatesCountUpdatesOutputHTML += "<p>The number of superseded updates that would be declined is: $AdamjDeclineSupersededUpdatesCountUpdatesCount.</p>`n"
4398
4399 # Variables Output
4400 # $AdamjDeclineSupersededUpdatesCountUpdatesOutputTXT
4401 # $AdamjDeclineSupersededUpdatesCountUpdatesOutputTXT
4402 }
4403 function DeclineSupersededUpdatesDisplayUpdates {
4404 Write-Verbose "Display the titles of the Superseded updates that will be declined - just for fun. I like fun :)"
4405 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "<ol>`n"
4406 $Count=0
4407 ForEach ($update in $AdamjDeclineSupersededUpdatesUpdates) {
4408 $Count++
4409 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputTXT += "$($Count). $($update.title) - https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`r`n"
4410 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "<li><a href=`"https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`">$($update.title)</a></li>`n"
4411 }
4412 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputTXT += "`r`n"
4413 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "</ol>`n"
4414
4415 # Variables Output
4416 # $AdamjDeclineSupersededUpdatesUpdatesDisplayOutputTXT
4417 # $AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML
4418 }
4419 function DeclineSupersededUpdatesProceed {
4420 $Script:AdamjDeclineSupersededUpdatesProceedOutputTXT += "You've chosen to Decline Superseded Updates. Declining $AdamjDeclineSupersededUpdatesCountUpdatesCount Superseded updates.`r`n`r`n"
4421 $Script:AdamjDeclineSupersededUpdatesProceedOutputHTML += "<p>You've chosen to Decline Superseded Updates. <strong>Declining $AdamjDeclineSupersededUpdatesCountUpdatesCount Superseded updates.</strong></p>`n"
4422 Write-Verbose "Decline these updates"
4423 $AdamjDeclineSupersededUpdatesUpdates | ForEach-Object -Process { $_.Decline() }
4424
4425 # Variables Output
4426 # $AdamjDeclineSupersededUpdatesProceedOutputTXT
4427 # $AdamjDeclineSupersededUpdatesProceedOutputHTML
4428 }
4429
4430 DeclineSupersededUpdatesCountUpdates
4431 if ($Display -ne $False) { DeclineSupersededUpdatesDisplayUpdates }
4432 if ($Proceed -ne $False) { DeclineSupersededUpdatesProceed }
4433 $FinishedRunning = Get-Date
4434 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
4435
4436 $Script:AdamjDeclineSupersededUpdatesOutputTXT += "Adamj Decline Superseded Updates:`r`n"
4437 $Script:AdamjDeclineSupersededUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Decline Superseded Updates:</span><br>`n"
4438 $Script:AdamjDeclineSupersededUpdatesOutputTXT += $AdamjDeclineSupersededUpdatesCountUpdatesOutputTXT
4439 $Script:AdamjDeclineSupersededUpdatesOutputHTML += $AdamjDeclineSupersededUpdatesCountUpdatesOutputHTML
4440 if ($Display -ne $False) {
4441 $Script:AdamjDeclineSupersededUpdatesOutputTXT += $AdamjDeclineSupersededUpdatesUpdatesDisplayOutputTXT
4442 $Script:AdamjDeclineSupersededUpdatesOutputHTML += $AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML
4443 }
4444 if ($Proceed -ne $False) {
4445 $Script:AdamjDeclineSupersededUpdatesOutputTXT += $AdamjDeclineSupersededUpdatesProceedOutputTXT
4446 $Script:AdamjDeclineSupersededUpdatesOutputHTML += $AdamjDeclineSupersededUpdatesProceedOutputHTML
4447 }
4448 $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})
4449 $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})
4450}
4451#endregion DeclineSupersededUpdates Function
4452
4453#region CleanUpWSUSSynchronizationLogs Function
4454################################
4455# Clean Up WSUS #
4456# Synchronization Logs Stream #
4457################################
4458
4459function CleanUpWSUSSynchronizationLogs {
4460 Param(
4461 [Int]$ConsistencyNumber,
4462 [String]$ConsistencyTime,
4463 [Switch]$All
4464 )
4465 $DateNow = Get-Date
4466 $AdamjCleanUpWSUSSynchronizationLogsSQLScript = @"
4467/*
4468################################
4469# Adamj WSUS Synchronization #
4470# Cleanup SQL Script #
4471# Version 1.0 #
4472# Taken from various sources #
4473# from the Internet. #
4474# #
4475# Modified By: Adam Marshall #
4476# http://www.adamj.org #
4477################################
4478*/
4479$(
4480 if ($ConsistencyNumber -ne "0") {
4481 $("
4482USE SUSDB
4483GO
4484DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389') AND DATEDIFF($($ConsistencyTime), TimeAtServer, CURRENT_TIMESTAMP) >= $($ConsistencyNumber);
4485GO")
4486}
4487elseif ($All -ne $False) {
4488$("USE SUSDB
4489GO
4490DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389')
4491GO")
4492}
4493)
4494"@
4495 Write-Verbose "Create a file with the content of the AdamjCleanUpWSUSSynchronizationLogs Script above in the same working directory as this PowerShell script is running."
4496 $AdamjCleanUpWSUSSynchronizationLogsSQLScriptFile = "$AdamjScriptPath\AdamjCleanUpWSUSSynchronizationLogs.sql"
4497 $AdamjCleanUpWSUSSynchronizationLogsSQLScript | Out-File "$AdamjCleanUpWSUSSynchronizationLogsSQLScriptFile"
4498 # Re-jig the $AdamjSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
4499 $AdamjSQLConnectCommand = $AdamjSQLConnectCommand.Replace('$','`$')
4500 Write-Verbose "Execute the SQL Script and store the results in a variable."
4501 $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobCommand = [scriptblock]::create("$AdamjSQLConnectCommand -i `"$AdamjCleanUpWSUSSynchronizationLogsSQLScriptFile`" -I")
4502 Write-Verbose "`$AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobCommand = $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobCommand"
4503 $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJob = Start-Job -ScriptBlock $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobCommand
4504 Wait-Job $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJob
4505 $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobOutput = Receive-Job $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJob
4506 Remove-Job $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJob
4507 Write-Verbose "Remove the SQL Script file."
4508 Remove-Item "$AdamjCleanUpWSUSSynchronizationLogsSQLScriptFile"
4509 $FinishedRunning = Get-Date
4510 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
4511
4512 # Setup variables to store the output to be added at the very end of the script for logging purposes.
4513 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT += "Adamj Clean Up WSUS Synchornization Logs:`r`n`r`n"
4514 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT += $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
4515 $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})
4516
4517 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Clean Up WSUS Synchornization Logs:</span></p>`r`n"
4518 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML += $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
4519 $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})
4520
4521 # Variables Output
4522 # $AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT
4523 # $AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML
4524
4525}
4526#endregion CleanUpWSUSSynchronizationLogs Function
4527
4528#region ComputerObjectCleanup Function
4529################################
4530# Computer Object Cleanup #
4531# Stream #
4532################################
4533
4534function ComputerObjectCleanup {
4535 $DateNow = Get-Date
4536 Write-Verbose "Create a new timespan using `$AdamjComputerObjectCleanupSearchDays and find how many computers need to be cleaned up"
4537 $AdamjComputerObjectCleanupSearchTimeSpan = New-Object timespan($AdamjComputerObjectCleanupSearchDays,0,0,0)
4538 $AdamjComputerObjectCleanupScope = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope
4539 $AdamjComputerObjectCleanupScope.ToLastSyncTime = [DateTime]::UtcNow.Subtract($AdamjComputerObjectCleanupSearchTimeSpan)
4540 $AdamjComputerObjectCleanupSet = $AdamjWSUSServerAdminProxy.GetComputerTargets($AdamjComputerObjectCleanupScope) | Sort-Object FullDomainName
4541 Write-Verbose "Clean up $($AdamjComputerObjectCleanupSet.Count) computer objects"
4542 $AdamjWSUSServerAdminProxy.GetComputerTargets($AdamjComputerObjectCleanupScope) | ForEach-Object { $_.Delete() }
4543
4544 $FinishedRunning = Get-Date
4545 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
4546
4547 # Setup variables to store the output to be added at the very end of the script for logging purposes.
4548 $Script:AdamjComputerObjectCleanupOutputTXT += "Adamj Computer Object Cleanup:`r`n`r`n"
4549 if ($($AdamjComputerObjectCleanupSet.Count) -gt "0") {
4550 $Script:AdamjComputerObjectCleanupOutputTXT += "The following $($AdamjComputerObjectCleanupSet.Count) $(if ($($AdamjComputerObjectCleanupSet.Count) -eq "1") { "computer" } else { "computers" }) have been removed."
4551 $Script:AdamjComputerObjectCleanupOutputTXT += $AdamjComputerObjectCleanupSet | Select-Object FullDomainName,@{Expression=" "},LastSyncTime | Format-Table -AutoSize | Out-String
4552 } else { $Script:AdamjComputerObjectCleanupOutputTXT += "There are no computers to clean up.`r`n" }
4553
4554 $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})
4555 $Script:AdamjComputerObjectCleanupOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Computer Object Cleanup:</span></p>`r`n"
4556 if ($($AdamjComputerObjectCleanupSet.Count) -gt "0") {
4557 $Script:AdamjComputerObjectCleanupOutputHTML += "<p>The following $($AdamjComputerObjectCleanupSet.Count) $(if ($($AdamjComputerObjectCleanupSet.Count) -eq "1") { "computer" } else { "computers" }) have been removed.</p>"
4558 $Script:AdamjComputerObjectCleanupOutputHTML += ($AdamjComputerObjectCleanupSet | Select-Object FullDomainName,LastSyncTime | ConvertTo-Html -Fragment) -replace "\<table\>",'<table class="gridtable">'
4559 } else { $Script:AdamjComputerObjectCleanupOutputHTML += "<p>There are no computers to clean up.</p>" }
4560 $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})
4561
4562 # Variables Output
4563 # $AdamjComputerObjectCleanupOutputTXT
4564 # $AdamjComputerObjectCleanupOutputHTML
4565}
4566
4567#endregion ComputerObjectCleanup Function
4568
4569#region WSUSServerCleanupWizard Function
4570################################
4571# WSUS Server Cleanup Wizard #
4572# Stream #
4573################################
4574
4575function WSUSServerCleanupWizard {
4576 $DateNow = Get-Date
4577 $WSUSServerCleanupWizardBody = "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUS Server Cleanup Wizard:</span></p>" | Out-String
4578 $CleanupManager = $AdamjWSUSServerAdminProxy.GetCleanupManager();
4579 $CleanupScope = New-Object Microsoft.UpdateServices.Administration.CleanupScope ($AdamjSCWSupersededUpdatesDeclined,$AdamjSCWExpiredUpdatesDeclined,$AdamjSCWObsoleteUpdatesDeleted,$AdamjSCWUpdatesCompressed,$AdamjSCWObsoleteComputersDeleted,$AdamjSCWUnneededContentFiles);
4580 $AdamjCleanupResults = $CleanupManager.PerformCleanup($CleanupScope)
4581 $FinishedRunning = Get-Date
4582 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
4583
4584 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "Adamj WSUS Server Cleanup Wizard:`r`n`r`n"
4585 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "$AdamjWSUSServer`r`n"
4586 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "Version: $($AdamjWSUSServerAdminProxy.Version)`r`n"
4587 #$Script:AdamjWSUSServerCleanupWizardOutputTXT += "Started: $($DateNow.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))`r`n"
4588 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "SupersededUpdatesDeclined: $($AdamjCleanupResults.SupersededUpdatesDeclined)`r`n"
4589 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "ExpiredUpdatesDeclined: $($AdamjCleanupResults.ExpiredUpdatesDeclined)`r`n"
4590 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "ObsoleteUpdatesDeleted: $($AdamjCleanupResults.ObsoleteUpdatesDeleted)`r`n"
4591 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "UpdatesCompressed: $($AdamjCleanupResults.UpdatesCompressed)`r`n"
4592 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "ObsoleteComputersDeleted: $($AdamjCleanupResults.ObsoleteComputersDeleted)`r`n"
4593 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "DiskSpaceFreed (MB): $([math]::round($AdamjCleanupResults.DiskSpaceFreed/1MB, 2))`r`n"
4594 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "DiskSpaceFreed (GB): $([math]::round($AdamjCleanupResults.DiskSpaceFreed/1GB, 2))`r`n"
4595 #$Script:AdamjWSUSServerCleanupWizardOutputTXT += "Finished: $($FinishedRunning.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))`r`n"
4596 $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})
4597
4598 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj WSUS Server Cleanup Wizard:</span></p>`r`n"
4599 #$Script:AdamjWSUSServerCleanupWizardOutputHTML += $AdamjCSSStyling + "`r`n"
4600 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<table class=`"gridtable`">`r`n"
4601 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tbody>`r`n"
4602 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><th colspan=`"2`" rowspan=`"1`">$AdamjWSUSServer</th></tr>`r`n"
4603 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>Version:</td><td>$($AdamjWSUSServerAdminProxy.Version)</td></tr>`r`n"
4604 #$Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>Started:</td><td>$($DateNow.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))</td></tr>`r`n"
4605 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>SupersededUpdatesDeclined:</td><td>$($AdamjCleanupResults.SupersededUpdatesDeclined)</td></tr>`r`n"
4606 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>ExpiredUpdatesDeclined:</td><td>$($AdamjCleanupResults.ExpiredUpdatesDeclined)</td></tr>`r`n"
4607 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>ObsoleteUpdatesDeleted:</td><td>$($AdamjCleanupResults.ObsoleteUpdatesDeleted)</td></tr>`r`n"
4608 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>UpdatesCompressed:</td><td>$($AdamjCleanupResults.UpdatesCompressed)</td></tr>`r`n"
4609 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>ObsoleteComputersDeleted:</td><td>$($AdamjCleanupResults.ObsoleteComputersDeleted)</td></tr>`r`n"
4610 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>DiskSpaceFreed (MB):</td><td>$([math]::round($AdamjCleanupResults.DiskSpaceFreed/1MB, 2))</td></tr>`r`n"
4611 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>DiskSpaceFreed (GB):</td><td>$([math]::round($AdamjCleanupResults.DiskSpaceFreed/1GB, 2))</td></tr>`r`n"
4612 #$Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>Finished:</td><td>$($FinishedRunning.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))</td></tr>`r`n"
4613 $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})
4614 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "</tbody>`r`n"
4615 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "</table>`r`n"
4616
4617 # Variables Output
4618 # $AdamjWSUSServerCleanupWizardOutputTXT
4619 # $AdamjWSUSServerCleanupWizardOutputHTML
4620}
4621#endregion WSUSServerCleanupWizard Function
4622
4623#region AdamjScriptDifferenceInTime Function
4624function AdamjScriptDifferenceInTime {
4625 $AdamjScriptFinishedRunning = Get-Date
4626 $Script:AdamjScriptDifferenceInTime = New-TimeSpan –Start $AdamjScriptTime –End $AdamjScriptFinishedRunning
4627}
4628#endregion AdamjScriptDifferenceInTime Function
4629
4630#region Create The CSS Styling
4631################################
4632# Create the CSS Styling #
4633################################
4634
4635$AdamjCSSStyling =@"
4636<style type="text/css">
4637table.gridtable {
4638 font-family: verdana,arial,sans-serif;
4639 font-size:11px;
4640 color:#333333;
4641 border-width: 1px;
4642 border-color: #666666;
4643 border-collapse: collapse;
4644}
4645table.gridtable th {
4646 border-width: 1px;
4647 padding: 8px;
4648 border-style: solid;
4649 border-color: #666666;
4650 background-color: #dedede;
4651}
4652table.gridtable td {
4653 border-width: 1px;
4654 padding: 8px;
4655 border-style: solid;
4656 border-color: #666666;
4657 background-color: #ffffff;
4658}
4659.TFtable{
4660 border-collapse:collapse;
4661}
4662.TFtable td{
4663 padding:7px;
4664 border:#4e95f4 1px solid;
4665}
4666
4667/* provide some minimal visual accommodation for IE8 and below */
4668.TFtable tr{
4669 background: #b8d1f3;
4670}
4671/* Define the background color for all the ODD background rows */
4672.TFtable tr:nth-child(odd){
4673 background: #b8d1f3;
4674}
4675/* Define the background color for all the EVEN background rows */
4676.TFtable tr:nth-child(even){
4677 background: #dae5f4;
4678}
4679.error {
4680border: 2px solid;
4681margin: 10px 10px;
4682padding:15px 50px 15px 50px;
4683}
4684.error ol {
4685color: #D8000C;
4686}
4687.error ol li p {
4688color: #000;
4689background-color: transparent;
4690}
4691.error ol li {
4692background-color: #FFBABA;
4693margin: 10px 0;
4694}
4695</style>
4696"@
4697#endregion Create The CSS Styling
4698
4699#region Create The Output
4700################################
4701# Create the TXT output #
4702################################
4703
4704function CreateBodyTXT {
4705 $Script:AdamjBodyTXT = "`n"
4706 $Script:AdamjBodyTXT += $AdamjBodyHeaderTXT
4707 $Script:AdamjBodyTXT += $AdamjConnectedTXT
4708 $Script:AdamjBodyTXT += $AdamjRemoveObsoleteUpdatesOutputTXT
4709 $Script:AdamjBodyTXT += $AdamjCompressUpdateRevisionsOutputTXT
4710 $Script:AdamjBodyTXT += $AdamjDeclineSupersededUpdatesOutputTXT
4711 $Script:AdamjBodyTXT += $AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT
4712 $Script:AdamjBodyTXT += $AdamjRemoveWSUSDriversOutputTXT
4713 $Script:AdamjBodyTXT += $AdamjRemoveDeclinedWSUSUpdatesOutputTXT
4714 $Script:AdamjBodyTXT += $AdamjComputerObjectCleanupOutputTXT
4715 $Script:AdamjBodyTXT += $AdamjWSUSDBMaintenanceOutputTXT
4716 $Script:AdamjBodyTXT += $AdamjWSUSServerCleanupWizardOutputTXT
4717 $Script:AdamjBodyTXT += "Clean-WSUS Script Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($AdamjScriptDifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
4718 $Script:AdamjBodyTXT += $AdamjBodyFooterTXT
4719}
4720
4721################################
4722# Create the HTML output #
4723################################
4724
4725function CreateBodyHTML {
4726 $Script:AdamjBodyHTML = "`n"
4727 $Script:AdamjBodyHTML += $AdamjCSSStyling
4728 $Script:AdamjBodyHTML += $AdamjBodyHeaderHTML
4729 $Script:AdamjBodyHTML += $AdamjConnectedHTML
4730 $Script:AdamjBodyHTML += $AdamjRemoveObsoleteUpdatesOutputHTML
4731 $Script:AdamjBodyHTML += $AdamjCompressUpdateRevisionsOutputHTML
4732 $Script:AdamjBodyHTML += $AdamjDeclineSupersededUpdatesOutputHTML
4733 $Script:AdamjBodyHTML += $AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML
4734 $Script:AdamjBodyHTML += $AdamjRemoveWSUSDriversOutputHTML
4735 $Script:AdamjBodyHTML += $AdamjRemoveDeclinedWSUSUpdatesOutputHTML
4736 $Script:AdamjBodyHTML += $AdamjComputerObjectCleanupOutputHTML
4737 $Script:AdamjBodyHTML += $AdamjWSUSDBMaintenanceOutputHTML
4738 $Script:AdamjBodyHTML += $AdamjWSUSServerCleanupWizardOutputHTML
4739 $Script:AdamjBodyHTML += "<p>Clean-WSUS Script Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($AdamjScriptDifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
4740 $Script:AdamjBodyHTML += $AdamjBodyFooterHTML
4741}
4742#endregion Create The Output
4743
4744#region SaveReport
4745################################
4746# Save the Report #
4747################################
4748
4749function SaveReport {
4750 Param(
4751 [ValidateSet("TXT","HTML")]
4752 [String]$ReportType = "TXT"
4753 )
4754 if ($ReportType -eq "HTML") {
4755 $AdamjBodyHTML | Out-File -FilePath "$AdamjScriptPath\$(get-date -f "yyyy.MM.dd-HH.mm.ss").htm"
4756 } else {
4757 $AdamjBodyTXT | Out-File -FilePath "$AdamjScriptPath\$(get-date -f "yyyy.MM.dd-HH.mm.ss").txt"
4758 }
4759}
4760#endregion SaveReport
4761
4762#region MailReport
4763################################
4764# Mail the Report #
4765################################
4766
4767function MailReport {
4768 param (
4769 [ValidateSet("TXT","HTML")]
4770 [String] $MessageContentType = "HTML"
4771 )
4772 $message = New-Object System.Net.Mail.MailMessage
4773 $mailer = New-Object System.Net.Mail.SmtpClient ($AdamjMailReportSMTPServer, $AdamjMailReportSMTPPort)
4774 $mailer.EnableSSL = $AdamjMailReportSMTPServerEnableSSL
4775 if ($AdamjMailReportSMTPServerUsername -ne "") {
4776 $mailer.Credentials = New-Object System.Net.NetworkCredential($AdamjMailReportSMTPServerUsername, $AdamjMailReportSMTPServerPassword)
4777 }
4778 $message.From = $AdamjMailReportEmailFromAddress
4779 $message.To.Add($AdamjMailReportEmailToAddress)
4780 $message.Subject = $AdamjMailReportEmailSubject
4781 $message.Body = if ($MessageContentType -eq "HTML") { $AdamjBodyHTML } else { $AdamjBodyTXT }
4782 $message.IsBodyHtml = if ($MessageContentType -eq "HTML") { $True } else { $False }
4783 $mailer.send(($message))
4784}
4785#endregion MailReport
4786
4787#region HelpMe
4788################################
4789# Help Me #
4790################################
4791
4792function HelpMe {
4793 ((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()
4794 Write-Output "PowerShell Version: $($PSVersionTable.PSVersion.ToString())"
4795 Write-Output "WSUS Version: $($AdamjWSUSServerAdminProxy.Version)"
4796 Write-Output "Replica Server: $($AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer)"
4797 Write-Output "The path to the WSUS Content folder is: $($AdamjWSUSServerAdminProxy.GetConfiguration().LocalContentCachePath)"
4798 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)"
4799 Write-Output "All Volumes on the WSUS Server:"
4800 (Get-DiskFree -Format | Out-String).Trim()
4801 Write-Output ".NET Installed Versions"
4802 (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()
4803 Write-Output "============================="
4804 Write-Output "All My Functions"
4805 Write-Output "============================="
4806 Show-MyFunctions
4807 Write-Output "============================="
4808 Write-Output "All My Variables"
4809 Write-Output "============================="
4810 Show-MyVariables
4811 Write-Output "============================="
4812 Write-Output " End of HelpMe Stream"
4813 Write-Output "============================="
4814
4815}
4816#endregion HelpMe
4817
4818#region Process The Functions
4819################################
4820# Process the Functions #
4821################################
4822
4823if ($FirstRun -eq $True) {
4824 CreateAdamjHeader
4825 Write-Output "Executing RemoveWSUSDrivers"
4826 RemoveWSUSDrivers -SQL
4827 Write-Output "Executing RemoveObsoleteUpdates"
4828 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."}
4829 Write-Output "Executing CompressUpdateRevisions"
4830 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."}
4831 Write-Output "Executing DeclineSupersededUpdates"
4832 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."}
4833 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
4834 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
4835 if ($AdamjComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
4836 Write-Output "Executing WSUSDBMaintenance"
4837 WSUSDBMaintenance
4838 Write-Output "Executing WSUSServerCleanupWizard"
4839 WSUSServerCleanupWizard
4840 CreateAdamjFooter
4841 AdamjScriptDifferenceInTime
4842 CreateBodyTXT
4843 CreateBodyHTML
4844 if ($AdamjMailReport -eq $True) { MailReport $AdamjMailReportType }
4845 SaveReport
4846}
4847if ($MonthlyRun -eq $True) {
4848 CreateAdamjHeader
4849 Write-Output "Executing RemoveObsoleteUpdates"
4850 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."}
4851 Write-Output "Executing CompressUpdateRevisions"
4852 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."}
4853 Write-Output "Executing DeclineSupersededUpdates"
4854 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."}
4855 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
4856 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
4857 if ($AdamjComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
4858 Write-Output "Executing WSUSDBMaintenance"
4859 WSUSDBMaintenance
4860 Write-Output "Executing WSUSServerCleanupWizard"
4861 WSUSServerCleanupWizard
4862 CreateAdamjFooter
4863 AdamjScriptDifferenceInTime
4864 CreateBodyTXT
4865 CreateBodyHTML
4866 if ($AdamjMailReport -eq $True) { MailReport $AdamjMailReportType }
4867 if ($AdamjSaveReport -eq $True) { SaveReport $AdamjSaveReportType }
4868}
4869if ($QuarterlyRun -eq $True) {
4870 CreateAdamjHeader
4871 Write-Output "Executing RemoveObsoleteUpdates"
4872 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."}
4873 Write-Output "Executing CompressUpdateRevisions"
4874 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."}
4875 Write-Output "Executing DeclineSupersededUpdates"
4876 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."}
4877 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
4878 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
4879 Write-Output "Executing RemoveWSUSDrivers"
4880 RemoveWSUSDrivers
4881 Write-Output "Executing RemoveDeclinedWSUSUpdates"
4882 RemoveDeclinedWSUSUpdates -Display -Proceed
4883 if ($AdamjComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
4884 Write-Output "Executing WSUSDBMaintenance"
4885 WSUSDBMaintenance
4886 Write-Output "Executing WSUSServerCleanupWizard"
4887 WSUSServerCleanupWizard
4888 CreateAdamjFooter
4889 AdamjScriptDifferenceInTime
4890 CreateBodyTXT
4891 CreateBodyHTML
4892 if ($AdamjMailReport -eq $True) { MailReport $AdamjMailReportType }
4893 if ($AdamjSaveReport -eq $True) { SaveReport $AdamjSaveReportType }
4894}
4895if ($ScheduledRun -eq $True) {
4896 $DateNow = Get-Date
4897 CreateAdamjHeader
4898 if ($AdamjScheduledRunStreamsDay -gt 31 -or $AdamjScheduledRunStreamsDay -eq 0) { Write-Output 'You failed to set a valid value for $AdamjScheduledRunStreamsDay. Setting to 31'; $AdamjScheduledRunStreamsDay = 31 }
4899 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."} }
4900 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."} }
4901 Write-Output "Executing DeclineSupersededUpdates"
4902 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."} }
4903 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
4904 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
4905 $AdamjScheduledRunQuarterlyMonths.Split(",") | ForEach-Object {
4906 if ($_ -eq $DateNow.Month) {
4907 if ($_ -eq 2) {
4908 if ($AdamjScheduledRunStreamsDay -gt 28 -and [System.DateTime]::isleapyear($DateNow.Year) -eq $True) { $AdamjScheduledRunStreamsDay = 29 }
4909 else { $AdamjScheduledRunStreamsDay = 28 }
4910 }
4911 if (4,6,9,11 -contains $_ -and $AdamjScheduledRunStreamsDay -gt 30) { $AdamjScheduledRunStreamsDay = 30 }
4912 if ($AdamjScheduledRunStreamsDay -eq $DateNow.Day) {
4913 Write-Output "Executing RemoveWSUSDrivers"
4914 RemoveWSUSDrivers
4915 Write-Output "Executing RemoveDeclinedWSUSUpdates"
4916 RemoveDeclinedWSUSUpdates -Display -Proceed
4917 }
4918 }
4919 }
4920 if ($AdamjComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
4921 Write-Output "Executing WSUSDBMaintenance"
4922 if ($AdamjScheduledRunStreamsDay -eq $DateNow.Day) { WSUSDBMaintenance } else { WSUSDBMaintenance -NoOutput }
4923 Write-Output "Executing WSUSServerCleanupWizard"
4924 WSUSServerCleanupWizard
4925 CreateAdamjFooter
4926 AdamjScriptDifferenceInTime
4927 CreateBodyTXT
4928 CreateBodyHTML
4929 if ($AdamjMailReport -eq $True) { MailReport $AdamjMailReportType }
4930 if ($AdamjSaveReport -eq $True) { SaveReport $AdamjSaveReportType }
4931}
4932
4933if ($DailyRun -eq $True) {
4934 CreateAdamjHeader
4935 Write-Output "Executing DeclineSupersededUpdates"
4936 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."}
4937 Write-Output "Executing CleanUpWSUSSynchronizationLogs"
4938 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
4939 if ($AdamjComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
4940 Write-Output "Executing WSUSDBMaintenance"
4941 WSUSDBMaintenance -NoOutput
4942 Write-Output "Executing WSUSServerCleanupWizard"
4943 WSUSServerCleanupWizard
4944 CreateAdamjFooter
4945 AdamjScriptDifferenceInTime
4946 CreateBodyTXT
4947 CreateBodyHTML
4948 if ($AdamjMailReport -eq $True) { MailReport $AdamjMailReportType }
4949 if ($AdamjSaveReport -eq $True) { SaveReport $AdamjSaveReportType }
4950}
4951
4952if (-not $FirstRun -and -not $MonthlyRun -and -not $QuarterlyRun -and -not $ScheduledRun -and -not $DailyRun) {
4953 Write-Verbose "All pre-defined routines (-FirstRun, -DailyRun, -MonthlyRun, -QuarterlyRun, -ScheduledRun) were not specified"
4954 CreateAdamjHeader
4955 if ($RemoveWSUSDriversSQL -eq $True) { Write-Output "Executing RemoveWSUSDrivers using SQL"; RemoveWSUSDrivers -SQL }
4956 if ($RemoveWSUSDriversPS -eq $True) { Write-Output "Executing RemoveWSUSDrivers using Powershell"; RemoveWSUSDrivers }
4957 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." } }
4958 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." } }
4959 if ($RemoveDeclinedWSUSUpdates -eq $True) { Write-Output "Executing RemoveDeclinedWSUSUpdates"; RemoveDeclinedWSUSUpdates -Display -Proceed }
4960 if ($WSUSDBMaintenance -eq $True) { Write-Output "Executing WSUSDBMaintenance"; WSUSDBMaintenance }
4961 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." } }
4962 if ($CleanUpWSUSSynchronizationLogs -eq $True) { Write-Output "Executing CleanUpWSUSSynchronizationLogs"; if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime } }
4963 if ($ComputerObjectCleanup -eq $True -and $AdamjComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup" ; ComputerObjectCleanup }
4964 if ($WSUSServerCleanupWizard -eq $True) { Write-Output "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard }
4965 CreateAdamjFooter
4966 AdamjScriptDifferenceInTime
4967 CreateBodyTXT
4968 CreateBodyHTML
4969 if ($SaveReport -eq "TXT") { SaveReport }
4970 if ($SaveReport -eq "HTML") { SaveReport -ReportType "HTML" }
4971 if ($MailReport -eq "HTML") { MailReport }
4972 if ($MailReport -eq "TXT") { MailReport -MessageContentType "TXT" }
4973}
4974
4975if ($HelpMe -eq $True) {
4976 HelpMe
4977}
4978if ($DisplayApplicationPoolMemory -eq $True) {
4979 ApplicationPoolMemory
4980}
4981if ($IncreaseApplicationPoolMemory) {
4982 ApplicationPoolMemory -IncreaseApplicationPoolBy $IncreaseApplicationPoolMemory
4983}
4984
4985#endregion ProcessTheFunctions
4986<#
4987# All Possible Function Calls
4988
4989CreateAdamjHeader
4990RemoveWSUSDrivers -SQL
4991 RemoveWSUSDriversSQL
4992 RemoveWSUSDriversPS
4993RemoveDeclinedWSUSUpdates -Display -Proceed
4994 RemoveDeclinedWSUSUpdatesProceed
4995 RemoveDeclinedWSUSUpdatesDisplayUpdates
4996 RemoveDeclinedWSUSUpdatesCountUpdates
4997CompressUpdateRevisions
4998RemoveObsoleteUpdates
4999WSUSDBMaintenance -NoOutput
5000DeclineSupersededUpdates -Display -Proceed
5001 DeclineSupersededUpdatesProceed
5002 DeclineSupersededUpdatesDisplayUpdates
5003 DeclineSupersededUpdatesCountUpdates
5004CleanUpWSUSSynchronizationLogs -ConsistencyNumber "14" -ConsistencyTime "Day" -All
5005ComputerObjectCleanup
5006WSUSServerCleanupWizard
5007CreateAdamjFooter
5008CreateBodyTXT
5009CreateBodyHTML
5010SaveReport -ReportType
5011MailReport -MessageContentType
5012HelpMe
5013ApplicationPoolMemory -IncreaseApplicationPoolBy 1024
5014Install-Task
5015#>
5016}
5017
5018End {
5019 if ($HelpMe -eq $True) { $VerbosePreference = $AdamjOldVerbose; Stop-Transcript }
5020 Write-Verbose "End Of Code"
5021}
5022################################
5023# End Of Code #
5024################################
5025#EOF