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