· 6 years ago · Jan 27, 2020, 08:32 PM
1#Requires -Version 3.0
2################################
3# Clean-WSUS #
4# Version 3.2 #
5# #
6# The last WSUS Script you #
7# will ever need! #
8# #
9################################
10<#
11
12################################
13# Prerequisites #
14################################
15
161. This script has to be saved as plain text in ANSI format. If you use Notepad++, you might need to
17 change the encoding to ANSI (Encoding > 'Encode in ANSI' or Encode > 'Convert to ANSI').
18 An easy way to tell if it is saved in plain text (ANSI) format is that there is a #Requires
19 statement at the top of the script. Make sure that there is a hyphen before the word
20 "Version" and you shouldn't have a problem with executing it. If you end up with an error
21 like below, it is due to the encoding of the file as you can tell by the – characters
22 before the word Version.
23
24 At C:\Scripts\Clean-WSUS.ps1:1 char:13
25 + #Requires –Version 3.0
26
272. You must run this on the WSUS Server itself and any downstream WSUS servers you may have.
28 It does not matter the order on where you run it as the script takes care of everything
29 for you.
30
313. On the WSUS Server, you must install the SQL Server Management Studio (SSMS) from Microsoft
32 so that you have the SQLCMD utility. The SSMS is not a requirement but rather a good tool for
33 troubleshooting if needed. The bare minimum requirement is the Microsoft Command Line
34 Utilities for SQL Server at whatever version yours is.
35
364. You must have PowerShell 3.0 or higher installed. I recommend version 4.0 or higher.
37
38 Prerequisite Downloads
39 ----------------------
40
41 - For Server 2008 SP2:
42 - Install Windows PowerShell from Server Manager - Features
43 - Install .NET 3.5 SP1 from - https://www.microsoft.com/en-ca/download/details.aspx?id=25150
44 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
45 You want to choose SQLManagementStudio_x64_ENU.exe
46 - Install .NET 4.0 - https://www.microsoft.com/en-us/download/details.aspx?id=17718
47 - Install PowerShell 2.0 & WinRM 2.0 from https://www.microsoft.com/en-ca/download/details.aspx?id=20430
48 - Install Windows Management Framework 3.0 from https://www.microsoft.com/en-ca/download/details.aspx?id=34595
49
50 - For Server 2008 R2:
51 - Install .NET 4.5.2 from https://www.microsoft.com/en-ca/download/details.aspx?id=42642
52 - Install Windows Management Framework 4.0 and reboot from https://www.microsoft.com/en-ca/download/details.aspx?id=40855
53 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
54 You want to choose SQLManagementStudio_x64_ENU.exe
55
56 - For SBS 2008: This script WILL work on SBS 2008 - you just have to install the prerequisites below.
57 .NET 4 is backwards compatible and I have a lot of users who have installed it on SBS 2008 and use the script.
58 - Install Windows PowerShell from Server Manager - Features
59 - Install .NET 3.5 SP1 from - https://www.microsoft.com/en-ca/download/details.aspx?id=25150
60 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
61 You want to choose SQLManagementStudio_x64_ENU.exe
62 - Install .NET 4.0 - https://www.microsoft.com/en-us/download/details.aspx?id=17718
63 - Install PowerShell 2.0 & WinRM 2.0 from https://www.microsoft.com/en-ca/download/details.aspx?id=20430
64 - Install Windows Management Framework 3.0 from https://www.microsoft.com/en-ca/download/details.aspx?id=34595
65 - See "A note to SBS users:" Below
66
67 - For SBS 2011: This script WILL work on SBS 2011 - you just have to install the prerequisites below.
68 .NET 4 is backwards compatible and I have a lot of users who have installed it on SBS 2011 and use the script.
69 - Install .NET 4.5.2 from https://www.microsoft.com/en-ca/download/details.aspx?id=42642
70 - Install Windows Management Framework 4.0 and reboot from https://www.microsoft.com/en-ca/download/details.aspx?id=40855
71 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
72 You want to choose SQLManagementStudio_x64_ENU.exe
73 - See "A note to SBS users:" Below
74
75 - For Server 2012 & 2012 R2
76 - Install SQL Server Management Studio from https://www.microsoft.com/en-us/download/details.aspx?id=29062
77 You want to choose the ENU\x64\SQLManagementStudio_x64_ENU.exe
78
79 - For Server 2016
80 - I've not personally tested this on server 2016, however many people have run it without issues on Server 2016.
81 I don't think Microsoft has changed much between 2012 R2 WSUS and 2016 WSUS.
82 - Install SQL Server Management Studio from https://msdn.microsoft.com/library/mt238290.aspx
83
84 IF YOU DON'T WANT TO INSTALL SQL SERVER MANAGEMENT STUDIO:
85 Microsoft Command Line Utilities for SQL Server (Minimum requirement instead of SQL Server Management Studio)
86 SQL 2008/2008R2 - https://www.microsoft.com/en-ca/download/details.aspx?id=16978
87 SQL 2012/2014 - Version 11 - https://www.microsoft.com/en-us/download/details.aspx?id=36433
88 - ODBC Driver Version 11 - https://www.microsoft.com/en-gb/download/details.aspx?id=36434
89 SQL 2016 - Version 13 - https://www.microsoft.com/en-us/download/details.aspx?id=53591
90
91 A note to SBS users:
92 For those of you who have already Googled and have read that there are compatibility issues with PowerShell 3.0
93 or 4.0 and/or Windows Management Framework 3.0 or 4.0 and have seen all of the release notes and posts saying
94 not to install these on SBS, please take notes of the dates of these pages and advice notes. Most of these are
95 relying on and regurgitating old information. If a site has a recent post that says not to install it as there
96 are compatibility issues, find their source of information and if you follow the source, you'll notice that
97 they are regurgitating a post from years ago. When you are reading things on the Internet, think critically,
98 look at dates, and use your intelligence to figure out if it still makes sense. Don't blindly rely on words
99 on pages of the internet.
100
101 An example is .NET 4.7 which was released 2017.06.15 and which has a warning to not install .NET 4.7 on an
102 Exchange server. This holds true until it can be properly tested, and if issues found, patches to .NET 4.7.x
103 released for compatibility with Exchange. The biggest issue - all previous forums, blogs and writings on the
104 Internet will not be updated to say that .NET 4.7 is now compatible to install on Exchange servers. This
105 showcases my point that imagine in 2019 someone who is thinking about updating an Exchange server, Googling
106 to find out if .NET 4.7 is compatible (when current version of .NET is probably around version 5.0 or 5.1)
107 and finding all these warnings about not installing it on an Exchange server.
108
109 One note for any system, but something to mention specifically for this thought:
110 The best thing you can do is make sure your system is updated. Non-updated systems suffer problems and exploits
111 that in the end, cause you more time in troubleshooting and fixing than to keep systems updated.
112
113################################
114# Instructions #
115################################
116
117 1. Edit the variables below to match your environment (It's only email server settings if you
118 use my default settings)
119 2. Open PowerShell using "Run As Administrator" on the WSUS Server.
120 3. Because you downloaded this script from the internet, you cannot initially run it directly
121 as the ExecutionPolicy is default set to "Restricted" (Server 2008, Server 2008 R2, and
122 Server 2012) or "RemoteSigned" (Server 2012 R2). You must change your ExecutionPolicy to
123 Bypass. You can do this with Set-ExecutionPolicy, however that will change it globally for
124 the server, which is not recommended. Instead, launch another PowerShell.exe with the
125 ExecutionPolicy set to bypass for just that session. At your current PowerShell prompt,
126 type in the following and then press enter:
127
128 PowerShell.exe -ExecutionPolicy Bypass
129
130 3. Run the script using -FirstRun.
131
132 .\Clean-WSUS.ps1 -FirstRun
133
134You can use Get-Help .\Clean-WSUS.ps1 for more information.
135#>
136
137<#
138.SYNOPSIS
139This 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.
140
141.DESCRIPTION
142################################
143# Background Information #
144# on Streams #
145################################
146
147All my recommendations are set in -ScheduledRun.
148
149WSUS Index Optimization Stream
150-----------------------------------------------------
151
152This stream will add the necessary SQL Indexes into the SUSDB Database that make WSUS work about
1531,000 to 1,500 times faster on many database operations, making your WSUS installation better
154than what Microsoft has left us with.
155
156This stream will be run first on -FirstRun to ensure the rest of the script doesn't take as long
157as it has in prior times.
158
159You can use -WSUSIndexOptimization to run this manually from the command-line.
160
161Remove WSUS Drivers Stream
162-----------------------------------------------------
163
164This stream will remove all WSUS Drivers Classifications from the WSUS database.
165This has 2 possible running methods - Run through PowerShell, or Run directly in SQL.
166The -FirstRun Switch will force the SQL method, but all other automatic runs will use the
167PowerShell method. I recommend this be done every quarter.
168
169You can use -RemoveWSUSDriversSQL or -RemoveWSUSDriversPS to run these manually from the command-line.
170
171Remove Obsolete Updates Stream
172-----------------------------------------------------
173
174This stream will use SQL code to execute pre-existing stored procedures that will return the update id
175of each obsolete update in the database and then remove it. There is no magic number of obsolete updates
176that will cause the server to time-out. Running this stream can easily take a couple of hours to delete
177the updates. While the process is running you might see WSUS synchronization errors. I recommend that
178this be done monthly.
179
180You can use -RemoveObsoleteUpdates to run this manually from the command-line.
181
182Compress Update Revisions Stream
183-----------------------------------------------------
184
185This stream will use SQL code to execute pre-existing stored procedures that will return the update id
186of each update revision that needs compressing and then compress it. I recommend that this be done
187monthly.
188
189You can use -CompressUpdateRevisions to run this manually from the command-line.
190
191Decline Multiple Types Of Updates Stream
192-----------------------------------------------------
193
194This stream will decline multiple types of updates: Superseded, Expired, and Itanium to name a few.
195This is configurable on a per-type basis for inclusion or exclusion when the stream is run.
196
197I recommend that this stream be run every month.
198
199You can use -DeclineMultipleTypesOfUpdates to run this manually from the command-line.
200
201### A note about the default types of updates to be removed. ###
202
203Expired: Decline updates that have been pulled by Microsoft.
204Itanium: Decline updates for Itanium computers.
205Beta: Decline updates for beta products and beta updates.
206Superseded: Decline updates that are superseded and not yet declined.
207Preview: Decline preview updates as preview updates may contain bugs because they are not the finished product.
208
209### Please read the background information below on superseded updates for more details. ###
210
211This will be the biggest factor in shrinking down the size of your WSUS Server. Any update that
212has been superseded but has not been declined is using extra space. This will save you GB of data
213in your WsusContent folder. A superseded update is a complete replacement of a previous release
214update. The superseding update has everything that the superseded update has, but also includes
215new data that either fixes bugs, or includes something more.
216
217The Server Cleanup Wizard (SCW) declines superseded updates, only if:
218
219 The newest update is approved, and
220 The superseded updates are Not Approved, and
221 The superseded update has not been reported as NotInstalled (i.e. Needed) by any computer in the previous 30 days.
222
223There is no feature in the product to automatically decline superseded updates on approval of the newer update,
224and in fact, you really do not want that feature. The "Best Practice" in dealing with this situation is:
225
2261. Approve the newer update.
2272. Verify that all systems have installed the newer update.
2283. Verify that all systems now report the superseded update as Not Applicable.
2294. THEN it is safe to decline the superseded update.
230
231To SEARCH for superseded updates, you need only enable the Superseded flag column in the All Updates view, and sort on that column.
232
233There will be four groups:
234
2351. Updates which have never been superseded (blank icon).
2362. Updates which have been superseded, but have never superseded another update (icon with blue square at bottom).
2373. Updates which have been superseded and have superseded another update (icon with blue square in middle).
2384. Updates which have superseded another update (icon with blue square at top).
239
240There's no way to filter based on the approval status of the updates in group #4, but if you've verified that all
241necessary/applicable updates in group #4 are approved and installed, then you'd be free to decline groups #2 and #3 en masse.
242
243If you decline superseded updates using the method described:
244
2451. Approve the newer update.
2462. Verify that all systems have installed the newer update.
2473. Verify that all systems now report the superseded update as Not Applicable.
2484. THEN it is safe to decline the superseded update.
249
250### THIS SCRIPT DOES NOT FOLLOW THE ABOVE GUIDELINES. IT WILL JUST DECLINE ANY SUPERSEDED UPDATES. ###
251
252Clean Up WSUS Synchronization Logs Stream
253-----------------------------------------------------
254
255This stream will remove all synchronization logs beyond a specified time period. WSUS is lacking the ability
256to remove synchronization logs through the GUI. Your WSUS server will become slower and slower loading up
257the synchronization logs view as the synchronization logs will just keep piling up over time. If you have
258your synchronization settings set to synchronize 4 times a day, it would take less than 3 months before you
259have over 300 logs that it has to load for the view. This is very time consuming and many just ignore this
260view and rarely go to it. When they accidentally click on it, they curse. I recommend that this be done daily.
261
262You can use -CleanUpWSUSSynchronizationLogs to run this manually from the command-line.
263
264Remove Declined WSUS Updates Stream
265-----------------------------------------------------
266
267This stream will remove any Declined WSUS updates from the WSUS Database. This is good if you are removing
268Specific products (Like Server 2003 / Windows XP updates) from the WSUS server under the Products and
269Classifications section. Since this will remove them from the database, if they are still valid, and you
270want them to re-appear, you will have to re-add them using 1 of 2 methods. Use the 'Import Update' option
271from within the WSUS Console to install specific updates through the Windows Catalog, or remove the product
272family, sync, re-select the product family, and then the next synchronizations will pick up the updates
273again, along with everything else in that product family. I recommend that this be done every quarter.
274This stream is NOT included on -FirstRun on purpose.
275
276You can use -RemoveDeclinedWSUSUpdates to run this manually from the command-line.
277
278Computer Object Cleanup Stream
279-----------------------------------------------------
280
281This stream will find all computers that have not synchronized with the server within a certain time period
282and remove them. This is usually done through the Server Cleanup Wizard (SCW), however the SCW has been
283hard-coded to 30 days. I've setup this stream to be configurable. You can also tell it not to delete any
284computer objects if you really want to. The default I've kept at 30 days. I recommend that this be done daily.
285
286You can use -ComputerObjectCleanup to run this manually from the command-line.
287
288WSUS Database Maintenance Stream
289-----------------------------------------------------
290
291This stream will perform basic maintenance tasks on SUSDB, the WSUS Database. It will identify indexes
292that are fragmented and defragment them. For certain tables, a fill-factor is set in order to improve
293insert performance. It will then update potentially out-of-date table statistics. I recommend that this
294be done daily.
295
296You can use -WSUSDBMaintenance to run this manually from the command-line.
297
298Server Cleanup Wizard Stream
299-----------------------------------------------------
300
301The Server Cleanup Wizard (SCW) is integrated into the WSUS GUI, and can be used to help you manage your
302disk space. This runs the SCW through PowerShell which has the added bonus of not timing out as often
303the SCW GUI would.
304
305This wizard can do the following things:
306 - Remove unused updates and update revisions
307 The wizard will remove all older updates and update revisions that have not been approved.
308
309 - Delete computers not contacting the server
310 The wizard will delete all client computers that have not contacted the server in thirty days or more.
311 This is DISABLED by default as the Computer Object Cleanup Stream takes care of this in a more
312 configurable method.
313
314 - Delete unneeded update files
315 The wizard will delete all update files that are not needed by updates or by downstream servers.
316
317 - Decline expired updates
318 The wizard will decline all updates that have been expired by Microsoft.
319
320 - Decline superseded updates
321 The wizard will decline all updates that meet all the following criteria:
322 The superseded update is not mandatory
323 The superseded update has been on the server for thirty days or more
324 The superseded update is not currently reported as needed by any client
325 The superseded update has not been explicitly deployed to a computer group for ninety days or more
326 The superseding update must be approved for install to a computer group
327
328I recommend that this be done daily. When using -FirstRun, all of the script's streams perform compression and
329removal tasks prior to the SCW being run. Therefore, with the exception of DiskSpaceFreed, all of the other
330fields of the SCW will return 0 when using -FirstRun.
331
332You can use -WSUSServerCleanupWizard to run this manually from the command-line.
333
334Application Pool Memory Configuration Stream
335-----------------------------------------------------
336Why does the WSUS Application pool crash and how can we fix it? The WSUS Application pool has a
337"private memory limit" setting that is configured by default to a low number based on RAM. The
338Application pool crashes because it can't keep up and the limit is reached. So why couldn't the WSUS
339Application pool keep up? This has to do with the larger number of updates in the Update Catalog
340(database) which continues to grow over time. WSUS does not handle an excessive number of updates well
341and as as the number increases, the load on the application pool increases causing it to slowly run out
342of memory until the limit is hit and WSUS crashes. I've seen it start having issues above the low
343number of 10,000 updates and above the high number of 100,000 updates. The number of updates can in
344part be due to obsolete updates that remain in the database and it varies in every system and
345implementation. In order to help alleviate this, we can increase the memory on the WSUS Application Pool.
346
347I recommend that this be done manually, only if necessary, by the command-line.
348
349-DisplayApplicationPoolMemory to display the current application pool memory.
350-SetApplicationPoolMemory <number in MB> to set the private memory limit by the number specified.
351
352Dirty Database Check Stream
353-----------------------------------------------------
354
355From a similar phrase from the movie 'Sleeping With Other People', I coined this stream the
356Dirty Database Check. This stream will run a SQL Query that originally came from Microsoft but has been
357expanded by me to include all future upgrades of Windows 10. This SQL query checks to see if your
358database is 'in a bad state' which is Microsoft's wording but mine sounds a whole lot more fun :)
359
360In addition to checking to see if you have a dirty database, it will fully fix your database
361automatically if it is found to be dirty. This again follows Microsoft's methods, but expanded
362by me to include all future upgrades of Windows 10.
363
364If your upgrades for Windows 10 are not installing properly and have been approved on your WSUS
365server, run this check to see if you have a dirty database and subsequently fix it.
366
367I recommend that this be done manually from the command-line, if you suspect that you may have a
368dirty database.
369
370You can use -DirtyDatabaseCheck to run this manually from the command-line.
371
372.NOTES
373Name: Clean-WSUS
374
375This script has been tested on Server 2008 SP2, Server 2008 R2, Server 2012, and Server 2012 R2. This script should run
376fine on Server 2016 and others have ran it with success on 2016, but I have not had the ability to test it in production.
377
378################################
379# Version History & #
380# Release Notes #
381################################
382
383 Version 3.1 to 3.2
384 - Bug Fix: Dirty Database Fix SQL Script to 1.1 - Added use SUSDB.
385 - Added EULA.
386
387.EXAMPLE
388Clean-WSUS -FirstRun
389Description: Run the routines that are recommended for running this script for the first time.
390
391.EXAMPLE
392Clean-WSUS -InstallTask
393Description: Install the Scheduled task to run this script at 8AM daily with the -ScheduledRun switch.
394
395.EXAMPLE
396Clean-WSUS -HelpMe
397Description: Run the HelpMe stream to create a transcript of the session and provide troubleshooting information in a log file.
398
399.EXAMPLE
400Clean-WSUS -DisplayApplicationPoolMemory
401Description: Display the current Private Memory Limit for the WSUS Application Pool
402
403.EXAMPLE
404Clean-WSUS -SetApplicationPoolMemory 4096
405Description: Set the Private Memory Limit for the WSUS Application Pool to 4096 MB (4GB)
406
407.EXAMPLE
408Clean-WSUS -SetApplicationPoolMemory 0
409Description: Set the Private Memory Limit for the WSUS Application Pool to 0 MB (Unlimited)
410
411.EXAMPLE
412Clean-WSUS -DirtyDatabaseCheck
413Description: Checks to see if the WSUS database is in a bad state.
414
415.EXAMPLE
416Clean-WSUS -DailyRun
417Description: Run the recommended daily routines.
418
419.EXAMPLE
420Clean-WSUS -MonthlyRun
421Description: Run the recommended monthly routines.
422
423.EXAMPLE
424Clean-WSUS -QuarterlyRun
425Description: Run the recommended quarterly routines.
426
427.EXAMPLE
428Clean-WSUS -ScheduledRun
429Description: Run the recommended routines on a schedule having the script take care of all timetables.
430
431.EXAMPLE
432Clean-WSUS -RemoveWSUSDriversSQL -SaveReport TXT
433Description: 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.
434
435.EXAMPLE
436Clean-WSUS -RemoveWSUSDriversPS -MailReport HTML
437Description: Only Remove WSUS Drivers by way of PowerShell and email the output as HTML to the configured parties.
438
439.EXAMPLE
440Clean-WSUS -RemoveDeclinedWSUSUpdates -CleanUpWSUSSynchronizationLogs -WSUSDBMaintenance -WSUSServerCleanupWizard -SaveReport HTML -MailReport TXT
441Description: 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.
442
443.EXAMPLE
444Clean-WSUS -DeclineMultipleTypesOfUpdates -ComputerObjectCleanup -SaveReport TXT -MailReport HTML
445Description: 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.
446
447.EXAMPLE
448Clean-WSUS -RemoveObsoleteUpdates -CompressUpdateRevisions -DeclineMultipleTypesOfUpdates -SaveReport TXT -MailReport HTML
449Description: 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.
450
451.LINK
452#>
453
454################################
455# Script Setup Parameters #
456# #
457# DO NOT EDIT!!! SCROLL DOWN #
458# TO FIND THE VARIABLES #
459# TO EDIT #
460################################
461[CmdletBinding()]
462param (
463 # Run the routines that are recommended for running this script for the first time.
464 [Switch]$FirstRun,
465 # Run the troubleshooting HelpMe stream to copy and paste for getting support.
466 [Switch]$HelpMe,
467 # Run a check on the SUSDB Database to see if you have a bad state (a dirty database).
468 [switch]$DirtyDatabaseCheck,
469 # Display the Application Pool Memory Limit
470 [switch]$DisplayApplicationPoolMemory,
471 # Set the Application Pool Memory Limit.
472 [ValidateRange(0,[int]::MaxValue)]
473 [Int16]$SetApplicationPoolMemory=-1,
474 # Run the recommended daily routines.
475 [Switch]$DailyRun,
476 # Run the recommended monthly routines.
477 [Switch]$MonthlyRun,
478 # Run the recommended quarterly routines.
479 [Switch]$QuarterlyRun,
480 # Run the recommended routines on a schedule having the script take care of all timetables.
481 [Switch]$ScheduledRun,
482 # Remove WSUS Drivers by way of SQL.
483 [Switch]$RemoveWSUSDriversSQL,
484 # Remove WSUS Drivers by way of PowerShell.
485 [Switch]$RemoveWSUSDriversPS,
486 # Compress Update Revisions by way of SQL.
487 [Switch]$CompressUpdateRevisions,
488 # Remove Obsolete Updates by way of SQL.
489 [Switch]$RemoveObsoleteUpdates,
490 # Remove Declined WSUS Updates.
491 [Switch]$RemoveDeclinedWSUSUpdates,
492 # Decline Multiple Types of Updates.
493 [Switch]$DeclineMultipleTypesOfUpdates,
494 # Clean Up WSUS Synchronization Logs based on the configuration variables.
495 [Switch]$CleanUpWSUSSynchronizationLogs,
496 # Clean Up WSUS Synchronization Logs based on the configuration variables.
497 [Switch]$ComputerObjectCleanup,
498 # Run the SQL Maintenance.
499 [Switch]$WSUSDBMaintenance,
500 # Run the Server Cleanup Wizard (SCW) through PowerShell rather than through a GUI.
501 [Switch]$WSUSServerCleanupWizard,
502 # Run the Server Cleanup Wizard (SCW) through PowerShell rather than through a GUI.
503 [Switch]$WSUSIndexOptimization,
504 # Install the Scheduled Task for daily @ 8AM.
505 [Switch]$InstallTask,
506 # 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.
507 [ValidateSet("TXT","HTML")]
508 [String]$SaveReport,
509 # Email the output report to an email address based on the configuration variables. TXT or HTML are valid output types.
510 [ValidateSet("TXT","HTML")]
511 [String]$MailReport
512 )
513Begin {
514$CurrentSystemFunctions = Get-ChildItem function:
515$CurrentSystemVariables = Get-Variable
516if (-not $DailyRun -and -not $FirstRun -and -not $MonthlyRun -and -not $QuarterlyRun -and -not $ScheduledRun -and -not $HelpMe -and -not $InstallTask) {
517 Write-Verbose "Not using a pre-defined routine"
518 if (-not ($DisplayApplicationPoolMemory -or $DirtyDatabaseCheck) -and $SetApplicationPoolMemory -eq '-1') {
519 Write-Verbose "Not using a using the Application Pool commands or the InstallTask or DirtyDatabaseCheck"
520 if ($SaveReport -eq '' -and $MailReport -eq '') {
521 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 -SetApplicationPoolMemory -DirtyDatabaseCheck."
522 } else { Write-Verbose "SaveReport or MailReport have been specified. Continuing on." }
523 } else { Write-Verbose "`$DisplayApplicationPoolMemory -or `$SetApplicationPoolMemory -or `$DirtyDatabaseCheck were specified."; Write-Verbose "`$SetApplicationPoolMemory is set to $SetApplicationPoolMemory" }
524}
525Function Test-RegistryValue {
526 param(
527 [Alias("PSPath")]
528 [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
529 [String]$Path
530 ,
531 [Parameter(Position = 1, Mandatory = $true)]
532 [String]$Name
533 ,
534 [Switch]$PassThru
535 )
536 process {
537 if (Test-Path $Path) {
538 $Key = Get-Item -LiteralPath $Path
539 if ($Key.GetValue($Name, $null) -ne $null) {
540 if ($PassThru) {
541 Get-ItemProperty $Path $Name
542 } else {
543 $true
544 }
545 } else {
546 $false
547 }
548 } else {
549 $false
550 }
551 }
552}
553if ($HelpMe -eq $True) { $OldVerbose = $VerbosePreference; $VerbosePreference = "continue"; Start-Transcript -Path "$(get-date -f "yyyy.MM.dd-HH.mm.ss")-HelpMe.txt" }
554
555#region Configuration Variables
556################################
557# Configuration Variables #
558# Simple Configuration #
559################################
560
561################################
562# Mail Report Setup Variables #
563################################
564
565# 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
566# your Gmail address). Example: 'WSUS@domain.com' or 'email@gmail.com'
567[string]$MailReportEmailFromAddress = 'support@domain.com'
568
569# To: address for email notifications. Example: 'firstname.lastname@domain.com'
570[string]$MailReportEmailToAddress = 'sdas@domain.com'
571
572# Subject: of the results email
573[string]$MailReportEmailSubject = 'WSUS Cleanup Results'
574
575# Enter your SMTP server name. Example: 'mailserver.domain.local' or 'mail.domain.com' or 'smtp.gmail.com'
576# Note Gmail Settings: smtp.gmail.com Port:587 SSL:Enabled User:user@gmail.com Password (if you use 2FA, make an app password).
577[string]$MailReportSMTPServer = 'email.domain.com'
578
579# Enter your SMTP port number. Example: '25' or '465' (Usually for SSL) or '587' or '1025'
580[int32]$MailReportSMTPPort = '25'
581
582# Do you want to enable SSL communication for your SMTP Server
583[boolean]$MailReportSMTPServerEnableSSL = $True
584
585# Do you need to authenticate to the server? If not, leave blank. Note: if your password includes an apostrophe, use 2 apostrophes so that one escapes the other. eg. 'that''s how'
586[string]$MailReportSMTPServerUsername = ''
587[string]$MailReportSMTPServerPassword = ''
588
589################################
590# Configuration Variables #
591# Advanced Configuration #
592################################
593
594################################
595# Mail Report or Save Report #
596################################
597
598# Do you want to enable the Mail Report for every run?
599[boolean]$MailReport = $True
600
601# Do you want the mailed report to be in HTML or plain text? (Valid options are 'HTML' or 'TXT')
602[string]$MailReportType = 'HTML'
603
604# Do you want to enable the save report for every run? (-FirstRun will save the report regardless)
605[boolean]$SaveReport = $False
606
607# Do you want the saved report to be outputted in HTML or plain text? (Valid options are 'HTML' or 'TXT')
608[string]$SaveReportType = 'TXT'
609
610################################
611# Decline Multiple Types #
612# of Updates Variables #
613################################
614
615$DeclineMultipleTypesOfUpdatesList = @{
616'Superseded' = $True #remove superseded updates.
617'Expired' = $True #remove updates that have been pulled by Microsoft.
618'Preview' = $True #remove preview updates.
619'Itanium' = $True #remove updates for Itanium computers.
620'LanguagePacks' = $False #remove language packs.
621'IE7' = $True #remove updates for old versions of IE (IE7).
622'IE8' = $True #remove updates for old versions of IE (IE8).
623'IE9' = $True #remove updates for old versions of IE (IE9).
624'IE10' = $True #remove updates for old versions of IE (IE10).
625'Beta' = $True #Beta products and beta updates.
626'Embedded' = $True #Embedded version of Windows.
627'NonEnglishUpdates' = $True #some non-English updates are not filtered by WSUS language filtering.
628'ComputerUpdates32bit' = $False #remove updates for 32-bit computers.
629'WinXP' = $True #remove Windows XP updates.
630}
631
632################################
633# Computer Object Cleanup #
634# Variables #
635################################
636
637# Do you want to remove the computer objects from WSUS that have not synchronized in days?
638# This is good to keep your WSUS clean of previously removed computers.
639[boolean]$ComputerObjectCleanup = $True
640
641# If the above is set to $True, how many days of no synchronization do you want to remove
642# computer objects from the WSUS Server? Set this to 0 to remove all computer objects.
643[int]$ComputerObjectCleanupSearchDays = '30'
644
645################################
646# WSUS Server Cleanup Wizard #
647# Parameters #
648# Set to $True or $False #
649################################
650
651# 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.
652[boolean]$SCWSupersededUpdatesDeclined = $True
653
654# Decline updates that aren't approved and have been expired my Microsoft.
655[boolean]$SCWExpiredUpdatesDeclined = $True
656
657# Delete updates that are expired and have not been approved for 30 days or more.
658[boolean]$SCWObsoleteUpdatesDeleted = $True
659
660# Delete older update revisions that have not been approved for 30 days or more.
661[boolean]$SCWUpdatesCompressed = $True
662
663# Delete computers that have not contacted the server in 30 days or more. Default: $False
664# This is taken care of by the Computer Object Cleanup Stream
665[boolean]$SCWObsoleteComputersDeleted = $False
666
667# Delete update files that aren't needed by updates or downstream servers.
668[boolean]$SCWUnneededContentFiles = $True
669
670################################
671# Scheduled Run Variables #
672################################
673
674# On what day do you wish to run the MonthlyRun and QuarterlyRun Stream? I recommend on the 1st-7th of the month.
675# This will give enough time for you to approve (if you approve manually) and your computers to receive the
676# superseding updates after patch Tuesday (second Tuesday of the month).
677# (Valid days are 1-31. February, April, June, September, and November have logic to set to the last day
678# of the month if this is set to a number greater than the amount of days in that month, including leap years.)
679[int]$ScheduledRunStreamsDay = '1'
680
681# What months would you like to run the QuarterlyRun Stream?
682# (Valid months are 1-12, comma separated for multiple months)
683[string]$ScheduledRunQuarterlyMonths = '1,4,7,10'
684
685# What time daily do you want to run the script using the scheduled task?
686[string]$ScheduledTaskTime = '8:00am'
687
688################################
689# Clean Up WSUS #
690# Synchronization Logs #
691# Variables #
692################################
693
694# Clean up the synchronization logs older than a consistency.
695
696# (Valid consistency number are whole numbers.)
697[int]$CleanUpWSUSSynchronizationLogsConsistencyNumber = '14'
698
699# Valid consistency time are 'Day' or 'Month'
700[String]$CleanUpWSUSSynchronizationLogsConsistencyTime = 'Day'
701
702# Or remove all synchronization logs each time
703[boolean]$CleanUpWSUSSynchronizationLogsAll = $False
704
705################################
706# Remove WSUS Drivers #
707# Variables #
708################################
709
710# Remove WSUS Drivers on -FirstRun
711[boolean]$RemoveWSUSDriversInFirstRun = $True
712
713# Remove WSUS Drivers on -ScheduledRun or -QuaterlyRun
714[boolean]$RemoveWSUSDriversInRoutines = $True
715
716
717################################
718# SQL Server Variable #
719################################
720
721# The SQL Server Variable is detected automatically whether you are using the Windows Internal Database, a SQL
722# Express instance on the same server or remote server, or a full SQL version on the same server or remote server.
723
724# If you are using a Remote SQL connection, you will need to set the Scheduled Task to use the NETWORK SERVICE
725# account as the user that runs the script. This will run the script with the computer object's security context
726# when accessing resources over the network. As such, the SQL Server will need the computer account added (in
727# the format of: DOMAIN\COMPUTER$) with the appropriate permissions (db_dlladmin or db_owner) for the SUSDB
728# database. This is the recommended way of doing it.
729
730# An alternative way of doing it would be to run the Scheduled Task as a user account that already has the
731# appropriate permissions, saving credentials so that it can pass them through to the SQL Server.
732
733# ONLY uncomment and fill out if you've received explicit instructions from me for support.
734#[string]$SQLServer = 'THIS LINE SHOULD ONLY BE CHANGED WITH EXPLICIT INSTRUCTIONS FROM SUPPORT!'
735
736################################
737# WSUS Setup Variables #
738# This section auto-detects #
739# and shouldn't need #
740# to be modified #
741################################
742
743# FQDN of the WSUS server. Example: 'server.domain.local'
744# WSUS does not play well with Aliases or CNAMEs and requires using the FQDN or the HostName
745[string]$WSUSServer = "$((Get-WmiObject win32_computersystem).DNSHostName)" + $(if ((Get-WmiObject -Class Win32_ComputerSystem).PartOfDomain -eq 'True') { ".$((Get-WmiObject win32_computersystem).Domain)" } )
746
747# Use secure connection: $True or $False
748[boolean]$WSUSServerUseSecureConnection = if ($(Test-RegistryValue "HKLM:\Software\Microsoft\Update Services\Server\Setup" "UsingSSL") -eq $True) { if ((Get-ItemProperty -Path 'HKLM:\Software\Microsoft\Update Services\Server\Setup' -Name 'UsingSSL' | Select-Object -ExpandProperty 'UsingSSL') -eq '1') { $True } else { $False } } else { $False }
749
750# What port number are you using for WSUS? Example: '80' or '443' if on Server 2008 or '8530' or '8531' if on Server 2012+
751[int32]$WSUSServerPortNumber = Get-ItemProperty -Path 'HKLM:\Software\Microsoft\Update Services\Server\Setup' -Name 'PortNumber' | Select-Object -ExpandProperty 'PortNumber'
752
753################################
754# Install the Scheduled Task #
755# This section should be left #
756# alone. #
757################################
758
759<#
760This script is meant to be run daily. It is not just an ad-hock WSUS cleaning tool but rather
761it's a daily maintenance tool. -FirstRun does NOT run all the routines on purpose, and uses certain
762switches that SHOULD NOT be used consistently. If you choose to ignore this and switch the
763$InstallScheduledTask variable to $False, please know that you can encounter problems with
764WSUS in the future that you can't explain. One should not blame Microsoft for messing up WSUS or not
765being able to make a product that works (like so many others have done), but rather blame themselves
766for not running the appropriate WSUS Maintenance routines (declining superseded updates, running the
767WSUS maintenance SQL script, running the server cleanup wizard, etc), to keep WSUS running smoothly.
768
769For those enterprise environments or environments where you want more control over when this script
770runs its streams, I've included the different switches (DailyRun, MonthlyRun, and QuarterlyRun) to be
771used on the appropriate schedules. Do not mistake these options as assuming this script should be run
772only when you feel it is necessary. For these environments, please set the $InstallScheduledTask
773variable to $False and then manually create at least 3 scheduled tasks to run the -DailyRun,
774-MonthlyRun, and -QuarterlyRun switches following the template of -InstallTask's schedule.
775#>
776
777# Install the ScheduledTask to Task Scheduler. (Default: $True)
778[boolean]$Script:InstallScheduledTask = $True
779
780################################
781# Do not edit below this line #
782################################
783}
784#endregion
785
786Process {
787$ScriptTime = Get-Date
788$WSUSServer = $WSUSServer.ToLower()
789Write-verbose "Set the script's current working directory path"
790$ScriptPath = Split-Path $script:MyInvocation.MyCommand.Path
791Write-Verbose "`$ScriptPath = $ScriptPath"
792
793#region Test Elevation
794function Test-Administrator
795{
796 $CurrentUser = [Security.Principal.WindowsIdentity]::GetCurrent();
797 (New-Object Security.Principal.WindowsPrincipal $CurrentUser).IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator)
798}
799Write-Verbose "Testing to see if you are running this from an Elevated PowerShell Prompt."
800if ((Test-Administrator) -ne $True -and ([System.Security.Principal.WindowsIdentity]::GetCurrent().Name -ne 'NT AUTHORITY\SYSTEM')) {
801 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`""
802}
803else {
804 Write-Verbose "Done. You are running this from an Elevated PowerShell Prompt"
805}
806#endregion Test Elevation
807
808#region Test-IfBlocked
809function Test-IfBlocked {
810 if ($(Get-Item $($script:MyInvocation.MyCommand.Path) -Stream "Zone.Identifier" -ErrorAction SilentlyContinue) -eq $null) {
811 Write-Verbose "Zone.Identifier not found. The file is already unblocked"
812 } else {
813 Write-Verbose "Zone.Identifier was found. Unblocking File"
814 Unblock-File -Path $($script:MyInvocation.MyCommand.Path)
815 }
816}
817Test-IfBlocked
818#endregion Test-IfBlocked
819
820if ($HelpMe -eq $True) {
821 $Script:HelpMeHeader = @"
822=============================
823 Clean-WSUS HelpMe Stream
824=============================
825
826This is the HelpMe Section for troubleshooting
827Please provide this information to get support
828
829
830
831"@
832 $Script:ScriptVersion = "3.2"
833 $Script:HelpMeHeader
834 Write-Output 'Starting the connection to the SQL database and WSUS services. Please wait...'
835} else {
836 Write-Output 'Starting the connection to the SQL database and WSUS services. Please wait...'
837}
838
839#region Test SQLConnection
840function Test-SQLConnection
841{
842 param (
843 [parameter(Mandatory = $true)][string] $ServerInstance,
844 [parameter(Mandatory = $false)][int] $TimeOut = 1
845 )
846
847 $SqlConnectionResult = $false
848
849 try
850 {
851 $SqlCatalog = "SUSDB"
852 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
853 $SqlConnection.ConnectionString = "Server = $ServerInstance; Database = $SqlCatalog; Integrated Security = True; Connection Timeout=$TimeOut"
854 $TimeOutVerbage = if ($TimeOut -gt "1") { "seconds" } else { "second" }
855 Write-Verbose "Initiating SQL Connection Testing to `'$ServerInstance'` with a timeout of $TimeOut $TimeOutVerbage"
856 $SqlConnection.Open()
857 Write-Verbose "Connected. Setting `$SqlConnectionResult to $($SqlConnection.State -eq "Open")"
858 $SqlConnectionResult = $SqlConnection.State -eq "Open"
859 }
860
861 catch
862 {
863 Write-Output "Connection Failed."
864 }
865
866 finally
867 {
868 $SqlConnection.Close()
869 }
870
871 return $SqlConnectionResult
872}
873
874if ([string]::isnullorempty($SQLServer)) {
875 Write-Verbose '$SQLServer has not been specified. Starting autodetection for SQL Instance'
876 [string]$WID2008 = 'np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query'
877 [string]$WID2012Plus = 'np:\\.\pipe\MICROSOFT##WID\tsql\query'
878 $SQLServerName = Get-ItemProperty -Path "HKLM:\Software\Microsoft\Update Services\Server\Setup" -Name "SqlServerName" | Select-Object -ExpandProperty "SqlServerName"
879 #$SQLServerName = "$((Get-WmiObject win32_computersystem).DNSHostName)\MICROSOFT##SSEE" #2008 Testing
880 #$SQLServerName = "$((Get-WmiObject win32_computersystem).DNSHostName)\SQLEXPRESS" #SQLEXPRESS instance Testing
881 #$SQLServerName = "$((Get-WmiObject win32_computersystem).DNSHostName)" #SQL Standard default instance testing
882 #$SQLServerName = "$((Get-WmiObject win32_computersystem).DNSHostName)\NamedWSUSInstance" #SQL Other Named Instance testing
883 #$SQLServerName = "REMOTESERVER" #SQL Remote Server testing
884 Write-Verbose "Autodetected `$SQLServerName as $SQLServerName"
885 if ($SQLServerName -eq 'MICROSOFT##WID') {
886 Write-Verbose 'Setting $SQLServer for Server 2012+ Windows Internal Database.'
887 $SQLServer = $WID2012Plus
888 } elseif ($SQLServerName -eq "$((Get-WmiObject win32_computersystem).DNSHostName)\MICROSOFT##SSEE") {
889 Write-Verbose 'Setting $SQLServer for Server 2008 & 2008 R2 Windows Internal Database.'
890 $SQLServer = $WID2008
891 } elseif ($SQLServerName -eq "$((Get-WmiObject win32_computersystem).DNSHostName)\SQLEXPRESS") {
892 Write-Verbose "Setting `$SQLServer for SQLEXPRESS Instance on the local server - `'$SQLServerName'."
893 $SQLServer = $SQLServerName
894 } elseif ($SQLServerName -eq "$((Get-WmiObject win32_computersystem).DNSHostName)") {
895 Write-Verbose "Setting `$SQLServer for SQL Default Instance on the local server - `'$SQLServerName`'."
896 $SQLServer = $SQLServerName
897 } else {
898 Write-Verbose "Setting `$SQLServer to the remote SQL Instance of: `'$SQLServerName`'."
899 $SQLServer = $SQLServerName
900 $SQLServerIsRemote = $True
901 }
902} else {
903 Write-Verbose "You've specified the `$SQLServer variable as `'$SQLServer`'."
904}
905Write-Verbose "Now test that there is a SUSDB database on `'$SQLServer`' and that we can connect to it."
906if ((Test-SQLConnection $SQLServer 60) -eq $true) {
907 Write-Verbose "SQL Server test succeeded. Continuing on."
908} else {
909 if ($HelpMe -ne $True) {
910 #Terminate the script erroring out with a reason.
911 #Throw "I've tested the server `'$SQLServer`' from 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."
912 }
913 else {
914 Write-Output "I can't connect to the SQL server `'$SQLServer`', and you've asked for help. Connecting to the WSUS Server to get troubleshooting information."
915 }
916}
917#Create the connection command variable.
918$SQLConnectCommand = "sqlcmd -S $SQLServer"
919#endregion Test SQLConnection
920
921#region Connect to the WSUS Server
922function Connect-WSUSServer {
923 [CmdletBinding()]
924 param
925 (
926 [Parameter(Position=0, Mandatory = $True)]
927 [Alias("Server")]
928 [string]$WSUSServer,
929
930 [Parameter(Position=1, Mandatory = $True)]
931 [Alias("Port")]
932 [int]$WSUSPort,
933
934 [Parameter(Position=2, Mandatory = $True)]
935 [Alias("SSL")]
936 [boolean]$WSUSEnableSSL
937 )
938 Write-Verbose "Load .NET assembly"
939 [void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration");
940
941 Write-Verbose "Connect to WSUS Server: $WSUSServer"
942 $Script:WSUSAdminProxy = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($WSUSServer,$WSUSEnableSSL,$WSUSPort);
943 If ($? -eq $False) {
944 if ($HelpMe -ne $True) {
945 Throw "ERROR Connecting to the WSUS Server: $WSUSServer. Please check your settings and try again."
946 }
947 else {
948 Write-Output "ERROR Connecting to the WSUS Server: $WSUSServer and you've asked for help. Getting troubleshooting information."
949 }
950 } else {
951 $Script:ConnectedTime = Get-Date
952 $Script:ConnectedTXT = "Connected to the WSUS server $WSUSServer @ $($ConnectedTime.ToString(`"yyyy.MM.dd hh:mm:ss tt zzz`"))`r`n`r`n"
953 $Script:ConnectedHTML = "<i>Connected to the WSUS server $WSUSServer @ $($ConnectedTime.ToString(`"yyyy.MM.dd hh:mm:ss tt zzz`"))</i>`r`n`r`n"
954 Write-Output "Connected to the WSUS server $WSUSServer"
955 }
956}
957Write-Verbose 'Do we really need to connect to the WSUS Server? If we do, connect.'
958if ((($InstallTask -or $DisplayApplicationPoolMemory -or $WSUSIndexOptimization) -eq $False) -and $SetApplicationPoolMemory -eq '-1') {
959 Write-Verbose 'We have a reason to connect. Connecting...'
960 Connect-WSUSServer -Server $WSUSServer -Port $WSUSServerPortNumber -SSL $WSUSServerUseSecureConnection
961 $WSUSServerAdminProxy = $Script:WSUSAdminProxy
962}
963else {
964 Write-Verbose 'We do not have a reason to connect. Continuing on without connecting to the WSUS API'
965 Write-Verbose "`$SetApplicationPoolMemory is set to $SetApplicationPoolMemory"
966}
967#endregion Connect to the WSUS Server
968
969#region Get-DiskFree Function
970################################
971# Get-DiskFree #
972################################
973
974function Get-DiskFree
975# Taken from http://binarynature.blogspot.ca/2010/04/powershell-version-of-df-command.html
976{
977 [CmdletBinding()]
978 param
979 (
980 [Parameter(Position=0,
981 ValueFromPipeline=$true,
982 ValueFromPipelineByPropertyName=$true)]
983 [Alias('hostname')]
984 [Alias('cn')]
985 [string[]]$ComputerName = $env:COMPUTERNAME,
986
987 [Parameter(Position=1,
988 Mandatory=$false)]
989 [Alias('runas')]
990 [System.Management.Automation.Credential()]$Credential =
991 [System.Management.Automation.PSCredential]::Empty,
992
993 [Parameter(Position=2)]
994 [switch]$Format
995 )
996
997 BEGIN
998 {
999 function Format-HumanReadable
1000 {
1001 param ($size)
1002 switch ($size)
1003 {
1004 {$_ -ge 1PB}{"{0:#.#'P'}" -f ($size / 1PB); break}
1005 {$_ -ge 1TB}{"{0:#.#'T'}" -f ($size / 1TB); break}
1006 {$_ -ge 1GB}{"{0:#.#'G'}" -f ($size / 1GB); break}
1007 {$_ -ge 1MB}{"{0:#.#'M'}" -f ($size / 1MB); break}
1008 {$_ -ge 1KB}{"{0:#'K'}" -f ($size / 1KB); break}
1009 default {"{0}" -f ($size) + "B"}
1010 }
1011 }
1012 $wmiq = 'SELECT * FROM Win32_LogicalDisk WHERE Size != Null AND DriveType >= 2'
1013 }
1014
1015 PROCESS
1016 {
1017 foreach ($computer in $ComputerName)
1018 {
1019 try
1020 {
1021 if ($computer -eq $env:COMPUTERNAME)
1022 {
1023 $disks = Get-WmiObject -Query $wmiq `
1024 -ComputerName $computer -ErrorAction Stop
1025 }
1026 else
1027 {
1028 $disks = Get-WmiObject -Query $wmiq `
1029 -ComputerName $computer -Credential $Credential `
1030 -ErrorAction Stop
1031 }
1032
1033 if ($Format)
1034 {
1035 # Create array for $disk objects and then populate
1036 $diskarray = @()
1037 $disks | ForEach-Object { $diskarray += $_ }
1038
1039 $diskarray | Select-Object @{n='Name';e={$_.SystemName}},
1040 @{n='Vol';e={$_.DeviceID}},
1041 @{n='Size';e={Format-HumanReadable $_.Size}},
1042 @{n='Used';e={Format-HumanReadable `
1043 (($_.Size)-($_.FreeSpace))}},
1044 @{n='Avail';e={Format-HumanReadable $_.FreeSpace}},
1045 @{n='Use%';e={[int](((($_.Size)-($_.FreeSpace))`
1046 /($_.Size) * 100))}},
1047 @{n='FS';e={$_.FileSystem}},
1048 @{n='Type';e={$_.Description}}
1049 }
1050 else
1051 {
1052 foreach ($disk in $disks)
1053 {
1054 $diskprops = @{'Volume'=$disk.DeviceID;
1055 'Size'=$disk.Size;
1056 'Used'=($disk.Size - $disk.FreeSpace);
1057 'Available'=$disk.FreeSpace;
1058 'FileSystem'=$disk.FileSystem;
1059 'Type'=$disk.Description
1060 'Computer'=$disk.SystemName;}
1061
1062 # Create custom PS object and apply type
1063 $diskobj = New-Object -TypeName PSObject `
1064 -Property $diskprops
1065 $diskobj.PSObject.TypeNames.Insert(0,'BinaryNature.DiskFree')
1066
1067 Write-Output $diskobj
1068 }
1069 }
1070 }
1071 catch
1072 {
1073 # Check for common DCOM errors and display "friendly" output
1074 switch ($_)
1075 {
1076 { $_.Exception.ErrorCode -eq 0x800706ba } `
1077 { $err = 'Unavailable (Host Offline or Firewall)';
1078 break; }
1079 { $_.CategoryInfo.Reason -eq 'UnauthorizedAccessException' } `
1080 { $err = 'Access denied (Check User Permissions)';
1081 break; }
1082 default { $err = $_.Exception.Message }
1083 }
1084 Write-Warning "$computer - $err"
1085 }
1086 }
1087 }
1088
1089 END {}
1090}
1091#endregion Get-DiskFree Function
1092
1093#region Setup The Header
1094################################
1095# Setup the Header #
1096################################
1097
1098function CreateHeader {
1099$Script:BodyHeaderTXT = @"
1100################################
1101# #
1102# Clean-WSUS #
1103# Version 3.2 #
1104# #
1105# The last WSUS Script you #
1106# will ever need! #
1107# #
1108################################
1109
1110
1111"@
1112$Script:BodyHeaderHTML = @"
1113 <table style="height: 0px; width: 0px;" border="0">
1114 <tbody>
1115 <tr>
1116 <td colspan="3">
1117 <span
1118 style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span>
1119 </td>
1120 </tr>
1121 <tr>
1122 <td style="text-align: left;">#</td>
1123 <td style="text-align: center;"> </td>
1124 <td style="text-align: right;">#</td>
1125 </tr>
1126 <tr>
1127 <td style="text-align: left;">#</td>
1128 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Clean-WSUS</span></td>
1129 <td style="text-align: right;">#</td>
1130 </tr>
1131 <tr>
1132 <td style="text-align: left;">#</td>
1133 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Version 3.2</span></td>
1134 <td style="text-align: right;">#</td>
1135 </tr>
1136 <tr>
1137 <td style="text-align: left;">#</td>
1138 <td> </td>
1139 <td style="text-align: right;">#</td>
1140 </tr>
1141 <tr>
1142 <td style="text-align: left;">#</td>
1143 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">The last WSUS Script you</span></td>
1144 <td style="text-align: right;">#</td>
1145 </tr>
1146 <tr>
1147 <td style="text-align: left;">#</td>
1148 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">will ever need!</span></td>
1149 <td style="text-align: right;">#</td>
1150 </tr>
1151 <tr>
1152 <td style="text-align: left;">#</td>
1153 <td> </td>
1154 <td style="text-align: right;">#</td>
1155 </tr>
1156 <tr>
1157 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
1158 </tr>
1159 </tbody>
1160 </table>
1161"@
1162}
1163#endregion Setup The Header
1164
1165#region Setup The Footer
1166################################
1167# Setup the Footer #
1168################################
1169
1170function CreateFooter {
1171$Script:BodyFooterTXT = @"
1172
1173################################
1174# End of the WSUS Cleanup #
1175################################
1176# #
1177# Latest version available #
1178# from Spiceworks #
1179# #
1180################################
1181
1182"@
1183$Script:BodyFooterHTML = @"
1184 <table style="height: 0px; width: 0px;" border="0">
1185 <tbody>
1186 <tr>
1187 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
1188 </tr>
1189 <tr>
1190 <td style="text-align: left;">#</td>
1191 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">End of the WSUS Cleanup</span></td>
1192 <td style="text-align: right;">#</td>
1193 </tr>
1194 <tr>
1195 <td colspan="3" rowspan="1"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
1196 </tr>
1197 <tr>
1198 <td style="text-align: left;">#</td>
1199 <td style="text-align: center;"> </td>
1200 <td style="text-align: right;">#</td>
1201 </tr>
1202 <tr>
1203 <td style="text-align: left;">#</td>
1204 <td> </td>
1205 <td style="text-align: right;">#</td>
1206 </tr>
1207 <tr>
1208 <td style="text-align: left;">#</td>
1209 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Latest version available</span></td>
1210 <td style="text-align: right;">#</td>
1211 </tr>
1212 <tr>
1213 <td style="text-align: left;">#</td>
1214 <td> </td>
1215 <td style="text-align: right;">#</td>
1216 </tr>
1217 <tr>
1218 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
1219 </tr>
1220 </tbody>
1221 </table>
1222"@
1223}
1224#endregion Setup The Footer
1225
1226#region Show-My Functions
1227################################
1228# Show-My Functions Stream #
1229################################
1230
1231function Show-MyFunctions { Get-ChildItem function: | Where-Object { $CurrentSystemFunctions -notcontains $_ } | Format-Table -AutoSize -Property CommandType,Name }
1232function Show-MyVariables { Get-Variable | Where-Object { $CurrentSystemVariables -notcontains $_ } | Format-Table }
1233#endregion Show-My Functions
1234
1235#region Install-Task Function
1236################################
1237# Install-Task Configuration #
1238################################
1239
1240Function Install-Task {
1241 Write-Verbose "Enter Install-Task Function"
1242 $DateNow = Get-Date
1243 Write-Verbose "`$DateNow is $DateNow"
1244 if ($Script:InstallScheduledTask -eq $True -or $InstallTask -eq $True) {
1245 $PowerShellMajorVersion = $($PSVersionTable.PSVersion.Major)
1246 $Version = @{}
1247 $Version.Add("Major", ((Get-CimInstance Win32_OperatingSystem).Version).Split(".")[0])
1248 $Version.Add("Minor", ((Get-CimInstance Win32_OperatingSystem).Version).Split(".")[1])
1249 #$Version.Add("Major", "5") # Comment above 2 lines and then uncomment for testing
1250 #$Version.Add("Minor", "3") # Uncomment for testing
1251 if ([int]$Version.Get_Item("Major") -ge "7" -or ([int]$Version.Get_Item("Major") -ge "6" -and [int]$Version.Get_Item("Minor") -ge "2")) {
1252 Write-Verbose "YES - OS Version $([int]$Version.Get_Item("Major")).$([int]$Version.Get_Item("Minor"))"
1253 $Windows = [PSCustomObject]@{
1254 Caption = (Get-WmiObject -Class Win32_OperatingSystem).Caption
1255 Version = [Environment]::OSVersion.Version
1256 }
1257 if ($Windows.Version.Major -gt "6") { Write-Verbose "$($Windows.Caption) - Use Win8 Compatibility"; $Compatibility = "Win8" }
1258 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -ge "2" ) { Write-Verbose "$($Windows.Caption) - Use Win8 Compatibility"; $Compatibility = "Win8" }
1259 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -eq "1" ) { Write-Verbose "$($Windows.Caption) - Use Win7 Compatibility"; $Compatibility = "Win7" }
1260 if ($Windows.Version.Major -ge "6" -and $Windows.Version.Minor -eq "0" ) { Write-Verbose "$($Windows.Caption) - Use Vista Compatibility"; $Compatibility = "Vista" }
1261
1262 $Trigger = New-ScheduledTaskTrigger -At $ScheduledTaskTime -Daily #Trigger the task daily at $ScheduledTaskTime
1263 $User = "$env:USERDOMAIN\$env:USERNAME"
1264 if ($SQLServerIsRemote -eq $True) { $Principal = New-ScheduledTaskPrincipal -UserID 'NT AUTHORITY\SYSTEM' -LogonType ServiceAccount -RunLevel Highest } else { $Principal = New-ScheduledTaskPrincipal -UserID "$env:USERDOMAIN\$env:USERNAME" -LogonType S4U -RunLevel Highest }
1265 $TaskName = "Clean-WSUS"
1266 $Description = "This task will run the Clean-WSUS script with the -ScheduledRun parameter which takes care of everything for you according to my recommendations."
1267 if ($Script:MyInvocation.MyCommand.Path.Contains(" ") -eq $True) {
1268 $Action = New-ScheduledTaskAction -Execute "$((Get-Command powershell.exe).Definition)" -Argument "-ExecutionPolicy Bypass -Command `"& `"`"$($script:MyInvocation.MyCommand.Path)`"`"`" -ScheduledRun"
1269 } else {
1270 $Action = New-ScheduledTaskAction -Execute "$((Get-Command powershell.exe).Definition)" -Argument "-ExecutionPolicy Bypass `"$($script:MyInvocation.MyCommand.Path) -ScheduledRun`""
1271 }
1272 $Settings = New-ScheduledTaskSettingsSet -Compatibility $Compatibility
1273 Write-Verbose "Register the Scheduled task."
1274 $Script:InstallTaskOutput = Register-ScheduledTask -TaskName $TaskName -Description $Description -Action $Action -Trigger $Trigger -Settings $Settings -Principal $Principal -Force
1275 if ($SQLServerIsRemote -eq $True) {
1276 Write-Verbose "As the SQL Server is remote, we need to give the computer name account db_owner access into SQL"
1277 $SQLServerIsRemoteALERT = @"
1278!!! SECURITY AWARENESS ALERT !!! Your SQL Server is a REMOTE SQL server. In order to run a scheduled task on a remote SQL Server,
1279the computer object's active directory account [$([Environment]::UserDomainName)\$([Environment]::MachineName)`$] needs to have the db_owner permission on the SUSDB
1280database on $SQLServer. Since WSUS is already installed and running, this account is already setup in the SQL Server and already
1281granted rights inside of the SUSDB database, so all we need to do is add the account to the db_owner role. Unfortunately it
1282must be db_owner and not the db_ddladmin role.
1283"@
1284 $SQLServerIsRemoteScript = @"
1285USE [SUSDB]
1286GO
1287ALTER ROLE [db_owner] ADD MEMBER [$([Environment]::UserDomainName)\$([Environment]::MachineName)`$];
1288PRINT 'Successfully added [$([Environment]::UserDomainName)\$([Environment]::MachineName)`$] to the db_owner role of the SUSDB database on $SQLServer.'
1289"@
1290 Write-Verbose "Create a file with the content of the SQLServerIsRemote Script above in the same working directory as this PowerShell script is running."
1291 $SQLServerIsRemoteScriptFile = "$ScriptPath\SQLServerIsRemoteScript.sql"
1292 $SQLServerIsRemoteScript | Out-File "$SQLServerIsRemoteScriptFile"
1293
1294 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1295 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
1296 Write-Verbose "Execute the SQL Script and store the results in a variable."
1297 $SQLServerIsRemoteScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$SQLServerIsRemoteScriptFile`" -I")
1298 Write-Verbose "`$SQLServerIsRemoteScriptJob = $SQLServerIsRemoteScriptJobCommand"
1299 $SQLServerIsRemoteScriptJob = Start-Job -ScriptBlock $SQLServerIsRemoteScriptJobCommand
1300 Wait-Job $SQLServerIsRemoteScriptJob
1301 $SQLServerIsRemoteScriptJobOutput = Receive-Job $SQLServerIsRemoteScriptJob
1302 Remove-Job $SQLServerIsRemoteScriptJob
1303 Write-Verbose "Remove the SQL Script file."
1304 Remove-Item "$SQLServerIsRemoteScriptFile"
1305 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1306 $Script:SQLServerIsRemoteScriptOutputTXT = $SQLServerIsRemoteALERT -creplace "$","`r`n`r`n"
1307 $Script:SQLServerIsRemoteScriptOutputTXT += $SQLServerIsRemoteScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1308 }
1309 } else {
1310 Write-Verbose "NO - OS Version $([int]$Version.Get_Item("Major")).$([int]$Version.Get_Item("Minor"))"
1311 $ManuallyCreateTaskInstructions = @"
1312You are not using Windows Server 2012 or higher. You will have to manually create the Scheduled Task
1313
1314To Create a Scheduled Task:
1315
13161. Open Task Scheduler and Create a new task (not a basic task)
13172. Go to the General Tab:
13183. Name: "Clean-WSUS"
13194. Under the section "Security Options" put the dot in "Run whether the user is logged on or not"
13205. Check "Do not store password. The task will only have access to local computer resources"
13216. Check "Run with highest privileges."
13227. Under the section "Configure for" - Choose the OS of the Server (e.g. Server 2012 R2)
13238. Go to the Triggers Tab:
13249. Click New at the bottom left.
132510. Under the section "Settings"
132611. Choose Daily. Choose $ScheduledTaskTime
132712. Confirm Enabled is checked, Press OK.
132813. Go to the Actions Tab:
132914. Click New at the bottom left.
133015. Action should be "Start a program"
133116. The "Program/script" should be set to
1332
1333 $((Get-Command powershell.exe).Definition)
1334
133517. The arguments line should be set to
1336
1337
1338 $(if ($Script:MyInvocation.MyCommand.Path.Contains(" ") -eq $True) {
1339 "-ExecutionPolicy Bypass -Command `"& `"`"$($script:MyInvocation.MyCommand.Path)`"`"`" -ScheduledRun"
1340 } else {
1341 "-ExecutionPolicy Bypass `"$($script:MyInvocation.MyCommand.Path) -ScheduledRun`""
1342 })
1343
134418. Go to the Settings Tab:
134519. Check "Allow task to be run on demand"
134620. Click OK
1347"@
1348 $InstallTaskOutput = $ManuallyCreateTaskInstructions
1349 }
1350 } else {
1351 $InstallTaskOutput = @"
1352WARNING!!! WARNING!!! WARNING!!! WARNING!!! WARNING!!! WARNING!!!
1353
1354You've chosen to not install the scheduled task that runs -ScheduledRun daily. THIS SCRIPT
1355IS MEANT TO BE RUN DAILY as it performs daily tasks that should be performed to keep WSUS
1356running in tip-top running condition. Since you've chosen not to install the scheduled task,
1357be sure to schedule manually the -DailyRun, -MonthlyRun, and -QuarterlyRun on an appropriate
1358schedule. Continuously running -FirstRun manually will NOT keep your WSUS maintained
1359properly as there are specific differences with -FirstRun. -FirstRun also does NOT run
1360everything on purpose, and does run streams that should NOT be used consistently.
1361"@
1362 }
1363 $FinishedRunning = Get-Date
1364 Write-Verbose "`$FinishedRunning is $FinishedRunning"
1365 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
1366 $Duration = "{0:00}:{1:00}:{2:00}:{3:00}:{4:00}" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds, $_.Milliseconds})
1367 Write-Verbose "Clean-WSUS Scheduled Task Installation Stream Duration: $Duration"
1368 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1369 $Script:InstallTaskOutputTXT += "Clean-WSUS Scheduled Task Installation:`r`n`r`n"
1370 if ($InstallTaskOutput.GetType().Name -eq "String") {
1371 $Script:InstallTaskOutputTXT += $($InstallTaskOutput.Trim() -creplace '$?',"" -creplace "$","`r`n`r`n")
1372 $Script:InstallTaskOutputTXT += $Script:SQLServerIsRemoteScriptOutputTXT
1373 Write-Output ""; Write-Output $InstallTaskOutput
1374 } else {
1375 $Script:InstallTaskOutputTXT += $($InstallTaskOutput | Select-Object -Property TaskName,State | Format-List | Out-String).Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1376 $Script:InstallTaskOutputTXT += $Script:SQLServerIsRemoteScriptOutputTXT
1377 Write-Output $($InstallTaskOutput | Select-Object -Property TaskName,State | Format-List | Out-String).Trim()
1378 Write-Output $Script:SQLServerIsRemoteScriptOutputTXT
1379 }
1380 #$Script:InstallTaskOutputTXT += "`r`nClean-WSUS Scheduled Task Installation: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1381 $Script:InstallTaskOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Clean-WSUS Scheduled Task Installation:</span></p>`r`n"
1382 if ($InstallTaskOutput.GetType().Name -eq "String") {
1383 #if ($Script:InstallScheduledTask -eq $False) { $InstallTaskOutput = $InstallTaskOutput -creplace '\r\n', " " } (Not sure if I want to use this or not)
1384 $Script:InstallTaskOutputHTML += $InstallTaskOutput -creplace '\r\n', "<br>`r`n" -creplace '^',"<p>" -creplace '$', "</p>`r`n"
1385 } else {
1386 $Script:InstallTaskOutputHTML += $($InstallTaskOutput| Select-Object TaskName,State | ConvertTo-Html -Fragment -PreContent "<div id='gridtable'>`r`n" -PostContent "</div>`r`n") #.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1387 }
1388 #$Script:InstallTaskOutputHTML += $InstallTaskOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1389 #$Script:InstallTaskOutputHTML += "`r`n<p>Clean-WSUS Scheduled Task Installation: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1390
1391 # Variables Output
1392 # $InstallTaskOutputTXT
1393 # $InstallTaskOutputHTML
1394}
1395#endregion Install-Task Function
1396
1397#region DeclineMultipleTypesOfUpdates Function
1398################################
1399# Decline Multiple Types #
1400# of Updates Stream #
1401################################
1402
1403Write-Verbose "Setup the array variables from the user configuration"
1404
1405$Superseded = New-Object System.Object
1406$Superseded | Add-Member -type NoteProperty -name Name -Value "Superseded"
1407$Superseded | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.Superseded)
1408$Superseded | Add-Member -type NoteProperty -name Syntax -Value '$_.IsSuperseded -eq $True'
1409
1410$Expired = New-Object System.Object
1411$Expired | Add-Member -type NoteProperty -name Name -Value "Expired"
1412$Expired | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.Expired)
1413$Expired | Add-Member -type NoteProperty -name Syntax -Value '$_.PublicationState -eq "Expired"'
1414
1415$Preview = New-Object System.Object
1416$Preview | Add-Member -type NoteProperty -name Name -Value "Preview"
1417$Preview | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.Preview)
1418$Preview | Add-Member -type NoteProperty -name Syntax -Value '$_.Title -match "Preview"'
1419
1420$Itanium = New-Object System.Object
1421$Itanium | Add-Member -type NoteProperty -name Name -Value "Itanium"
1422$Itanium | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.Itanium)
1423$Itanium | Add-Member -type NoteProperty -name Syntax -Value '$_.LegacyName -match "ia64|itanium"'
1424
1425$LanguagePacks = New-Object System.Object
1426$LanguagePacks | Add-Member -type NoteProperty -name Name -Value "LanguagePacks"
1427$LanguagePacks | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.LanguagePacks)
1428$LanguagePacks | Add-Member -type NoteProperty -name Syntax -Value '$_.Title -match "language\s"'
1429
1430$IE7 = New-Object System.Object
1431$IE7 | Add-Member -type NoteProperty -name Name -Value "IE7"
1432$IE7 | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.IE7)
1433$IE7 | Add-Member -type NoteProperty -name Syntax -Value '$_.title -match "Internet Explorer 7"'
1434
1435$IE8 = New-Object System.Object
1436$IE8 | Add-Member -type NoteProperty -name Name -Value "IE8"
1437$IE8 | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.IE8)
1438$IE8 | Add-Member -type NoteProperty -name Syntax -Value '$_.title -match "Internet Explorer 8"'
1439
1440$IE9 = New-Object System.Object
1441$IE9 | Add-Member -type NoteProperty -name Name -Value "IE9"
1442$IE9 | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.IE9)
1443$IE9 | Add-Member -type NoteProperty -name Syntax -Value '$_.title -match "Internet Explorer 9"'
1444
1445$IE10 = New-Object System.Object
1446$IE10 | Add-Member -type NoteProperty -name Name -Value "IE10"
1447$IE10 | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.IE10)
1448$IE10 | Add-Member -type NoteProperty -name Syntax -Value '$_.title -match "Internet Explorer 10"'
1449
1450$Beta = New-Object System.Object
1451$Beta | Add-Member -type NoteProperty -name Name -Value "Beta"
1452$Beta | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.Beta)
1453$Beta | Add-Member -type NoteProperty -name Syntax -Value '$_.Title -match "Beta"'
1454
1455$Embedded = New-Object System.Object
1456$Embedded | Add-Member -type NoteProperty -name Name -Value "Embedded"
1457$Embedded | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.Embedded)
1458$Embedded | Add-Member -type NoteProperty -name Syntax -Value '$_.title -match "Windows Embedded"'
1459
1460$NonEnglishUpdates = New-Object System.Object
1461$NonEnglishUpdates | Add-Member -type NoteProperty -name Name -Value "NonEnglishUpdates"
1462$NonEnglishUpdates | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.NonEnglishUpdates)
1463$NonEnglishUpdates | Add-Member -type NoteProperty -name Syntax -Value '$_.title -match "Japanese" -or $_.title -match "Korean" -or $_.title -match "Taiwan"'
1464
1465$ComputerUpdates32bit = New-Object System.Object
1466$ComputerUpdates32bit | Add-Member -type NoteProperty -name Name -Value "ComputerUpdates32bit"
1467$ComputerUpdates32bit | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.ComputerUpdates32bit)
1468$ComputerUpdates32bit | Add-Member -type NoteProperty -name Syntax -Value '$_.LegacyName -match "x86"'
1469
1470$WinXP = New-Object System.Object
1471$WinXP | Add-Member -type NoteProperty -name Name -Value "WinXP"
1472$WinXP | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.WinXP)
1473$WinXP | Add-Member -type NoteProperty -name Syntax -Value '$_.LegacyName -match "XP" -or $_.producttitles -match "XP"'
1474
1475$SharepointUpdates = New-Object System.Object
1476$SharepointUpdates | Add-Member -type NoteProperty -name Name -Value "SharepointUpdates"
1477$SharepointUpdates | Add-Member -type NoteProperty -name Decline -Value $($DeclineMultipleTypesOfUpdatesList.SharepointUpdates)
1478$SharepointUpdates | Add-Member -type NoteProperty -name Syntax -Value '$_.IsApproved -and $_.Title -match "SharePoint"'
1479
1480Write-Verbose "Create the array from all of the objects"
1481$TypesList = @()
1482$TypesList += $Superseded,$Expired, $Preview, $Itanium, $LanguagePacks, $IE7, $IE8, $IE9, $IE10, $Beta, $Embedded, $NonEnglishUpdates, $ComputerUpdates32bit, $WinXP
1483
1484function DeclineMultipleTypesOfUpdates {
1485 param (
1486 [Switch]$Force
1487 )
1488 # Log the date first
1489 $DateNow = Get-Date
1490 Write-Output "Decline Multiple Types of Updates Stream"
1491 Write-Output ""
1492 Write-Verbose "Create an update scope"
1493 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
1494 #$UpdateScope.ApprovedStates = "Any"
1495 Write-Verbose "Let's grab all the updates on the server and stick them into a variable so we don't have to keep querying the database."
1496 $AllUpdatesList = $WSUSServerAdminProxy.GetUpdates($UpdateScope)
1497 $ScheduledRunStreamsDayEnglish = $(
1498 if ($ScheduledRunStreamsDay -eq $DateNow.Day -or $FirstRun -eq $True) { "today" }
1499 else {
1500 if ($ScheduledRunStreamsDay -eq '1') {
1501 "on the $ScheduledRunStreamsDay" + "st"
1502 } elseif ($ScheduledRunStreamsDay -eq '2') {
1503 "on the $ScheduledRunStreamsDay" + "nd"
1504 } elseif ($ScheduledRunStreamsDay -eq '3') {
1505 "on the $ScheduledRunStreamsDay" + "rd"
1506 } else {
1507 "on the $ScheduledRunStreamsDay" + "th"
1508 }
1509 }
1510 )
1511 Write-Output "There are $($AllUpdatesList.Count) updates in this server's database."
1512 $DeclineMultipleTypesOfUpdatesOutputTXT = "There are $($AllUpdatesList.Count) updates in this server's database.`r`n"
1513 $DeclineMultipleTypesOfUpdatesOutputHTML += "<p>There are $($AllUpdatesList.Count) updates in this server's database.<br />`r`n"
1514 Write-Output "There are $($TypesList.Count) types of updates that we're going to deal with $($ScheduledRunStreamsDayEnglish):"
1515 $DeclineMultipleTypesOfUpdatesOutputTXT = "There are $($TypesList.Count) types of updates that we're going to deal with $($ScheduledRunStreamsDayEnglish):`r`n`r`n"
1516 $DeclineMultipleTypesOfUpdatesOutputHTML += "There are $($TypesList.Count) types of updates that we're going to deal with $($ScheduledRunStreamsDayEnglish):</p>`r`n`r`n"
1517 $DeclineMultipleTypesOfUpdatesOutputHTML += "<ol>`r`n"
1518 Write-Output ""
1519 $TypesList | ForEach-Object -Begin { $I=0 } -Process {
1520 $I = $I+1
1521 Write-Progress -Id 1 -Activity "Running through Decline Multiple Types Of Updates Stream" -Status "Currently Counting" -CurrentOperation "$($_.Name) updates" -PercentComplete ($I/$TypesList.count*100) -ParentId -1
1522 $TypesList_ = $_
1523 if ($_.Decline -eq $True) {
1524 Write-Verbose "On this iteration We are going to deal with: $($_.Name)."
1525 Write-Verbose "Let's query the `$AllUpdatesList which has the scope of `"$($UpdateScope.ApprovedStates)`" and store the results into a variable that we are going to work with."
1526 $TargetListConditions = "`$_.IsDeclined -eq `$False -and $($_.Syntax)"
1527 $TargetList = $AllUpdatesList | Where-Object { Invoke-Expression $TargetListConditions }
1528 if ($Force -eq $True -or $ScheduledRunStreamsDay -eq $DateNow.Day) {
1529 Write-Output "$($I). $($_.Name): Displaying the titles of the $($_.Name) updates that have been declined:"
1530 $DeclineMultipleTypesOfUpdatesOutputTXT += "$($I). $($_.Name): Displaying the titles of the $($_.Name) updates that have been declined:`r`n"
1531 $DeclineMultipleTypesOfUpdatesOutputHTML += "`t<li>$($_.Name): Displaying the titles of the $($_.Name) updates that have been declined:</li>`r`n"
1532 if ($TargetList.Count -ne 0) {
1533 $DeclineMultipleTypesOfUpdatesOutputHTML += "`t<ol>`r`n"
1534 $Count=0
1535 $TargetList | ForEach-Object -Begin { $J=0 } -Process {
1536 $J = $J+1
1537 Write-Progress -Id 2 -Activity "Declining $($TypesList_.Name) updates" -Status "Progress" -PercentComplete ($J/$TargetList.Count*100) -ParentId 1
1538 $Count++
1539 Write-Output "`t$($Count). $($_.Title) - https://support.microsoft.com/en-us/kb/$($_.KnowledgebaseArticles)"
1540 $DeclineMultipleTypesOfUpdatesOutputTXT += "`t$($Count). $($_.Title) - https://support.microsoft.com/en-us/kb/$($_.KnowledgebaseArticles)`r`n"
1541 $DeclineMultipleTypesOfUpdatesOutputHTML += "`t`t<li><a href=`"https://support.microsoft.com/en-us/kb$($_.KnowledgebaseArticles)`">$($_.Title)</a></li>`r`n"
1542 $_.Decline()
1543 }
1544 Write-Progress -Id 2 -Activity "Declining $($TypesList_.Name) updates" -Completed
1545 } else {
1546 Write-Output "`t$($_.Name) has no updates to decline."
1547 $DeclineMultipleTypesOfUpdatesOutputTXT += "`t$($_.Name) has no updates to decline.`r`n"
1548 $DeclineMultipleTypesOfUpdatesOutputHTML += "`t<ol>`r`n`t`t<li>$($_.Name) has no updates to decline.</li>`r`n"
1549 }
1550 $DeclineMultipleTypesOfUpdatesOutputHTML += "`t</ol>`r`n"
1551 Write-Progress -Id 2 -Activity "Declining $($TypesList_.Name) updates" -Completed
1552 } else {
1553 Write-Verbose "It is NOT THE streams day - Just Count it."
1554 Write-Output "$($I). $($_.Name): $($TargetList.Count)"
1555 $DeclineMultipleTypesOfUpdatesOutputTXT += "$($I). $($_.Name): $($TargetList.Count)`r`n"
1556 $DeclineMultipleTypesOfUpdatesOutputHTML += "`t<li>$($_.Name): $($TargetList.Count)</li>`r`n"
1557 #Write-Output "There are currently updates to decline for."
1558 }
1559 } else {
1560 Write-Output "$($I). $($_.Name): Skipped"
1561 $DeclineMultipleTypesOfUpdatesOutputTXT += "$($I). $($_.Name): Skipped`r`n"
1562 $DeclineMultipleTypesOfUpdatesOutputHTML += "`t<li>$($_.Name): Skipped</li>`r`n"
1563 }
1564 Write-Progress -Id 1 -Activity "Running through Decline Multiple Types Of Updates Stream" -Completed -ParentId -1
1565 }
1566 $DeclineMultipleTypesOfUpdatesOutputHTML += "</ol>`r`n`r`n"
1567 $FinishedRunning = Get-Date
1568 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
1569 Write-Output ""
1570 $Output = "Decline Multiple Types of Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1571 $Output
1572 $Script:DeclineMultipleTypesOfUpdatesOutputTXT += "Decline Multiple Types of Updates Stream:`r`n`r`n"
1573 $Script:DeclineMultipleTypesOfUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Decline Multiple Types of Updates Stream:</span></p>`r`n`r`n"
1574 $Script:DeclineMultipleTypesOfUpdatesOutputTXT += "$DeclineMultipleTypesOfUpdatesOutputTXT`r`n"
1575 $Script:DeclineMultipleTypesOfUpdatesOutputHTML += $DeclineMultipleTypesOfUpdatesOutputHTML
1576 $Script:DeclineMultipleTypesOfUpdatesOutputTXT += "Decline Multiple Types of Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1577 $Script:DeclineMultipleTypesOfUpdatesOutputHTML += "<p>Decline Multiple Types of Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1578}
1579#endregion DeclineMultipleTypesOfUpdates Function
1580
1581#region ApplicationPoolMemory Function
1582################################
1583# Application Pool Memory #
1584# Configuration Stream #
1585################################
1586function ApplicationPoolMemory {
1587 Param(
1588 [ValidateRange(0,[int]::MaxValue)]
1589 [Int]$Set=-1
1590 )
1591 Write-Verbose "`$Set is set to $Set"
1592 $DateNow = Get-Date
1593 Import-Module WebAdministration
1594 $applicationPoolsPath = "/system.applicationHost/applicationPools"
1595 $applicationPools = Get-WebConfiguration $applicationPoolsPath
1596 foreach ($appPool in $applicationPools.Collection) {
1597 if ($appPool.name -eq 'WsusPool') {
1598 $appPoolPath = "$applicationPoolsPath/add[@name='$($appPool.Name)']"
1599 $CurrentPrivateMemory = (Get-WebConfiguration "$appPoolPath/recycling/periodicRestart/@privateMemory").Value
1600 Write-Output "Current Private Memory Limit for $($appPool.name) is: $($CurrentPrivateMemory/1000) MB"
1601 if ($set -ne '-1') {
1602 Write-Verbose "Setting the private memory limit to $Set MB"
1603 $Set=$Set * 1000
1604 Write-Verbose "Setting the primary memory limit to $Set Bytes"
1605 $NewPrivateMemory = $Set
1606 Write-Output "New Private Memory Limit for $($appPool.name) is: $($NewPrivateMemory/1000) MB"
1607 Set-WebConfiguration "$appPoolPath/recycling/periodicRestart/@privateMemory" -Value $NewPrivateMemory
1608 Write-Verbose "Restart the $($appPool.name) Application Pool to make the new settings take effect"
1609 Restart-WebAppPool -Name $($appPool.name)
1610 }
1611 }
1612 }
1613 $FinishedRunning = Get-Date
1614 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
1615 $Duration = "{0:00}:{1:00}:{2:00}:{3:00}:{4:00}" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds, $_.Milliseconds})
1616 Write-Verbose "Application Pool Memory Stream Duration: $Duration"
1617}
1618#endregion ApplicationPoolMemory Function
1619
1620#region RemoveWSUSDrivers Function
1621################################
1622# Remove WSUS Drivers #
1623# Stream #
1624################################
1625
1626function RemoveWSUSDrivers {
1627 param (
1628 [Parameter()]
1629 [Switch] $SQL
1630 )
1631 function RemoveWSUSDriversSQL {
1632 $RemoveWSUSDriversSQLScript = @"
1633/*
1634################################
1635# WSUS Delete Drivers #
1636# SQL Script #
1637# Version 1.0 #
1638# Taken from various sources #
1639# from the Internet. #
1640# #
1641################################
1642
1643-- Originally taken from http://www.flexecom.com/how-to-delete-driver-updates-from-wsus-3-0/
1644-- Modified to be dynamic and more of a nice output
1645*/
1646USE SUSDB;
1647GO
1648
1649SET NOCOUNT ON;
1650DECLARE @tbrevisionlanguage nvarchar(255)
1651DECLARE @tbProperty nvarchar(255)
1652DECLARE @tbLocalizedPropertyForRevision nvarchar(255)
1653DECLARE @tbFileForRevision nvarchar(255)
1654DECLARE @tbInstalledUpdateSufficientForPrerequisite nvarchar(255)
1655DECLARE @tbPreRequisite nvarchar(255)
1656DECLARE @tbDeployment nvarchar(255)
1657DECLARE @tbXml nvarchar(255)
1658DECLARE @tbPreComputedLocalizedProperty nvarchar(255)
1659DECLARE @tbDriver nvarchar(255)
1660DECLARE @tbFlattenedRevisionInCategory nvarchar(255)
1661DECLARE @tbRevisionInCategory nvarchar(255)
1662DECLARE @tbMoreInfoURLForRevision nvarchar(255)
1663DECLARE @tbRevision nvarchar(255)
1664DECLARE @tbUpdateSummaryForAllComputers nvarchar(255)
1665DECLARE @tbUpdate nvarchar(255)
1666DECLARE @var1 nvarchar(255)
1667
1668/*
1669This query gives you the GUID that you will need to substitute in all subsequent queries. In my case, it is
1670D2CB599A-FA9F-4AE9-B346-94AD54EE0629. I saw this GUID in several WSUS databases so I think it does not change;
1671at least not between WSUS 3.0 SP2 servers. Either way, we are setting a variable for this so this will
1672dynamically reference the correct GUID.
1673*/
1674
1675SELECT @var1 = UpdateTypeID FROM tbUpdateType WHERE Name = 'Driver'
1676
1677/*
1678The bad news is that WSUS database has over 100 tables. The good news is that SQL allows to enforce referential
1679integrity in data model designs, which in this case can be used to essentially reverse engineer a procedure,
1680that as far as I know isn't documented anywhere.
1681
1682The trick is to delete all driver type records from tbUpdate table - but FIRST we have to delete all records in
1683all other tables (revisions, languages, dependencies, files, reports...), which refer to driver rows in tbUpdate.
1684
1685Here's how this is done, in 16 tables/queries.
1686*/
1687
1688delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1689SELECT @tbrevisionlanguage = @@ROWCOUNT
1690PRINT 'Delete records from tbrevisionlanguage: ' + @tbrevisionlanguage
1691
1692delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1693SELECT @tbProperty = @@ROWCOUNT
1694PRINT 'Delete records from tbProperty: ' + @tbProperty
1695
1696delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1697SELECT @tbLocalizedPropertyForRevision = @@ROWCOUNT
1698PRINT 'Delete records from tbLocalizedPropertyForRevision: ' + @tbLocalizedPropertyForRevision
1699
1700delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1701SELECT @tbFileForRevision = @@ROWCOUNT
1702PRINT 'Delete records from tbFileForRevision: ' + @tbFileForRevision
1703
1704delete 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)))
1705SELECT @tbInstalledUpdateSufficientForPrerequisite = @@ROWCOUNT
1706PRINT 'Delete records from tbInstalledUpdateSufficientForPrerequisite: ' + @tbInstalledUpdateSufficientForPrerequisite
1707
1708delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1709SELECT @tbPreRequisite = @@ROWCOUNT
1710PRINT 'Delete records from tbPreRequisite: ' + @tbPreRequisite
1711
1712delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1713SELECT @tbDeployment = @@ROWCOUNT
1714PRINT 'Delete records from tbDeployment: ' + @tbDeployment
1715
1716delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1717SELECT @tbXml = @@ROWCOUNT
1718PRINT 'Delete records from tbXml: ' + @tbXml
1719
1720delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1721SELECT @tbPreComputedLocalizedProperty = @@ROWCOUNT
1722PRINT 'Delete records from tbPreComputedLocalizedProperty: ' + @tbPreComputedLocalizedProperty
1723
1724delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1725SELECT @tbDriver = @@ROWCOUNT
1726PRINT 'Delete records from tbDriver: ' + @tbDriver
1727
1728delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1729SELECT @tbFlattenedRevisionInCategory = @@ROWCOUNT
1730PRINT 'Delete records from tbFlattenedRevisionInCategory: ' + @tbFlattenedRevisionInCategory
1731
1732delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1733SELECT @tbRevisionInCategory = @@ROWCOUNT
1734PRINT 'Delete records from tbRevisionInCategory: ' + @tbRevisionInCategory
1735
1736delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1737SELECT @tbMoreInfoURLForRevision = @@ROWCOUNT
1738PRINT 'Delete records from tbMoreInfoURLForRevision: ' + @tbMoreInfoURLForRevision
1739
1740delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)
1741SELECT @tbRevision = @@ROWCOUNT
1742PRINT 'Delete records from tbRevision: ' + @tbRevision
1743
1744delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)
1745SELECT @tbUpdateSummaryForAllComputers = @@ROWCOUNT
1746PRINT 'Delete records from tbUpdateSummaryForAllComputers: ' + @tbUpdateSummaryForAllComputers
1747
1748PRINT CHAR(13)+CHAR(10) + 'This is the last query and this is really what we came here for.'
1749
1750delete from tbUpdate where UpdateTypeID = @var1
1751SELECT @tbUpdate = @@ROWCOUNT
1752PRINT 'Delete records from tbUpdate: ' + @tbUpdate
1753
1754/*
1755If at this point you get an error saying something about foreign key constraint, that will be most likely
1756due to the difference between which reports I ran in my WSUS installation and which reports were ran against
1757your particular installation. Fortunately, the error gives you exact location (table) where this constraint
1758is violated, so you can adjust one of the queries in the batch above to delete references in any other tables.
1759*/
1760"@
1761 Write-Verbose "Create a file with the content of the RemoveWSUSDrivers Script above in the same working directory as this PowerShell script is running."
1762 $RemoveWSUSDriversSQLScriptFile = "$ScriptPath\RemoveWSUSDrivers.sql"
1763 $RemoveWSUSDriversSQLScript | Out-File "$RemoveWSUSDriversSQLScriptFile"
1764 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1765 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
1766 Write-Verbose "Execute the SQL Script and store the results in a variable."
1767 $RemoveWSUSDriversSQLScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$RemoveWSUSDriversSQLScriptFile`" -I")
1768 Write-Verbose "`$RemoveWSUSDriversSQLScriptJobCommand = $RemoveWSUSDriversSQLScriptJobCommand"
1769 $RemoveWSUSDriversSQLScriptJob = Start-Job -ScriptBlock $RemoveWSUSDriversSQLScriptJobCommand
1770 Wait-Job $RemoveWSUSDriversSQLScriptJob
1771 $RemoveWSUSDriversSQLScriptJobOutput = Receive-Job $RemoveWSUSDriversSQLScriptJob
1772 Remove-Job $RemoveWSUSDriversSQLScriptJob
1773 Write-Verbose "Remove the SQL Script file."
1774 Remove-Item "$RemoveWSUSDriversSQLScriptFile"
1775 $Script:RemoveWSUSDriversSQLOutputTXT = $RemoveWSUSDriversSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n`r`n"
1776 $Script:RemoveWSUSDriversSQLOutputHTML = $RemoveWSUSDriversSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1777
1778 # Variables Output
1779 # $RemoveWSUSDriversSQLOutputTXT
1780 # $RemoveWSUSDriversSQLOutputHTML
1781
1782 }
1783 function RemoveWSUSDriversPS {
1784 $Count = 0
1785 $WSUSServerAdminProxy.GetUpdates() | Where-Object { $_.IsDeclined -eq $true -and $_.UpdateClassificationTitle -eq "Drivers" } | ForEach-Object {
1786 # Delete these updates
1787 $WSUSServerAdminProxy.DeleteUpdate($_.Id.UpdateId.ToString())
1788 $DeleteDeclinedDriverTitle = $_.Title
1789 $Count++
1790 $RemoveWSUSDriversPSDeleteOutputTXT += "$($Count). $($DeleteDeclinedDriverTitle)`n`n"
1791 $RemoveWSUSDriversPSDeleteOutputHTML += "<li>$DeleteDeclinedDriverTitle</li>`n"
1792 }
1793 $RemoveWSUSDriversPSDeleteOutputTXT += "`n`n"
1794 $RemoveWSUSDriversPSDeleteOutputHTML += "</ol>`n"
1795
1796 $Script:RemoveWSUSDriversPSOutputTXT += "`n`n"
1797 $Script:RemoveWSUSDriversPSOutputHTML += "<ol>`n"
1798 $Script:RemoveWSUSDriversPSOutputTXT += $RemoveWSUSDriversPSDeleteOutputTXT
1799 $Script:RemoveWSUSDriversPSOutputHTML += $RemoveWSUSDriversPSDeleteOutputHTML
1800
1801 # Variables Output
1802 # $RemoveWSUSDriversPSOutputTXT
1803 # $RemoveWSUSDriversPSOutputHTML
1804 }
1805 # Process the appropriate internal function
1806 $DateNow = Get-Date
1807 if ($SQL -eq $True) { RemoveWSUSDriversSQL } else { RemoveWSUSDriversPS }
1808 $FinishedRunning = Get-Date
1809 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
1810 # Create the output for the RemoveWSUSDrivers function
1811 $Script:RemoveWSUSDriversOutputTXT += "Remove WSUS Drivers:`n`n"
1812 $Script:RemoveWSUSDriversOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Remove WSUS Drivers:</span></p>`n"
1813 if ($SQL -eq $True) {
1814 $Script:RemoveWSUSDriversOutputTXT += $RemoveWSUSDriversSQLOutputTXT
1815 $Script:RemoveWSUSDriversOutputHTML += $RemoveWSUSDriversSQLOutputHTML
1816 } else {
1817 $Script:RemoveWSUSDriversOutputTXT += $RemoveWSUSDriversPSOutputTXT
1818 $Script:RemoveWSUSDriversOutputHTML += $RemoveWSUSDriversPSOutputHTML
1819 }
1820 $Script:RemoveWSUSDriversOutputTXT += "Remove WSUS Drivers Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1821 $Script:RemoveWSUSDriversOutputHTML += "<p>Remove WSUS Drivers Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1822
1823 # Variables Output
1824 # $RemoveWSUSDriversOutputTXT
1825 # $RemoveWSUSDriversOutputHTML
1826}
1827#endregion RemoveWSUSDrivers Function
1828
1829#region WSUSIndexOptimization Function
1830################################
1831# WSUS Index #
1832# Optimization Stream #
1833################################
1834
1835function WSUSIndexOptimization {
1836 Param (
1837 )
1838 $DateNow = Get-Date
1839 $WSUSIndexOptimizationSQLScript = @"
1840USE [SUSDB]
1841GO
1842/****** Object: Index [_IX_TargetGroupTypeID_LastChangeNumber_UpdateType] Script Date: 2017-06-05 17:22:17 ******/
1843IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = '_IX_TargetGroupTypeID_LastChangeNumber_UpdateType' AND object_id = OBJECT_ID('[dbo].[tbDeadDeployment]'))
1844 BEGIN
1845 PRINT '_IX_TargetGroupTypeID_LastChangeNumber_UpdateType on [dbo].[tbDeadDeployment] doesn''t exist. Creating...'
1846 CREATE NONCLUSTERED INDEX [_IX_TargetGroupTypeID_LastChangeNumber_UpdateType] ON [dbo].[tbDeadDeployment]
1847 (
1848 [TargetGroupTypeID] ASC,
1849 [LastChangeNumber] ASC,
1850 [UpdateType] ASC
1851 )
1852 INCLUDE ( [TargetGroupID],
1853 [UpdateID],
1854 [RevisionNumber]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1855 PRINT 'Done.'
1856 END
1857ELSE
1858 BEGIN
1859 PRINT '_IX_TargetGroupTypeID_LastChangeNumber_UpdateType on [dbo].[tbDeadDeployment] already created. No changes made.'
1860 END
1861/****** Object: Index [_IX_RevisionID_ActionID_DeploymentStatus___UpdateType] Script Date: 2017-06-05 17:22:40 ******/
1862IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = '_IX_RevisionID_ActionID_DeploymentStatus___UpdateType' AND object_id = OBJECT_ID('[dbo].[tbDeployment]'))
1863 BEGIN
1864 PRINT '_IX_RevisionID_ActionID_DeploymentStatus___UpdateType on [dbo].[tbDeployment] doesn''t exist. Creating...'
1865 CREATE NONCLUSTERED INDEX [_IX_RevisionID_ActionID_DeploymentStatus___UpdateType] ON [dbo].[tbDeployment]
1866 (
1867 [RevisionID] ASC,
1868 [ActionID] ASC,
1869 [DeploymentStatus] ASC
1870 )
1871 INCLUDE ( [UpdateType]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
1872 PRINT 'Done.'
1873 END
1874ELSE
1875 BEGIN
1876 PRINT '_IX_RevisionID_ActionID_DeploymentStatus___UpdateType on [dbo].[tbDeployment] already created. No changes made.'
1877 END
1878/****** Object: Index [_IX_ActualState] Script Date: 2017-06-05 17:27:34 ******/
1879IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = '_IX_ActualState' AND object_id = OBJECT_ID('[dbo].[tbFileOnServer]'))
1880 BEGIN
1881 PRINT '_IX_ActualState on [dbo].[tbFileOnServer] doesn''t exist. Creating...'
1882 CREATE NONCLUSTERED INDEX [_IX_ActualState] ON [dbo].[tbFileOnServer]
1883 (
1884 [ActualState] ASC
1885 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
1886 PRINT 'Done.'
1887 END
1888ELSE
1889 BEGIN
1890 PRINT '_IX_ActualState on [dbo].[tbFileOnServer] already created. No changes made.'
1891 END
1892/****** Object: Index [_IX_LocalizedPropertyID] Script Date: 2017-06-05 17:28:14 ******/
1893IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = '_IX_LocalizedPropertyID' AND object_id = OBJECT_ID('[dbo].[tbLocalizedProperty]'))
1894 BEGIN
1895 PRINT '_IX_LocalizedPropertyID on [dbo].[tbLocalizedProperty] doesn''t exist. Creating...'
1896 CREATE NONCLUSTERED INDEX [_IX_LocalizedPropertyID] ON [dbo].[tbLocalizedProperty]
1897 (
1898 [LocalizedPropertyID] ASC
1899 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1900 PRINT 'Done.'
1901 END
1902ELSE
1903 BEGIN
1904 PRINT '_IX_LocalizedPropertyID on [dbo].[tbLocalizedProperty] already created. No changes made.'
1905 END
1906/****** Object: Index [_IX_LocalizedPropertyID] Script Date: 2017-06-05 17:28:38 ******/
1907IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = '_IX_LocalizedPropertyID' AND object_id = OBJECT_ID('[dbo].[tbLocalizedPropertyForRevision]'))
1908 BEGIN
1909 PRINT '_IX_LocalizedPropertyID on [dbo].[tbLocalizedPropertyForRevision] doesn''t exist. Creating...'
1910 CREATE NONCLUSTERED INDEX [_IX_LocalizedPropertyID] ON [dbo].[tbLocalizedPropertyForRevision]
1911 (
1912 [LocalizedPropertyID] ASC
1913 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1914 PRINT 'Done.'
1915 END
1916ELSE
1917 BEGIN
1918 PRINT '_IX_LocalizedPropertyID on [dbo].[tbLocalizedPropertyForRevision] already created. No changes made.'
1919 END
1920/****** Object: Index [_IX_RowID_RevisionID] Script Date: 2017-06-05 17:29:12 ******/
1921IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = '_IX_RowID_RevisionID' AND object_id = OBJECT_ID('[dbo].[tbRevision]'))
1922 BEGIN
1923 PRINT '_IX_RowID_RevisionID on [dbo].[tbRevision] doesn''t exist. Creating...'
1924 CREATE NONCLUSTERED INDEX [_IX_RowID_RevisionID] ON [dbo].[tbRevision]
1925 (
1926 [RowID] ASC,
1927 [RevisionID] ASC
1928 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
1929 PRINT 'Done.'
1930 END
1931ELSE
1932 BEGIN
1933 PRINT '_IX_RowID_RevisionID on [dbo].[tbRevision] already created. No changes made.'
1934 END
1935/****** Object: Index [_IX_SupersededUpdateID] Script Date: 2017-06-05 17:29:42 ******/
1936IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = '_IX_SupersededUpdateID' AND object_id = OBJECT_ID('[dbo].[tbRevisionSupersedesUpdate]'))
1937 BEGIN
1938 PRINT '_IX_SupersededUpdateID on [dbo].[tbRevisionSupersedesUpdate] doesn''t exist. Creating...'
1939 CREATE NONCLUSTERED INDEX [_IX_SupersededUpdateID] ON [dbo].[tbRevisionSupersedesUpdate]
1940 (
1941 [SupersededUpdateID] ASC
1942 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
1943 PRINT 'Done.'
1944 END
1945ELSE
1946 BEGIN
1947 PRINT '_IX_SupersededUpdateID on [dbo].[tbRevisionSupersedesUpdate] already created. No changes made.'
1948 END
1949"@
1950 Write-Verbose "Create a file with the content of the WSUSIndexOptimization Script above in the same working directory as this PowerShell script is running."
1951 $WSUSIndexOptimizationSQLScriptFile = "$ScriptPath\WSUSIndexOptimization.sql"
1952 $WSUSIndexOptimizationSQLScript | Out-File "$WSUSIndexOptimizationSQLScriptFile"
1953
1954 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1955 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
1956 Write-Verbose "Execute the SQL Script and store the results in a variable."
1957 $WSUSIndexOptimizationSQLScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$WSUSIndexOptimizationSQLScriptFile`" -I")
1958 Write-Verbose "`$WSUSIndexOptimizationSQLScriptJob = $WSUSIndexOptimizationSQLScriptJobCommand"
1959 $WSUSIndexOptimizationSQLScriptJob = Start-Job -ScriptBlock $WSUSIndexOptimizationSQLScriptJobCommand
1960 Wait-Job $WSUSIndexOptimizationSQLScriptJob
1961 $WSUSIndexOptimizationSQLScriptJobOutput = Receive-Job $WSUSIndexOptimizationSQLScriptJob
1962 Remove-Job $WSUSIndexOptimizationSQLScriptJob
1963 Write-Verbose "Remove the SQL Script file."
1964 Remove-Item "$WSUSIndexOptimizationSQLScriptFile"
1965 $FinishedRunning = Get-Date
1966 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
1967 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1968 $Script:WSUSIndexOptimizationOutputTXT += "WSUS Index Optimization:`r`n`r`n"
1969 $Script:WSUSIndexOptimizationOutputTXT += $WSUSIndexOptimizationSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1970 $Script:WSUSIndexOptimizationOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUS Index Optimization:</span></p>`n`n"
1971 $Script:WSUSIndexOptimizationOutputHTML += $WSUSIndexOptimizationSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1972 $Script:WSUSIndexOptimizationOutputTXT += "WSUS Index Optimization Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1973 $Script:WSUSIndexOptimizationOutputHTML += "<p>WSUS Index Optimization Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
1974
1975 # Variables Output
1976 # $WSUSIndexOptimizationOutputTXT
1977 # $WSUSIndexOptimizationOutputHTML
1978}
1979#endregion WSUSIndexOptimization Function
1980
1981#region RemoveDeclinedWSUSUpdates Function
1982################################
1983# Remove Declined WSUS #
1984# Updates Stream #
1985################################
1986
1987function RemoveDeclinedWSUSUpdates {
1988 param (
1989 [Switch]$Display,
1990 [Switch]$Proceed
1991 )
1992 # Log the date first
1993 $DateNow = Get-Date
1994 Write-Verbose "Create an update scope"
1995 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
1996 Write-Verbose "By default the update scope is created for any approval states"
1997 $UpdateScope.ApprovedStates = "Any"
1998 Write-Verbose "Get all updates that are Declined"
1999 $RemoveDeclinedWSUSUpdatesUpdates = $WSUSServerAdminProxy.GetUpdates($UpdateScope) | Where { ($_.isDeclined) }
2000 function RemoveDeclinedWSUSUpdatesCountUpdates {
2001 Write-Verbose "First count how many updates will be removed that are already declined updates - just for fun. I like fun :)"
2002 $Script:RemoveDeclinedWSUSUpdatesCountUpdatesCount = "{0:N0}" -f $RemoveDeclinedWSUSUpdatesUpdates.Count
2003 $Script:RemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT += "The number of declined updates that would be removed from the database are: $RemoveDeclinedWSUSUpdatesCountUpdatesCount.`r`n`r`n"
2004 $Script:RemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML += "<p>The number of declined updates that would be removed from the database are: $RemoveDeclinedWSUSUpdatesCountUpdatesCount.</p>`n"
2005
2006 # Variables Output
2007 # $RemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT
2008 # $RemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML
2009 }
2010
2011 function RemoveDeclinedWSUSUpdatesDisplayUpdates {
2012 Write-Verbose "Display the titles of the declined updates that will be removed from the database - just for fun. I like fun :)"
2013 $Script:RemoveDeclinedWSUSUpdatesDisplayOutputHTML += "<ol>`n"
2014 $Count=0
2015 ForEach ($update in $RemoveDeclinedWSUSUpdatesUpdates) {
2016 $Count++
2017 $Script:RemoveDeclinedWSUSUpdatesDisplayOutputTXT += "$($Count). $($update.title) - https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`r`n"
2018 $Script:RemoveDeclinedWSUSUpdatesDisplayOutputHTML += "<li><a href=`"https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`">$($update.title)</a></li>`n"
2019 }
2020 $Script:RemoveDeclinedWSUSUpdatesDisplayOutputTXT += "`r`n"
2021 $Script:RemoveDeclinedWSUSUpdatesDisplayOutputHTML += "</ol>`n"
2022
2023 # Variables Output
2024 # $RemoveDeclinedWSUSUpdatesDisplayOutputTXT
2025 # $RemoveDeclinedWSUSUpdatesDisplayOutputHTML
2026 }
2027
2028 function RemoveDeclinedWSUSUpdatesProceed {
2029 Write-Output "You've chosen to remove declined updates from the database. Removing $RemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates."
2030 Write-Output ""
2031 Write-Output "Please be patient, this may take a while."
2032 Write-Output ""
2033 Write-Output "It is not abnormal for this process to take minutes or hours. It varies per install and per execution."
2034 Write-Output ""
2035 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."
2036 Write-Output ""
2037 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."
2038 $Script:RemoveDeclinedWSUSUpdatesProceedOutputTXT += "You've chosen to remove declined updates from the database. Removing $RemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates.`r`n`r`n"
2039 $Script:RemoveDeclinedWSUSUpdatesProceedOutputHTML += "<p>You've chosen to remove declined updates from the database. <strong>Removing $RemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates.</strong></p>`n"
2040 # Remove these updates
2041 $RemoveDeclinedWSUSUpdatesUpdates | ForEach-Object {
2042 $DeleteID = $_.Id.UpdateId.ToString()
2043 Try {
2044 $RemoveDeclinedWSUSUpdatesUpdateTitle = $($_.Title)
2045 Write-Output "Deleting" $RemoveDeclinedWSUSUpdatesUpdateTitle
2046 $WSUSServerAdminProxy.DeleteUpdate($DeleteId)
2047 }
2048 Catch {
2049 $ExceptionError = $_.Exception
2050 if ([string]::isnullorempty($RemoveDeclinedWSUSUpdatesProceedExceptionsTXT)) { $RemoveDeclinedWSUSUpdatesProceedExceptionsTXT = "" }
2051 if ([string]::isnullorempty($RemoveDeclinedWSUSUpdatesProceedExceptionsHTML)) { $RemoveDeclinedWSUSUpdatesProceedExceptionsHTML = "" }
2052 $RemoveDeclinedWSUSUpdatesProceedExceptionsTXT += "Error: $RemoveDeclinedWSUSUpdatesUpdateTitle`r`n`r`n$ExceptionError.InnerException`r`n`r`n"
2053 $RemoveDeclinedWSUSUpdatesProceedExceptionsHTML += "<li><p>$RemoveDeclinedWSUSUpdatesUpdateTitle</p>$ExceptionError.InnerException</li>"
2054 }
2055 Finally {
2056 if ($ExceptionError) {
2057 Write-Output "Errors:" $ExceptionError.Message
2058 Remove-Variable ExceptionError
2059 } else {
2060 Write-Verbose "Successful"
2061 }
2062 }
2063 }
2064 if (-not [string]::isnullorempty($RemoveDeclinedWSUSUpdatesProceedExceptionsTXT)) {
2065 $Script:RemoveDeclinedWSUSUpdatesProceedOutputTXT += "*** Errors Removing Declined WSUS Updates ***`r`n"
2066 $Script:RemoveDeclinedWSUSUpdatesProceedOutputTXT += $RemoveDeclinedWSUSUpdatesProceedExceptionsTXT
2067 $Script:RemoveDeclinedWSUSUpdatesProceedOutputTXT += "`r`n`r`n"
2068 }
2069 if (-not [string]::isnullorempty($RemoveDeclinedWSUSUpdatesProceedExceptionsHTML)) {
2070 $Script:RemoveDeclinedWSUSUpdatesProceedOutputHTML += "<div class='error'><h1>Errors Removing Declined WSUS Updates</h1><ol start='1'>"
2071 $Script:RemoveDeclinedWSUSUpdatesProceedOutputHTML += $RemoveDeclinedWSUSUpdatesProceedExceptionsHTML
2072 $Script:RemoveDeclinedWSUSUpdatesProceedOutputHTML += "</ol></div>"
2073 }
2074
2075 # Variables Output
2076 # $RemoveDeclinedWSUSUpdatesProceedOutputTXT
2077 # $RemoveDeclinedWSUSUpdatesProceedOutputHTML
2078 }
2079
2080 RemoveDeclinedWSUSUpdatesCountUpdates
2081 if ($Display -ne $False) { RemoveDeclinedWSUSUpdatesDisplayUpdates }
2082 if ($Proceed -ne $False) { RemoveDeclinedWSUSUpdatesProceed }
2083 $FinishedRunning = Get-Date
2084 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
2085
2086 $Script:RemoveDeclinedWSUSUpdatesOutputTXT += "Remove Declined WSUS Updates:`r`n`r`n"
2087 $Script:RemoveDeclinedWSUSUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Remove Declined WSUS Updates:</span></p>`n<ol>`n"
2088 $Script:RemoveDeclinedWSUSUpdatesOutputTXT += $RemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT
2089 $Script:RemoveDeclinedWSUSUpdatesOutputHTML += $RemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML
2090 if ($Display -ne $False) {
2091 $Script:RemoveDeclinedWSUSUpdatesOutputTXT += $RemoveDeclinedWSUSUpdatesDisplayOutputTXT
2092 $Script:RemoveDeclinedWSUSUpdatesOutputHTML += $RemoveDeclinedWSUSUpdatesDisplayOutputHTML
2093 }
2094 if ($Proceed -ne $False) {
2095 $Script:RemoveDeclinedWSUSUpdatesOutputTXT += $RemoveDeclinedWSUSUpdatesProceedOutputTXT
2096 $Script:RemoveDeclinedWSUSUpdatesOutputHTML += $RemoveDeclinedWSUSUpdatesProceedOutputHTML
2097 }
2098 $Script:RemoveDeclinedWSUSUpdatesOutputTXT += "Remove Declined WSUS Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2099 $Script:RemoveDeclinedWSUSUpdatesOutputHTML += "<p>Remove Declined WSUS Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2100
2101 # Variables Output
2102 # $RemoveDeclinedWSUSUpdatesOutputTXT
2103 # $RemoveDeclinedWSUSUpdatesOutputHTML
2104}
2105#endregion RemoveDeclinedWSUSUpdates Function
2106
2107#region CompressUpdateRevisions Function
2108################################
2109# Compress Update #
2110# Revisions Stream #
2111################################
2112
2113function CompressUpdateRevisions {
2114 Param (
2115 )
2116 $DateNow = Get-Date
2117 $CompressUpdateRevisionsSQLScript = @"
2118USE SUSDB;
2119GO
2120-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
2121SET NOCOUNT ON
2122
2123DECLARE @var1 INT, @curitem INT, @totaltocompress INT
2124DECLARE @msg nvarchar(200)
2125
2126IF EXISTS (
2127 SELECT * FROM tempdb.dbo.sysobjects o
2128 WHERE o.xtype IN ('U')
2129 AND o.id = object_id(N'tempdb..#results')
2130)
2131DROP TABLE #results
2132CREATE TABLE #results (Col1 INT)
2133
2134-- Compress Update Revisions
2135INSERT INTO #results(Col1) EXEC spGetUpdatesToCompress
2136SET @totaltocompress = (SELECT COUNT(*) FROM #results)
2137SELECT @curitem=1
2138DECLARE WC Cursor FOR SELECT Col1 FROM #results;
2139OPEN WC
2140FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
2141BEGIN
2142 SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltocompress as varchar(5)) + ': Compressing ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
2143 RAISERROR(@msg,0,1) WITH NOWAIT
2144 EXEC spCompressUpdate @localUpdateID=@var1
2145 SET @curitem = @curitem +1
2146 FETCH NEXT FROM WC INTO @var1
2147END
2148CLOSE WC
2149DEALLOCATE WC
2150DROP TABLE #results
2151"@
2152 Write-Verbose "Create a file with the content of the CompressUpdateRevisions Script above in the same working directory as this PowerShell script is running."
2153 $CompressUpdateRevisionsSQLScriptFile = "$ScriptPath\CompressUpdateRevisions.sql"
2154 $CompressUpdateRevisionsSQLScript | Out-File "$CompressUpdateRevisionsSQLScriptFile"
2155
2156 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
2157 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
2158 Write-Verbose "Execute the SQL Script and store the results in a variable."
2159 $CompressUpdateRevisionsSQLScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$CompressUpdateRevisionsSQLScriptFile`" -I")
2160 Write-Verbose "`$CompressUpdateRevisionsSQLScriptJob = $CompressUpdateRevisionsSQLScriptJobCommand"
2161 $CompressUpdateRevisionsSQLScriptJob = Start-Job -ScriptBlock $CompressUpdateRevisionsSQLScriptJobCommand
2162 Wait-Job $CompressUpdateRevisionsSQLScriptJob
2163 $CompressUpdateRevisionsSQLScriptJobOutput = Receive-Job $CompressUpdateRevisionsSQLScriptJob
2164 Remove-Job $CompressUpdateRevisionsSQLScriptJob
2165 Write-Verbose "Remove the SQL Script file."
2166 Remove-Item "$CompressUpdateRevisionsSQLScriptFile"
2167 $FinishedRunning = Get-Date
2168 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
2169 # Setup variables to store the output to be added at the very end of the script for logging purposes.
2170 $Script:CompressUpdateRevisionsOutputTXT += "Compress Update Revisions:`r`n`r`n"
2171 $Script:CompressUpdateRevisionsOutputTXT += $CompressUpdateRevisionsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
2172 $Script:CompressUpdateRevisionsOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Compress Update Revisions:</span></p>`n`n"
2173 $Script:CompressUpdateRevisionsOutputHTML += $CompressUpdateRevisionsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
2174 $Script:CompressUpdateRevisionsOutputTXT += "Compress Update Revisions Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2175 $Script:CompressUpdateRevisionsOutputHTML += "<p>Compress Update Revisions Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2176
2177 # Variables Output
2178 # $CompressUpdateRevisionsOutputTXT
2179 # $CompressUpdateRevisionsOutputHTML
2180}
2181#endregion CompressUpdateRevisions Function
2182
2183#region RemoveObsoleteUpdates Function
2184################################
2185# Remove Obsolete #
2186# Updates Stream #
2187################################
2188
2189function RemoveObsoleteUpdates {
2190 Param (
2191 )
2192 $DateNow = Get-Date
2193 $RemoveObsoleteUpdatesSQLScript = @"
2194USE SUSDB;
2195GO
2196-- SET NOCOUNT ON added to prevent extra result sets from
2197-- interfering with SELECT statements.
2198SET NOCOUNT ON
2199
2200DECLARE @var1 INT, @curitem INT, @totaltoremove INT
2201DECLARE @msg nvarchar(200)
2202
2203IF EXISTS (
2204 SELECT * FROM tempdb.dbo.sysobjects o
2205 WHERE o.xtype IN ('U')
2206 AND o.id = object_id(N'tempdb..#results')
2207)
2208DROP TABLE #results
2209CREATE TABLE #results (Col1 INT)
2210
2211-- Remove Obsolete Updates
2212INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
2213SET @totaltoremove = (SELECT COUNT(*) FROM #results)
2214SELECT @curitem=1
2215DECLARE WC Cursor FOR SELECT Col1 FROM #results
2216OPEN WC
2217FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
2218BEGIN
2219 SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltoremove as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
2220 RAISERROR(@msg,0,1) WITH NOWAIT
2221 EXEC spDeleteUpdate @localUpdateID=@var1
2222 SET @curitem = @curitem +1
2223 FETCH NEXT FROM WC INTO @var1
2224END
2225CLOSE WC
2226DEALLOCATE WC
2227DROP TABLE #results
2228"@
2229 Write-Output ""
2230 Write-Output "Please be patient, this may take a while."
2231 Write-Output ""
2232 Write-Output "It is not abnormal for this process to take minutes or hours. It varies per install and per execution."
2233 Write-Output ""
2234 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."
2235 Write-Verbose "Create a file with the content of the RemoveObsoleteUpdates Script above in the same working directory as this PowerShell script is running."
2236 $RemoveObsoleteUpdatesSQLScriptFile = "$ScriptPath\RemoveObsoleteUpdates.sql"
2237 $RemoveObsoleteUpdatesSQLScript | Out-File "$RemoveObsoleteUpdatesSQLScriptFile"
2238 Write-Debug "Just wrote to script file"
2239 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
2240 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
2241 Write-Verbose "Execute the SQL Script and store the results in a variable."
2242 $RemoveObsoleteUpdatesSQLScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$RemoveObsoleteUpdatesSQLScriptFile`" -I")
2243 Write-Verbose "`$RemoveObsoleteUpdatesSQLScriptJobCommand = $RemoveObsoleteUpdatesSQLScriptJobCommand"
2244 $RemoveObsoleteUpdatesSQLScriptJob = Start-Job -ScriptBlock $RemoveObsoleteUpdatesSQLScriptJobCommand
2245 Wait-Job $RemoveObsoleteUpdatesSQLScriptJob
2246 $RemoveObsoleteUpdatesSQLScriptJobOutput = Receive-Job $RemoveObsoleteUpdatesSQLScriptJob
2247 Write-Debug "Just finished - check RemoveObsoleteUpdatesSQLScriptJobOutput"
2248 Remove-Job $RemoveObsoleteUpdatesSQLScriptJob
2249 Write-Verbose "Remove the SQL Script file."
2250 Remove-Item "$RemoveObsoleteUpdatesSQLScriptFile"
2251 $FinishedRunning = Get-Date
2252 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
2253 # Setup variables to store the output to be added at the very end of the script for logging purposes.
2254 $Script:RemoveObsoleteUpdatesOutputTXT += "Remove Obsolete Updates:`r`n`r`n"
2255 $Script:RemoveObsoleteUpdatesOutputTXT += $RemoveObsoleteUpdatesSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
2256 $Script:RemoveObsoleteUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Remove Obsolete Updates:</span></p>`n`n"
2257 $Script:RemoveObsoleteUpdatesOutputHTML += $RemoveObsoleteUpdatesSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
2258 $Script:RemoveObsoleteUpdatesOutputTXT += "Remove Obsolete Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2259 $Script:RemoveObsoleteUpdatesOutputHTML += "<p>Remove Obsolete Updates Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2260
2261 # Variables Output
2262 # $RemoveObsoleteUpdatesOutputTXT
2263 # $RemoveObsoleteUpdatesOutputHTML
2264}
2265#endregion RemoveObsoleteUpdates Function
2266
2267#region WSUSDBMaintenance Function
2268################################
2269# WSUS DB Maintenance #
2270# Stream #
2271################################
2272
2273function WSUSDBMaintenance {
2274 Param (
2275 [Switch]$NoOutput
2276 )
2277 $DateNow = Get-Date
2278 $WSUSDBMaintenanceSQLScript = @"
2279/*
2280################################
2281# WSUSDBMaintenance #
2282# SQL Script #
2283# Version 1.0 #
2284# Taken from TechNet #
2285# referenced below. #
2286# #
2287################################
2288*/
2289-- Taken from https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61
2290
2291/******************************************************************************
2292This sample T-SQL script performs basic maintenance tasks on SUSDB
22931. Identifies indexes that are fragmented and defragments them. For certain
2294 tables, a fill-factor is set in order to improve insert performance.
2295 Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx
2296 and tailored for SUSDB requirements
22972. Updates potentially out-of-date table statistics.
2298******************************************************************************/
2299
2300USE SUSDB;
2301GO
2302SET NOCOUNT ON;
2303
2304-- Rebuild or reorganize indexes based on their fragmentation levels
2305DECLARE @work_to_do TABLE (
2306 objectid int
2307 , indexid int
2308 , pagedensity float
2309 , fragmentation float
2310 , numrows int
2311)
2312
2313DECLARE @objectid int;
2314DECLARE @indexid int;
2315DECLARE @schemaname nvarchar(130);
2316DECLARE @objectname nvarchar(130);
2317DECLARE @indexname nvarchar(130);
2318DECLARE @numrows int
2319DECLARE @density float;
2320DECLARE @fragmentation float;
2321DECLARE @command nvarchar(4000);
2322DECLARE @fillfactorset bit
2323DECLARE @numpages int
2324
2325-- Select indexes that need to be defragmented based on the following
2326-- * Page density is low
2327-- * External fragmentation is high in relation to index size
2328PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
2329INSERT @work_to_do
2330SELECT
2331 f.object_id
2332 , index_id
2333 , avg_page_space_used_in_percent
2334 , avg_fragmentation_in_percent
2335 , record_count
2336FROM
2337 sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
2338WHERE
2339 (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
2340 or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)
2341 or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)
2342
2343PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))
2344
2345PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)
2346
2347SELECT @numpages = sum(ps.used_page_count)
2348FROM
2349 @work_to_do AS fi
2350 INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
2351 INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
2352
2353-- Declare the cursor for the list of indexes to be processed.
2354DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
2355
2356-- Open the cursor.
2357OPEN curIndexes
2358
2359-- Loop through the indexes
2360WHILE (1=1)
2361BEGIN
2362 FETCH NEXT FROM curIndexes
2363 INTO @objectid, @indexid, @density, @fragmentation, @numrows;
2364 IF @@FETCH_STATUS < 0 BREAK;
2365
2366 SELECT
2367 @objectname = QUOTENAME(o.name)
2368 , @schemaname = QUOTENAME(s.name)
2369 FROM
2370 sys.objects AS o
2371 INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
2372 WHERE
2373 o.object_id = @objectid;
2374
2375 SELECT
2376 @indexname = QUOTENAME(name)
2377 , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
2378 FROM
2379 sys.indexes
2380 WHERE
2381 object_id = @objectid AND index_id = @indexid;
2382
2383 IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
2384 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
2385 ELSE IF @numrows >= 5000 AND @fillfactorset = 0
2386 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
2387 ELSE
2388 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
2389 PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
2390 EXEC (@command);
2391 PRINT convert(nvarchar, getdate(), 121) + N' Done.';
2392END
2393
2394-- Close and deallocate the cursor.
2395CLOSE curIndexes;
2396DEALLOCATE curIndexes;
2397
2398IF EXISTS (SELECT * FROM @work_to_do)
2399BEGIN
2400 PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
2401 SELECT @numpages = @numpages - sum(ps.used_page_count)
2402 FROM
2403 @work_to_do AS fi
2404 INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
2405 INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
2406 PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
2407END
2408GO
2409
2410--Update all statistics
2411PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
2412EXEC sp_updatestats
2413PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
2414GO
2415"@
2416 Write-Verbose "Create a file with the content of the WSUSDBMaintenance Script above in the same working directory as this PowerShell script is running."
2417 $WSUSDBMaintenanceSQLScriptFile = "$ScriptPath\WSUSDBMaintenance.sql"
2418 $WSUSDBMaintenanceSQLScript | Out-File "$WSUSDBMaintenanceSQLScriptFile"
2419
2420 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
2421 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
2422 Write-Verbose "Execute the SQL Script and store the results in a variable."
2423 $WSUSDBMaintenanceSQLScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$WSUSDBMaintenanceSQLScriptFile`" -I")
2424 Write-Verbose "`$WSUSDBMaintenanceSQLScriptJobCommand = $WSUSDBMaintenanceSQLScriptJobCommand"
2425 $WSUSDBMaintenanceSQLScriptJob = Start-Job -ScriptBlock $WSUSDBMaintenanceSQLScriptJobCommand
2426 Wait-Job $WSUSDBMaintenanceSQLScriptJob
2427 $WSUSDBMaintenanceSQLScriptJobOutput = Receive-Job $WSUSDBMaintenanceSQLScriptJob
2428 Remove-Job $WSUSDBMaintenanceSQLScriptJob
2429 Write-Verbose "Remove the SQL Script file."
2430 Remove-Item "$WSUSDBMaintenanceSQLScriptFile"
2431 $FinishedRunning = Get-Date
2432 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
2433 # Setup variables to store the output to be added at the very end of the script for logging purposes.
2434 if ($NoOutput -eq $False) {
2435 $Script:WSUSDBMaintenanceOutputTXT += "WSUS DB Maintenance:`r`n`r`n"
2436 $Script:WSUSDBMaintenanceOutputTXT += $WSUSDBMaintenanceSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
2437 $Script:WSUSDBMaintenanceOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUS DB Maintenance:</span></p>`n`n"
2438 $Script:WSUSDBMaintenanceOutputHTML += $WSUSDBMaintenanceSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
2439 } else {
2440 $Script:WSUSDBMaintenanceOutputTXT += "WSUS DB Maintenance:`r`n`r`n"
2441 $Script:WSUSDBMaintenanceOutputTXT += "The WSUS DB Maintenance Stream was run with the -NoOutput switch.`r`n"
2442 $Script:WSUSDBMaintenanceOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUS DB Maintenance:</span></p>`n`n"
2443 $Script:WSUSDBMaintenanceOutputHTML += "<p>The WSUS DB Maintenance Stream was run with the -NoOutput switch.</p>`n`n"
2444 }
2445 $Script:WSUSDBMaintenanceOutputTXT += "WSUS DB Maintenance Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2446 $Script:WSUSDBMaintenanceOutputHTML += "<p>WSUS DB Maintenance Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2447
2448 # Variables Output
2449 # $WSUSDBMaintenanceOutputTXT
2450 # $WSUSDBMaintenanceOutputHTML
2451}
2452#endregion WSUSDBMaintenance Function
2453
2454#region CleanUpWSUSSynchronizationLogs Function
2455################################
2456# Clean Up WSUS #
2457# Synchronization Logs Stream #
2458################################
2459
2460function CleanUpWSUSSynchronizationLogs {
2461 Param(
2462 [Int]$ConsistencyNumber,
2463 [String]$ConsistencyTime,
2464 [Switch]$All
2465 )
2466 $DateNow = Get-Date
2467 $CleanUpWSUSSynchronizationLogsSQLScript = @"
2468/*
2469################################
2470# WSUS Synchronization #
2471# Cleanup SQL Script #
2472# Version 1.0 #
2473# Taken from various sources #
2474# from the Internet. #
2475# #
2476################################
2477*/
2478$(
2479 if ($ConsistencyNumber -ne "0") {
2480 $("
2481USE SUSDB
2482GO
2483DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389') AND DATEDIFF($($ConsistencyTime), TimeAtServer, CURRENT_TIMESTAMP) >= $($ConsistencyNumber);
2484GO")
2485}
2486elseif ($All -ne $False) {
2487$("USE SUSDB
2488GO
2489DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389')
2490GO")
2491}
2492)
2493"@
2494 Write-Verbose "Create a file with the content of the CleanUpWSUSSynchronizationLogs Script above in the same working directory as this PowerShell script is running."
2495 $CleanUpWSUSSynchronizationLogsSQLScriptFile = "$ScriptPath\CleanUpWSUSSynchronizationLogs.sql"
2496 $CleanUpWSUSSynchronizationLogsSQLScript | Out-File "$CleanUpWSUSSynchronizationLogsSQLScriptFile"
2497 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
2498 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
2499 Write-Verbose "Execute the SQL Script and store the results in a variable."
2500 $CleanUpWSUSSynchronizationLogsSQLScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$CleanUpWSUSSynchronizationLogsSQLScriptFile`" -I")
2501 Write-Verbose "`$CleanUpWSUSSynchronizationLogsSQLScriptJobCommand = $CleanUpWSUSSynchronizationLogsSQLScriptJobCommand"
2502 $CleanUpWSUSSynchronizationLogsSQLScriptJob = Start-Job -ScriptBlock $CleanUpWSUSSynchronizationLogsSQLScriptJobCommand
2503 Wait-Job $CleanUpWSUSSynchronizationLogsSQLScriptJob
2504 $CleanUpWSUSSynchronizationLogsSQLScriptJobOutput = Receive-Job $CleanUpWSUSSynchronizationLogsSQLScriptJob
2505 Remove-Job $CleanUpWSUSSynchronizationLogsSQLScriptJob
2506 Write-Verbose "Remove the SQL Script file."
2507 Remove-Item "$CleanUpWSUSSynchronizationLogsSQLScriptFile"
2508 $FinishedRunning = Get-Date
2509 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
2510
2511 # Setup variables to store the output to be added at the very end of the script for logging purposes.
2512 $Script:CleanUpWSUSSynchronizationLogsSQLOutputTXT += "Clean Up WSUS Synchronization Logs:`r`n`r`n"
2513 $Script:CleanUpWSUSSynchronizationLogsSQLOutputTXT += $CleanUpWSUSSynchronizationLogsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
2514 $Script:CleanUpWSUSSynchronizationLogsSQLOutputTXT += "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})
2515
2516 $Script:CleanUpWSUSSynchronizationLogsSQLOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Clean Up WSUS Synchronization Logs:</span></p>`r`n"
2517 $Script:CleanUpWSUSSynchronizationLogsSQLOutputHTML += $CleanUpWSUSSynchronizationLogsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
2518 $Script:CleanUpWSUSSynchronizationLogsSQLOutputHTML += "<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})
2519
2520 # Variables Output
2521 # $CleanUpWSUSSynchronizationLogsSQLOutputTXT
2522 # $CleanUpWSUSSynchronizationLogsSQLOutputHTML
2523}
2524#endregion CleanUpWSUSSynchronizationLogs Function
2525
2526#region DirtyDatabaseCheck Function
2527################################
2528# Dirty Database Check #
2529# Stream #
2530################################
2531
2532function DirtyDatabaseCheck {
2533 param (
2534 )
2535 $DateNow = Get-Date
2536 $DirtyDatabaseCheckSQLScript = @"
2537/*
2538################################
2539# Dirty Database Check #
2540# SQL Script #
2541# Version 1.0 #
2542# #
2543################################
2544*/
2545USE SUSDB
2546select TotalResults = Count(*)
2547from tbFile
2548where (IsEncrypted = 1 and DecryptionKey is NULL) OR ((FileName like '%.esd' and IsEncrypted = 0) and DecryptionKey is NOT NULL) OR ((FileName like '%.esd' and IsEncrypted = 0) AND (FileName not like '%10586%.esd'))
2549"@
2550 Write-Verbose "Create a file with the content of the DirtyDatabaseCheck Script above in the same working directory as this PowerShell script is running."
2551 $DirtyDatabaseCheckSQLScriptFile = "$ScriptPath\DirtyDatabaseCheck.sql"
2552 $DirtyDatabaseCheckSQLScript | Out-File "$DirtyDatabaseCheckSQLScriptFile"
2553 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
2554 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
2555 Write-Verbose "Execute the SQL Script and store the results in a variable."
2556 $DirtyDatabaseCheckSQLScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$DirtyDatabaseCheckSQLScriptFile`" -I")
2557 Write-Verbose "`$DirtyDatabaseCheckSQLScriptJobCommand = $DirtyDatabaseCheckSQLScriptJobCommand"
2558 $DirtyDatabaseCheckSQLScriptJob = Start-Job -ScriptBlock $DirtyDatabaseCheckSQLScriptJobCommand
2559 Wait-Job $DirtyDatabaseCheckSQLScriptJob
2560 $DirtyDatabaseCheckSQLScriptJobOutput = Receive-Job $DirtyDatabaseCheckSQLScriptJob
2561 Remove-Job $DirtyDatabaseCheckSQLScriptJob
2562 Write-Verbose "Remove the SQL Script file."
2563 Remove-Item "$DirtyDatabaseCheckSQLScriptFile"
2564 if ($DirtyDatabaseCheckSQLScriptJobOutput.Trim()[3] -eq "0") {
2565 Write-Output "You have a clean database."
2566 $DirtyDatabaseCheckOutputTXT = "You have a clean database."
2567 } else {
2568 Write-Output 'You have a dirty database. Please see: https://support.microsoft.com/en-us/help/3194588 for more information about it.'
2569 $DirtyDatabaseFixOutput ="You have a dirty database. Please see: https://support.microsoft.com/en-us/help/3194588 for more information about it."
2570 Write-Output "First we need to install the WSUS Index Optimization so that this doesn't take as long."
2571 $DirtyDatabaseFixOutput += "First we need to install the WSUS Index Optimization so that this doesn't take as long."
2572 WSUSIndexOptimization
2573 Write-Output $WSUSIndexOptimizationOutputTXT
2574 $DirtyDatabaseFixOutput += "Now we need to run the WSUS DB Maintenance on the database to make sure we're starting with an optimized database."
2575 Write-Output "Now we need to run the WSUS DB Maintenance on the database to make sure we're starting with an optimized database."
2576 WSUSDBMaintenance
2577 Write-Output "Done. Now let's begin cleansing your database."
2578 $DirtyDatabaseFixOutput += "Done. Now let's begin cleansing your database."
2579 Write-Output "Attempting to fix your database by the methods Microsoft recommends but augmented for future-proofing..."
2580 $DirtyDatabaseFixOutput += "Attempting to fix your database by the methods Microsoft recommends but augmented for future-proofing..."
2581 Write-Verbose "First let's disable the 'Upgrades' Classification"
2582 Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Upgrades"} | Set-WsusClassification -Disable
2583 Write-Verbose "Create an update scope"
2584 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
2585 Write-Verbose "Set the update scope to 'Any' approval states"
2586 $UpdateScope.ApprovedStates = "Any"
2587 Write-Verbose "Get all updates that do not match 1511 or 1507, but do have 'Windows 10' in the title and stick them into a variable."
2588 $DirtyDatabaseUpdates = $WSUSServerAdminProxy.GetUpdates($UpdateScope) | Where-Object { -not($_.Title -match '1511' -or $_.Title -match '1507') -and ($_.Title -imatch 'Windows 10') }
2589 Write-Verbose "Let's decline them all"
2590 $DirtyDatabaseUpdates | foreach { $_.Decline() }
2591 Write-Verbose "Let's remove them from the WSUS Server"
2592 $DirtyDatabaseUpdates | foreach { $WSUSServerAdminProxy.DeleteUpdate($_.Id.UpdateId) }
2593 Write-Verbose "Now let's re-enable the 'Upgrades' Classification"
2594 Get-WsusClassification | Where-Object -FilterScript {$_.Classification.Title -Eq "Upgrades"} | Set-WsusClassification
2595 Write-Verbose "We need to run a SQL Script to remove these files from the WSUS metadata"
2596 $DirtyDatabaseFixSQLScript =@"
2597/*
2598################################
2599# Dirty Database Fix #
2600# SQL Script #
2601# Version 1.1 #
2602# #
2603################################
2604*/
2605use SUSDB
2606declare @NotNeededFiles table (FileDigest binary(20) UNIQUE);
2607insert into @NotNeededFiles(FileDigest) (select FileDigest from tbFile where FileName like '%.esd' and (FileName not like '%10240%.esd' or FileName not like '%10586%.esd') except select FileDigest from tbFileForRevision);
2608delete from tbFileOnServer where FileDigest in (select FileDigest from @NotNeededFiles)
2609delete from tbFile where FileDigest in (select FileDigest from @NotNeededFiles)
2610"@
2611 $DirtyDatabaseFixSQLScriptFile = "$ScriptPath\DirtyDatabaseCheck.sql"
2612 $DirtyDatabaseFixSQLScript | Out-File "$DirtyDatabaseFixSQLScriptFile"
2613 # Re-jig the $SQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
2614 $SQLConnectCommand = $SQLConnectCommand.Replace('$','`$')
2615 Write-Verbose "Execute the SQL Script and store the results in a variable."
2616 $DirtyDatabaseFixSQLScriptJobCommand = [scriptblock]::create("$SQLConnectCommand -i `"$DirtyDatabaseFixSQLScriptFile`" -I")
2617 Write-Verbose "`$DirtyDatabaseFixSQLScriptJobCommand = $DirtyDatabaseFixSQLScriptJobCommand"
2618 $DirtyDatabaseFixSQLScriptJob = Start-Job -ScriptBlock $DirtyDatabaseFixSQLScriptJobCommand
2619 Wait-Job $DirtyDatabaseFixSQLScriptJob
2620 $DirtyDatabaseFixSQLScriptJobOutput = Receive-Job $DirtyDatabaseFixSQLScriptJob
2621 Remove-Job $DirtyDatabaseFixSQLScriptJob
2622 Write-Output $DirtyDatabaseFixSQLScriptJobOutput
2623 $DirtyDatabaseFixOutput += $DirtyDatabaseFixSQLScriptJobOutput
2624 Write-Verbose "Remove the SQL Script file."
2625 Remove-Item "$DirtyDatabaseFixSQLScriptFile"
2626 Write-Verbose "Finally, let's re-syncronize the server with Microsoft to pull down the updates again"
2627 $($WSUSServerAdminProxy.GetSubscription()).StartSynchronization()
2628 Write-Output "Your WSUS server has been fixed. A syncronization has been initialized. Please wait while it finishes. You can monitor it through the WSUS Console."
2629 $DirtyDatabaseFixOutput += "Your WSUS server has been fixed. A syncronization has been initialized. Please wait while it finishes. You can monitor it through the WSUS Console."
2630 $DirtyDatabaseFixOutputTXT = $DirtyDatabaseFixOutput
2631 }
2632 $FinishedRunning = Get-Date
2633 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
2634
2635 $Script:DirtyDatabaseOutputTXT = "Dirty Database Check Stream:`r`n`r`n"
2636 $Script:DirtyDatabaseOutputTXT += if ([string]::isnullorempty($DirtyDatabaseCheckOutputTXT)) { $DirtyDatabaseFixOutputTXT + "`r`n`r`n" } else { $DirtyDatabaseCheckOutputTXT + "`r`n`r`n" }
2637 $Script:DirtyDatabaseOutputTXT += "Dirty Database Check Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2638
2639 $Script:DirtyDatabaseOutputHTML = "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Dirty Database Check Stream:</span></p>`r`n"
2640 $Script:DirtyDatabaseOutputHTML += if ([string]::isnullorempty($DirtyDatabaseCheckOutputTXT)) { $DirtyDatabaseFixOutputTXT -creplace '\r\n', "<br>`r`n" -creplace '^',"<p>" -creplace '$', "</p>`r`n" } else { $DirtyDatabaseCheckOutputTXT -creplace '\r\n', "<br>`r`n" -creplace '^',"<p>" -creplace '$', "</p>`r`n" }
2641 $Script:DirtyDatabaseOutputHTML += "<p>Dirty Database Check Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2642
2643 # Variables Output
2644 # $DirtyDatabaseOutputTXT
2645 # $DirtyDatabaseOutputHTML
2646}
2647#endregion DirtyDatabaseCheck Function
2648
2649#region ComputerObjectCleanup Function
2650################################
2651# Computer Object Cleanup #
2652# Stream #
2653################################
2654
2655function ComputerObjectCleanup {
2656 $DateNow = Get-Date
2657 Write-Verbose "Create a new timespan using `$ComputerObjectCleanupSearchDays and find how many computers need to be cleaned up"
2658 $ComputerObjectCleanupSearchTimeSpan = New-Object timespan($ComputerObjectCleanupSearchDays,0,0,0)
2659 $ComputerObjectCleanupScope = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope
2660 $ComputerObjectCleanupScope.ToLastSyncTime = [DateTime]::UtcNow.Subtract($ComputerObjectCleanupSearchTimeSpan)
2661 $ComputerObjectCleanupSet = $WSUSServerAdminProxy.GetComputerTargets($ComputerObjectCleanupScope) | Sort-Object FullDomainName
2662 Write-Verbose "Clean up $($ComputerObjectCleanupSet.Count) computer objects"
2663 $WSUSServerAdminProxy.GetComputerTargets($ComputerObjectCleanupScope) | ForEach-Object { $_.Delete() }
2664
2665 $FinishedRunning = Get-Date
2666 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
2667
2668 # Setup variables to store the output to be added at the very end of the script for logging purposes.
2669 $Script:ComputerObjectCleanupOutputTXT += "Computer Object Cleanup:`r`n`r`n"
2670 if ($($ComputerObjectCleanupSet.Count) -gt "0") {
2671 $Script:ComputerObjectCleanupOutputTXT += "The following $($ComputerObjectCleanupSet.Count) $(if ($($ComputerObjectCleanupSet.Count) -eq "1") { "computer" } else { "computers" }) have been removed."
2672 $Script:ComputerObjectCleanupOutputTXT += $ComputerObjectCleanupSet | Select-Object FullDomainName,@{Expression=" "},LastSyncTime | Format-Table -AutoSize | Out-String
2673 } else { $Script:ComputerObjectCleanupOutputTXT += "There are no computers to clean up.`r`n" }
2674
2675 $Script:ComputerObjectCleanupOutputTXT += "Computer Object Cleanup Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2676 $Script:ComputerObjectCleanupOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Computer Object Cleanup:</span></p>`r`n"
2677 if ($($ComputerObjectCleanupSet.Count) -gt "0") {
2678 $Script:ComputerObjectCleanupOutputHTML += "<p>The following $($ComputerObjectCleanupSet.Count) $(if ($($ComputerObjectCleanupSet.Count) -eq "1") { "computer" } else { "computers" }) have been removed.</p>"
2679 $Script:ComputerObjectCleanupOutputHTML += ($ComputerObjectCleanupSet | Select-Object FullDomainName,LastSyncTime | ConvertTo-Html -Fragment) -replace "\<table\>",'<table class="gridtable">'
2680 } else { $Script:ComputerObjectCleanupOutputHTML += "<p>There are no computers to clean up.</p>" }
2681 $Script:ComputerObjectCleanupOutputHTML += "<p>Computer Object Cleanup Stream Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2682
2683 # Variables Output
2684 # $ComputerObjectCleanupOutputTXT
2685 # $ComputerObjectCleanupOutputHTML
2686}
2687
2688#endregion ComputerObjectCleanup Function
2689
2690#region WSUSServerCleanupWizard Function
2691################################
2692# WSUS Server Cleanup Wizard #
2693# Stream #
2694################################
2695
2696function WSUSServerCleanupWizard {
2697 $DateNow = Get-Date
2698 $WSUSServerCleanupWizardBody = "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUS Server Cleanup Wizard:</span></p>" | Out-String
2699 $CleanupManager = $WSUSServerAdminProxy.GetCleanupManager();
2700 $CleanupScope = New-Object Microsoft.UpdateServices.Administration.CleanupScope ($SCWSupersededUpdatesDeclined,$SCWExpiredUpdatesDeclined,$SCWObsoleteUpdatesDeleted,$SCWUpdatesCompressed,$SCWObsoleteComputersDeleted,$SCWUnneededContentFiles);
2701 $CleanupResults = $CleanupManager.PerformCleanup($CleanupScope)
2702 $FinishedRunning = Get-Date
2703 $DifferenceInTime = New-TimeSpan -Start $DateNow -End $FinishedRunning
2704
2705 $Script:WSUSServerCleanupWizardOutputTXT += "WSUS Server Cleanup Wizard:`r`n`r`n"
2706 $Script:WSUSServerCleanupWizardOutputTXT += "$WSUSServer`r`n"
2707 $Script:WSUSServerCleanupWizardOutputTXT += "Version: $($WSUSServerAdminProxy.Version)`r`n"
2708 #$Script:WSUSServerCleanupWizardOutputTXT += "Started: $($DateNow.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))`r`n"
2709 $Script:WSUSServerCleanupWizardOutputTXT += "SupersededUpdatesDeclined: $($CleanupResults.SupersededUpdatesDeclined)`r`n"
2710 $Script:WSUSServerCleanupWizardOutputTXT += "ExpiredUpdatesDeclined: $($CleanupResults.ExpiredUpdatesDeclined)`r`n"
2711 $Script:WSUSServerCleanupWizardOutputTXT += "ObsoleteUpdatesDeleted: $($CleanupResults.ObsoleteUpdatesDeleted)`r`n"
2712 $Script:WSUSServerCleanupWizardOutputTXT += "UpdatesCompressed: $($CleanupResults.UpdatesCompressed)`r`n"
2713 $Script:WSUSServerCleanupWizardOutputTXT += "ObsoleteComputersDeleted: $($CleanupResults.ObsoleteComputersDeleted)`r`n"
2714 $Script:WSUSServerCleanupWizardOutputTXT += "DiskSpaceFreed (MB): $([math]::round($CleanupResults.DiskSpaceFreed/1MB, 2))`r`n"
2715 $Script:WSUSServerCleanupWizardOutputTXT += "DiskSpaceFreed (GB): $([math]::round($CleanupResults.DiskSpaceFreed/1GB, 2))`r`n"
2716 #$Script:WSUSServerCleanupWizardOutputTXT += "Finished: $($FinishedRunning.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))`r`n"
2717 $Script:WSUSServerCleanupWizardOutputTXT += "WSUS Server Cleanup Wizard Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2718
2719 $Script:WSUSServerCleanupWizardOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUS Server Cleanup Wizard:</span></p>`r`n"
2720 #$Script:WSUSServerCleanupWizardOutputHTML += $CSSStyling + "`r`n"
2721 $Script:WSUSServerCleanupWizardOutputHTML += "<table class=`"gridtable`">`r`n"
2722 $Script:WSUSServerCleanupWizardOutputHTML += "<tbody>`r`n"
2723 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><th colspan=`"2`" rowspan=`"1`">$WSUSServer</th></tr>`r`n"
2724 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>Version:</td><td>$($WSUSServerAdminProxy.Version)</td></tr>`r`n"
2725 #$Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>Started:</td><td>$($DateNow.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))</td></tr>`r`n"
2726 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>SupersededUpdatesDeclined:</td><td>$($CleanupResults.SupersededUpdatesDeclined)</td></tr>`r`n"
2727 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>ExpiredUpdatesDeclined:</td><td>$($CleanupResults.ExpiredUpdatesDeclined)</td></tr>`r`n"
2728 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>ObsoleteUpdatesDeleted:</td><td>$($CleanupResults.ObsoleteUpdatesDeleted)</td></tr>`r`n"
2729 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>UpdatesCompressed:</td><td>$($CleanupResults.UpdatesCompressed)</td></tr>`r`n"
2730 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>ObsoleteComputersDeleted:</td><td>$($CleanupResults.ObsoleteComputersDeleted)</td></tr>`r`n"
2731 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>DiskSpaceFreed (MB):</td><td>$([math]::round($CleanupResults.DiskSpaceFreed/1MB, 2))</td></tr>`r`n"
2732 $Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>DiskSpaceFreed (GB):</td><td>$([math]::round($CleanupResults.DiskSpaceFreed/1GB, 2))</td></tr>`r`n"
2733 #$Script:WSUSServerCleanupWizardOutputHTML += "<tr><td>Finished:</td><td>$($FinishedRunning.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))</td></tr>`r`n"
2734 $Script:WSUSServerCleanupWizardOutputHTML += "<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})
2735 $Script:WSUSServerCleanupWizardOutputHTML += "</tbody>`r`n"
2736 $Script:WSUSServerCleanupWizardOutputHTML += "</table>`r`n"
2737
2738 # Variables Output
2739 # $WSUSServerCleanupWizardOutputTXT
2740 # $WSUSServerCleanupWizardOutputHTML
2741}
2742#endregion WSUSServerCleanupWizard Function
2743
2744#region ScriptDifferenceInTime Function
2745function ScriptDifferenceInTime {
2746 $ScriptFinishedRunning = Get-Date
2747 $Script:ScriptDifferenceInTime = New-TimeSpan -Start $ScriptTime -End $ScriptFinishedRunning
2748}
2749#endregion ScriptDifferenceInTime Function
2750
2751#region Create The CSS Styling
2752################################
2753# Create the CSS Styling #
2754################################
2755
2756$CSSStyling =@"
2757<style type="text/css">
2758#gridtable table, table.gridtable {
2759 font-family: verdana,arial,sans-serif;
2760 font-size: 11px;
2761 color: #333333;
2762 border-width: 1px;
2763 border-color: #666666;
2764 border-collapse: collapse;
2765}
2766#gridtable table th, table.gridtable th {
2767 border-width: 1px;
2768 padding: 8px;
2769 border-style: solid;
2770 border-color: #666666;
2771 background-color: #dedede;
2772}
2773#gridtable table td, table.gridtable td {
2774 border-width: 1px;
2775 padding: 8px;
2776 border-style: solid;
2777 border-color: #666666;
2778 background-color: #ffffff;
2779}
2780.TFtable{
2781 border-collapse:collapse;
2782}
2783.TFtable td{
2784 padding:7px;
2785 border:#4e95f4 1px solid;
2786}
2787
2788/* provide some minimal visual accommodation for IE8 and below */
2789.TFtable tr{
2790 background: #b8d1f3;
2791}
2792/* Define the background color for all the ODD background rows */
2793.TFtable tr:nth-child(odd){
2794 background: #b8d1f3;
2795}
2796/* Define the background color for all the EVEN background rows */
2797.TFtable tr:nth-child(even){
2798 background: #dae5f4;
2799}
2800.error {
2801border: 2px solid;
2802margin: 10px 10px;
2803padding: 15px 50px 15px 50px;
2804}
2805.error ol {
2806color: #D8000C;
2807}
2808.error ol li p {
2809color: #000;
2810background-color: transparent;
2811}
2812.error ol li {
2813background-color: #FFBABA;
2814margin: 10px 0;
2815}
2816</style>
2817"@
2818#endregion Create The CSS Styling
2819
2820#region Create The Output
2821################################
2822# Create the TXT output #
2823################################
2824
2825function CreateBodyTXT {
2826 $Script:BodyTXT = "`n"
2827 $Script:BodyTXT += $BodyHeaderTXT
2828 $Script:BodyTXT += $ConnectedTXT
2829 $Script:BodyTXT += $WSUSIndexOptimizationOutputTXT
2830 $Script:BodyTXT += $RemoveObsoleteUpdatesOutputTXT
2831 $Script:BodyTXT += $CompressUpdateRevisionsOutputTXT
2832 $Script:BodyTXT += $DeclineMultipleTypesOfUpdatesOutputTXT
2833 $Script:BodyTXT += $CleanUpWSUSSynchronizationLogsSQLOutputTXT
2834 $Script:BodyTXT += $RemoveWSUSDriversOutputTXT
2835 $Script:BodyTXT += $RemoveDeclinedWSUSUpdatesOutputTXT
2836 $Script:BodyTXT += $ComputerObjectCleanupOutputTXT
2837 $Script:BodyTXT += $WSUSDBMaintenanceOutputTXT
2838 $Script:BodyTXT += $WSUSServerCleanupWizardOutputTXT
2839 $Script:BodyTXT += $InstallTaskOutputTXT
2840 $Script:BodyTXT += $DirtyDatabaseOutputTXT
2841 $Script:BodyTXT += "`r`nClean-WSUS Script Duration: {0:00}:{1:00}:{2:00}:{3:00}`r`n`r`n" -f ($ScriptDifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2842 $Script:BodyTXT += $BodyFooterTXT
2843}
2844
2845################################
2846# Create the HTML output #
2847################################
2848
2849function CreateBodyHTML {
2850 $Script:BodyHTML = "`n"
2851 $Script:BodyHTML += $CSSStyling
2852 $Script:BodyHTML += $BodyHeaderHTML
2853 $Script:BodyHTML += $ConnectedHTML
2854 $Script:BodyHTML += $WSUSIndexOptimizationOutputHTML
2855 $Script:BodyHTML += $RemoveObsoleteUpdatesOutputHTML
2856 $Script:BodyHTML += $CompressUpdateRevisionsOutputHTML
2857 $Script:BodyHTML += $DeclineMultipleTypesOfUpdatesOutputHTML
2858 $Script:BodyHTML += $CleanUpWSUSSynchronizationLogsSQLOutputHTML
2859 $Script:BodyHTML += $RemoveWSUSDriversOutputHTML
2860 $Script:BodyHTML += $RemoveDeclinedWSUSUpdatesOutputHTML
2861 $Script:BodyHTML += $ComputerObjectCleanupOutputHTML
2862 $Script:BodyHTML += $WSUSDBMaintenanceOutputHTML
2863 $Script:BodyHTML += $WSUSServerCleanupWizardOutputHTML
2864 $Script:BodyHTML += $InstallTaskOutputHTML
2865 $Script:BodyHTML += $DirtyDatabaseOutputHTML
2866 $Script:BodyHTML += "<p>Clean-WSUS Script Duration: {0:00}:{1:00}:{2:00}:{3:00}</p>`r`n" -f ($ScriptDifferenceInTime | % {$_.Days, $_.Hours, $_.Minutes, $_.Seconds})
2867 $Script:BodyHTML += $BodyFooterHTML
2868}
2869#endregion Create The Output
2870
2871#region SaveReport
2872################################
2873# Save the Report #
2874################################
2875
2876function SaveReport {
2877 Param(
2878 [ValidateSet("TXT","HTML")]
2879 [String]$ReportType = "TXT"
2880 )
2881 if ($ReportType -eq "HTML") {
2882 $BodyHTML | Out-File -FilePath "$ScriptPath\$(get-date -f "yyyy.MM.dd-HH.mm.ss").htm"
2883 } else {
2884 $BodyTXT | Out-File -FilePath "$ScriptPath\$(get-date -f "yyyy.MM.dd-HH.mm.ss").txt"
2885 }
2886}
2887#endregion SaveReport
2888
2889#region MailReport
2890################################
2891# Mail the Report #
2892################################
2893
2894function MailReport {
2895 param (
2896 [ValidateSet("TXT","HTML")]
2897 [String] $MessageContentType = "HTML"
2898 )
2899 $message = New-Object System.Net.Mail.MailMessage
2900 $mailer = New-Object System.Net.Mail.SmtpClient ($MailReportSMTPServer, $MailReportSMTPPort)
2901 $mailer.EnableSSL = $MailReportSMTPServerEnableSSL
2902 if ($MailReportSMTPServerUsername -ne "") {
2903 $mailer.Credentials = New-Object System.Net.NetworkCredential($MailReportSMTPServerUsername, $MailReportSMTPServerPassword)
2904 }
2905 $message.From = $MailReportEmailFromAddress
2906 $message.To.Add($MailReportEmailToAddress)
2907 $message.Subject = $MailReportEmailSubject
2908 $message.Body = if ($MessageContentType -eq "HTML") { $BodyHTML } else { $BodyTXT }
2909 $message.IsBodyHtml = if ($MessageContentType -eq "HTML") { $True } else { $False }
2910 $mailer.send(($message))
2911}
2912#endregion MailReport
2913
2914#region HelpMe
2915################################
2916# Help Me #
2917################################
2918
2919function HelpMe {
2920 ((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()
2921 Write-Output "PowerShell Version: $($PSVersionTable.PSVersion.ToString())"
2922 Write-Output "WSUS Version: $($WSUSServerAdminProxy.Version)"
2923 Write-Output "Replica Server: $($WSUSServerAdminProxy.GetConfiguration().IsReplicaServer)"
2924 Write-Output "The path to the WSUS Content folder is: $($WSUSServerAdminProxy.GetConfiguration().LocalContentCachePath)"
2925 Write-Output "Free Space on the WSUS Content folder Volume is: $((Get-DiskFree -Format | ? { $_.Type -like '*fixed*' } | Where-Object { ($_.Vol -eq ($WSUSServerAdminProxy.GetConfiguration().LocalContentCachePath).split("\")[0]) }).Avail)"
2926 Write-Output "All Volumes on the WSUS Server:"
2927 (Get-DiskFree -Format | Out-String).Trim()
2928 Write-Output ".NET Installed Versions"
2929 (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()
2930 Write-Output "============================="
2931 Write-Output "All My Functions"
2932 Write-Output "============================="
2933 Show-MyFunctions
2934 Write-Output "============================="
2935 Write-Output "All My Variables"
2936 Write-Output "============================="
2937 Show-MyVariables
2938 Write-Output "============================="
2939 Write-Output " End of HelpMe Stream"
2940 Write-Output "============================="
2941
2942}
2943#endregion HelpMe
2944
2945#region Process The Functions
2946################################
2947# Process the Functions #
2948################################
2949
2950if ($FirstRun -eq $True) {
2951 CreateHeader
2952 Write-Output "Executing WSUSIndexOptimization"; WSUSIndexOptimization
2953 if ($RemoveWSUSDriversInFirstRun -eq $True) { Write-Output "Executing RemoveWSUSDrivers"; RemoveWSUSDrivers -SQL }
2954 Write-Output "Executing RemoveObsoleteUpdates"; RemoveObsoleteUpdates
2955 Write-Output "Executing CompressUpdateRevisions"; CompressUpdateRevisions
2956 Write-Output "Executing DeclineMultipleTypesOfUpdates"; if ($WSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineMultipleTypesOfUpdates -Force } else { Write-Output "This WSUS Server is a Replica Server. You can't decline updates from a replica server. Skipping this stream." }
2957 Write-Output "Executing CleanUpWSUSSynchronizationLogs"; if ($CleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $CleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $CleanUpWSUSSynchronizationLogsConsistencyTime }
2958 if ($ComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup"; ComputerObjectCleanup }
2959 Write-Output "Executing WSUSDBMaintenance"; WSUSDBMaintenance
2960 Write-Output "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard
2961 Write-Output "Executing Install-Task"; Install-Task;
2962 CreateFooter
2963 ScriptDifferenceInTime
2964 CreateBodyTXT
2965 CreateBodyHTML
2966 if ($MailReport -eq $True) { MailReport $MailReportType }
2967 SaveReport
2968
2969}
2970if ($MonthlyRun -eq $True) {
2971 CreateHeader
2972 Write-Output "Executing RemoveObsoleteUpdates"; RemoveObsoleteUpdates
2973 Write-Output "Executing CompressUpdateRevisions"; CompressUpdateRevisions
2974 Write-Output "Executing DeclineMultipleTypesOfUpdates"; if ($WSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineMultipleTypesOfUpdates -Force } else { Write-Output "This WSUS Server is a Replica Server. You can't decline updates from a replica server. Skipping this stream." }
2975 Write-Output "Executing CleanUpWSUSSynchronizationLogs"; if ($CleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $CleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $CleanUpWSUSSynchronizationLogsConsistencyTime }
2976 if ($ComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup"; ComputerObjectCleanup }
2977 Write-Output "Executing WSUSDBMaintenance"; WSUSDBMaintenance
2978 Write-Output "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard
2979 CreateFooter
2980 ScriptDifferenceInTime
2981 CreateBodyTXT
2982 CreateBodyHTML
2983 if ($MailReport -eq $True) { MailReport $MailReportType }
2984 if ($SaveReport -eq $True) { SaveReport $SaveReportType }
2985}
2986if ($QuarterlyRun -eq $True) {
2987 CreateHeader
2988 Write-Output "Executing RemoveObsoleteUpdates"; RemoveObsoleteUpdates
2989 Write-Output "Executing CompressUpdateRevisions"; CompressUpdateRevisions
2990 Write-Output "Executing DeclineMultipleTypesOfUpdates"; if ($WSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineMultipleTypesOfUpdates -Force } else { Write-Output "This WSUS Server is a Replica Server. You can't decline updates from a replica server. Skipping this stream." }
2991 Write-Output "Executing CleanUpWSUSSynchronizationLogs"; if ($CleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $CleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $CleanUpWSUSSynchronizationLogsConsistencyTime }
2992 if ($RemoveWSUSDriversInRoutines -eq $True) { Write-Output "Executing RemoveWSUSDrivers"; RemoveWSUSDrivers }
2993 Write-Output "Executing RemoveDeclinedWSUSUpdates"; RemoveDeclinedWSUSUpdates -Display -Proceed
2994 if ($ComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup"; ComputerObjectCleanup }
2995 Write-Output "Executing WSUSDBMaintenance"; WSUSDBMaintenance
2996 Write-Output "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard
2997 CreateFooter
2998 ScriptDifferenceInTime
2999 CreateBodyTXT
3000 CreateBodyHTML
3001 if ($MailReport -eq $True) { MailReport $MailReportType }
3002 if ($SaveReport -eq $True) { SaveReport $SaveReportType }
3003}
3004if ($ScheduledRun -eq $True) {
3005 $DateNow = Get-Date
3006 CreateHeader
3007 if ($ScheduledRunStreamsDay -gt 31 -or $ScheduledRunStreamsDay -eq 0) { Write-Output 'You failed to set a valid value for $ScheduledRunStreamsDay. Setting to 31'; $ScheduledRunStreamsDay = 31 }
3008 if ($ScheduledRunStreamsDay -eq $DateNow.Day) { Write-Output "Executing RemoveObsoleteUpdates"; RemoveObsoleteUpdates }
3009 if ($ScheduledRunStreamsDay -eq $DateNow.Day) { Write-Output "Executing CompressUpdateRevisions"; CompressUpdateRevisions }
3010 Write-Output "Executing DeclineMultipleTypesOfUpdates"; if ($WSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineMultipleTypesOfUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
3011 Write-Output "Executing CleanUpWSUSSynchronizationLogs"; if ($CleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $CleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $CleanUpWSUSSynchronizationLogsConsistencyTime }
3012 $ScheduledRunQuarterlyMonths.Split(",") | ForEach-Object {
3013 if ($_ -eq $DateNow.Month) {
3014 if ($_ -eq 2) {
3015 if ($ScheduledRunStreamsDay -gt 28 -and [System.DateTime]::isleapyear($DateNow.Year) -eq $True) { $ScheduledRunStreamsDay = 29 }
3016 else { $ScheduledRunStreamsDay = 28 }
3017 }
3018 if (4,6,9,11 -contains $_ -and $ScheduledRunStreamsDay -gt 30) { $ScheduledRunStreamsDay = 30 }
3019 if ($ScheduledRunStreamsDay -eq $DateNow.Day) {
3020 if ($RemoveWSUSDriversInRoutines -eq $True) { Write-Output "Executing RemoveWSUSDrivers"; RemoveWSUSDrivers }
3021 Write-Output "Executing RemoveDeclinedWSUSUpdates"; RemoveDeclinedWSUSUpdates -Display -Proceed
3022 }
3023 }
3024 }
3025 if ($ComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup"; ComputerObjectCleanup }
3026 Write-Output "Executing WSUSDBMaintenance"; if ($ScheduledRunStreamsDay -eq $DateNow.Day) { WSUSDBMaintenance } else { WSUSDBMaintenance -NoOutput }
3027 Write-Output "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard
3028 CreateFooter
3029 ScriptDifferenceInTime
3030 CreateBodyTXT
3031 CreateBodyHTML
3032 if ($MailReport -eq $True) { MailReport $MailReportType }
3033 if ($SaveReport -eq $True) { SaveReport $SaveReportType }
3034}
3035if ($DailyRun -eq $True) {
3036 CreateHeader
3037 Write-Output "Executing DeclineMultipleTypesOfUpdates"; if ($WSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineMultipleTypesOfUpdates } else { Write-Output "This WSUS Server is a Replica Server. You can't decline updates from a replica server. Skipping this stream." }
3038 Write-Output "Executing CleanUpWSUSSynchronizationLogs"; if ($CleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $CleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $CleanUpWSUSSynchronizationLogsConsistencyTime }
3039 if ($ComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup"; ComputerObjectCleanup }
3040 Write-Output "Executing WSUSDBMaintenance"; WSUSDBMaintenance
3041 Write-Output "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard
3042 CreateFooter
3043 ScriptDifferenceInTime
3044 CreateBodyTXT
3045 CreateBodyHTML
3046 if ($MailReport -eq $True) { MailReport $MailReportType }
3047 if ($SaveReport -eq $True) { SaveReport $SaveReportType }
3048}
3049if (-not $FirstRun -and -not $MonthlyRun -and -not $QuarterlyRun -and -not $ScheduledRun -and -not $DailyRun) {
3050 Write-Verbose "All pre-defined routines (-FirstRun, -DailyRun, -MonthlyRun, -QuarterlyRun, -ScheduledRun) were not specified"
3051 CreateHeader
3052 if ($WSUSIndexOptimization -eq $True) { Write-Output "Executing WSUSIndexOptimization"; WSUSIndexOptimization }
3053 if ($RemoveWSUSDriversSQL -eq $True) { Write-Output "Executing RemoveWSUSDrivers using SQL"; RemoveWSUSDrivers -SQL }
3054 if ($RemoveWSUSDriversPS -eq $True) { Write-Output "Executing RemoveWSUSDrivers using PowerShell"; RemoveWSUSDrivers }
3055 if ($RemoveObsoleteUpdates -eq $True) { Write-Output "Executing RemoveObsoleteUpdates using SQL"; if ($WSUSServerAdminProxy.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." } }
3056 if ($CompressUpdateRevisions -eq $True) { Write-Output "Executing CompressUpdateRevisions using SQL"; if ($WSUSServerAdminProxy.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." } }
3057 if ($DeclineMultipleTypesOfUpdates -eq $True) { Write-Output "Executing DeclineMultipleTypesOfUpdates"; if ($WSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineMultipleTypesOfUpdates -Force } else { Write-Output "This WSUS Server is a Replica Server. You can't decline updates from a replica server. Skipping this stream." } }
3058 if ($CleanUpWSUSSynchronizationLogs -eq $True) { Write-Output "Executing CleanUpWSUSSynchronizationLogs"; if ($CleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $CleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $CleanUpWSUSSynchronizationLogsConsistencyTime } }
3059 if ($RemoveDeclinedWSUSUpdates -eq $True) { Write-Output "Executing RemoveDeclinedWSUSUpdates"; RemoveDeclinedWSUSUpdates -Display -Proceed }
3060 if ($ComputerObjectCleanup -eq $True -and $ComputerObjectCleanup -eq $True) { Write-Output "Executing ComputerObjectCleanup"; ComputerObjectCleanup }
3061 if ($WSUSDBMaintenance -eq $True) { Write-Output "Executing WSUSDBMaintenance"; WSUSDBMaintenance }
3062 if ($DirtyDatabaseCheck) { Write-Output "Executing DirtyDatabaseCheck"; DirtyDatabaseCheck }
3063 if ($WSUSServerCleanupWizard -eq $True) { Write-Output "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard }
3064 CreateFooter
3065 ScriptDifferenceInTime
3066 CreateBodyTXT
3067 CreateBodyHTML
3068 if ($SaveReport -eq "TXT") { SaveReport }
3069 if ($SaveReport -eq "HTML") { SaveReport -ReportType "HTML" }
3070 if ($MailReport -eq "HTML") { MailReport }
3071 if ($MailReport -eq "TXT") { MailReport -MessageContentType "TXT" }
3072}
3073
3074if ($HelpMe -eq $True) {
3075 HelpMe
3076}
3077if ($DisplayApplicationPoolMemory -eq $True) {
3078 ApplicationPoolMemory
3079}
3080Write-Verbose "Just before setting the application memory `$SetApplicationPoolMemory is $SetApplicationPoolMemory"
3081if ($SetApplicationPoolMemory -ne '-1') {
3082 ApplicationPoolMemory -Set $SetApplicationPoolMemory
3083}
3084
3085if ($InstallTask -eq $True) {
3086 Install-Task
3087}
3088#endregion ProcessTheFunctions
3089}
3090
3091End {
3092 if ($HelpMe -eq $True) { $VerbosePreference = $OldVerbose; Stop-Transcript }
3093 Write-Verbose "End Of Code"
3094}
3095################################
3096# End Of Code #
3097################################
3098#EOF