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