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