· 9 years ago · May 07, 2017, 08:52 AM
1#Requires –Version 3.0
2################################
3# Adamj Clean-WSUS #
4# Version 2.07 #
5# #
6# The last WSUS Script you #
7# will ever need! #
8# #
9# Taken from various sources #
10# from the Internet. #
11# #
12# Modified By: Adam Marshall #
13# http://www.adamj.org #
14################################
15
16<#
17################################
18# Prerequisites #
19################################
20
211. This script has to be saved as plain text in ANSI format. If you use Notepad++, you must
22 change the encoding to ANSI (Encoding > 'Encode in ANSI' or Encode > 'Convert to ANSI').
23 An easy way to tell if it is saved in plain text (ANSI) format is that there is a #Requires
24 statement at the top of the script. Make sure that there is a hyphen before the word
25 "Version" and you shouldn't have a problem with executing it. If you end up with an error
26 like below, it is due to the encoding of the file as you can tell by the – characters
27 before the word Version.
28
29 At C:\Scripts\Clean-WSUS.ps1:1 char:13
30 + #Requires –Version 3.0
31
322. You must run this on the WSUS Server itself and any downstream WSUS servers you may have.
33
343. On the WSUS Server, you must install the SQL Server Management Studio (SSMS) from Microsoft
35 so that you have the SQLCMD utility.
36
374. You must have Powershell 3.0 or higher installed. I recommend version 4.0 or higher.
38
39 - For Server 2008 SP2:
40 - Install Windows Powershell from Server Manager - Features
41 - Install .NET 3.5 SP1 from - https://www.microsoft.com/en-ca/download/details.aspx?id=25150
42 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
43 You want to choose SQLManagementStudio_x64_ENU.exe
44 - Install .NET 4.0 - https://www.microsoft.com/en-us/download/details.aspx?id=17718
45 - Install Powershell 2.0 & WinRM 2.0 from https://www.microsoft.com/en-ca/download/details.aspx?id=20430
46 - Install Windows Management Framework 3.0 from https://www.microsoft.com/en-us/download/confirmation.aspx?id=34595
47
48 - For Server 2008 R2:
49 - Install .NET 4.5.2 from https://www.microsoft.com/en-ca/download/details.aspx?id=42642
50 - Install Windows Management Framework 4.0 and reboot from https://www.microsoft.com/en-ca/download/details.aspx?id=40855
51 - Install SQL Server Management Studio from https://www.microsoft.com/en-ca/download/details.aspx?id=30438
52 You want to choose SQLManagementStudio_x64_ENU.exe
53
54 - For Server 2012 & 2012 R2
55 - Install SQL Server Management Studio from https://www.microsoft.com/en-us/download/details.aspx?id=29062
56 You want to choose the ENU\x64\SQLManagementStudio_x64_ENU.exe
57
58################################
59# Instructions #
60################################
61
62
63 1. Edit the variables below to match your environment.
64 2. Open PowerShell using "Run As Administrator" on the WSUS Server.
65 3. Because you downloaded this script from the internet, you cannot initially run it directly
66 as the ExecutionPolicy is default set to "Restricted" (Server 2008, Server 2008 R2, and
67 Server 2012) or "RemoteSigned" (Server 2012 R2). You must change your ExecutionPolicy to
68 Bypass. You can do this with Set-ExecutionPolicy, however that will change it globally for
69 the server, which is not recommended. Instead, launch another PowerShell.exe with the
70 ExecutionPolicy set to bypass for just that session. At your current PowerShell prompt,
71 type in: "PowerShell.exe -ExecutionPolicy Bypass" and press enter.
72 3. Run the script using -FirstRun. (e.g. ".\Clean-WSUS.ps1 -FirstRun")
73 4. Create a Scheduled Task to run Clean-WSUS.ps1 -ScheduledRun, daily at the time you wish.
74 I recommend running it at 8:00 AM.
75
76To Create a Scheduled Task:
77
78 1. Open Task Scheduler and Create a new task (not a basic task)
79 2. Go to the General Tab:
80 3. Name: "Adamj Clean-WSUS"
81 4. Under the section "Security Options" put the dot in "Run whether the user is logged on or not"
82 5. Check "Do not store password. The task will only have access to local computer resources"
83 6. Check "Run with highest privileges."
84 7. Under the section "Configure for" - Choose the OS of the Server (e.g. Server 2012 R2)
85 8. Go to the Triggers Tab:
86 9. Click New at the bottom left.
8710. Under the section "Settings"
8811. Choose Daily. Pick a time to run (e.g. 8:00 AM)
8912. Confirm Enabled is checked, Press OK.
9013. Go to the Actions Tab:
9114. Click New at the bottom left.
9215. Action should be "Start a program"
9316. The "Program/script" should be set to the path to PowerShell on that server.
94 (e.g: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe)
9517. The arguments line should have "-ExecutionPolicy Bypass " and then the path to this script and
96 the -ScheduledRun switch.
97 (e.g: -ExecutionPolicy Bypass "C:\Scripts\Clean-WSUS.ps1 -ScheduledRun")
9818. Go to the Settings Tab:
9919. Check "Allow task to be run on demand"
10020. Click OK
101#>
102
103# You can use Get-Help .\Clean-WSUS for more information.
104
105<#
106.SYNOPSIS
107This is the last WSUS Script you will ever need. It has the capacity to remove all drivers from the database, remove declined updates, decline superseded updates, run the SQL database maintenance, remove synchronization logs, and finally run the server cleanup wizard.
108
109
110.DESCRIPTION
111################################
112# Background Information #
113# on Streams #
114################################
115
116All my recommendations are set in -ScheduledRun.
117
118Adamj Remove WSUS Drivers Stream
119-----------------------------------------------------
120
121This stream will remove all WSUS Drivers Classifications from the WSUS database.
122This has 2 possible running methods - Run through PowerShell, or Run directly in SQL.
123The -FirstRun Switch will force the SQL method, but all other automatic runs will use the
124PowerShell method. I recommend this be done every quarter.
125
126You can use -RemoveWSUSDriversSQL or -RemoveWSUSDriversPS to run these manually from the command-line.
127
128Adamj Remove Declined WSUS Updates Stream
129-----------------------------------------------------
130
131This stream will remove any Declined WSUS updates from the WSUS Database. This is good if you are removing
132Specific products (Like Server 2003 / Windows XP updates) from the WSUS server under the Products and
133Classifications section. Since this will remove them from the database, if they are still valid,
134the next synchronizations will pick up the updates again. I recommend that this be done every quarter.
135
136You can use -RemoveDeclinedWSUSUpdates to run this manually from the command-line.
137
138Adamj WSUS Database Maintenance Stream
139-----------------------------------------------------
140
141This stream will perform basic maintenance tasks on SUSDB, the WSUS Database. It will identify indexes
142that are fragmented and defragment them. For certain tables, a fill-factor is set in order to improve
143insert performance. It will then update potentially out-of-date table statistics. I recommend that this
144be done daily.
145
146You can use -WSUSDBMaintenance to run this manually from the command-line.
147
148Adamj Decline Superseded Updates Stream
149-----------------------------------------------------
150
151This stream will decline any update that is superseded and not yet declined. This is a BONUS cleanup for
152shrinking down the size of your WSUS Server. Any update that has been superseded but has not been declined
153is using extra space. This will save you GB of data in your WsusContent folder. I recommend that this be
154done every month.
155
156You can use -DeclineSupersededUpdates to run this manually from the command-line.
157
158### Please read the background information below for more details. ###
159
160The Server Cleanup Wizard (SCW) declines superseded updates, only if:
161
162 The newest update is approved, and
163 The superseded updates are Not Approved, and
164 The superseded update has not been reported as NotInstalled (i.e. Needed) by any computer in the previous 30 days.
165
166There is no feature in the product to automatically decline superseded updates on approval of the newer update,
167and in fact, you really do not want that feature. The "Best Practice" in dealing with this situation is:
168
1691. Approve the newer update.
1702. Verify that all systems have installed the newer update.
1713. Verify that all systems now report the superseded update as Not Applicable.
1724. THEN it is safe to decline the superseded update.
173
174To SEARCH for superseded updates, you need only enable the Superseded flag column in the All Updates view, and sort on that column.
175
176There will be four groups:
177
1781. Updates which have never been superseded (blank icon).
1792. Updates which have been superseded, but have never superseded another update (icon with blue square at bottom).
1803. Updates which have been superseded and have superseded another update (icon with blue square in middle).
1814. Updates which have superseded another update (icon with blue square at top).
182
183There's no way to filter based on the approval status of the updates in group #4, but if you've verified that all
184necessary/applicable updates in group #4 are approved and installed, then you'd be free to decline groups #2 and #3 en masse.
185
186If you decline superseded updates using the method described:
187
1881. Approve the newer update.
1892. Verify that all systems have installed the newer update.
1903. Verify that all systems now report the superseded update as Not Applicable.
1914. THEN it is safe to decline the superseded update.
192
193### THIS SCRIPT DOES NOT FOLLOW THE ABOVE GUIDELINES. IT WILL JUST DECLINE ANY SUPERSEDED UPDATES. ###
194
195Adamj Clean Up WSUS Synchronization Logs Stream
196-----------------------------------------------------
197
198This stream will remove all synchronization logs beyond a specified time period. WSUS is lacking the ability
199to remove synchronization logs through the GUI. Your WSUS server will become slower and slower loading up
200the synchronization logs view as the synchronization logs will just keep piling up over time. If you have
201your synchronization settings set to synchronize 4 times a day, it would take less than 3 months before you
202have over 300 logs that it has to load for the view. This is very time consuming and many just ignore this
203view and rarely go to it. When they accidentally click on it, they curse. I recommend that this be done daily.
204
205You can use -CleanUpWSUSSynchronizationLogs to run this manually from the command-line.
206
207
208Adamj Server Cleanup Wizard Stream
209-----------------------------------------------------
210
211The Server Cleanup Wizard (SCW) is integrated into the WSUS GUI, and can be used to help you manage your
212disk space. This runs the SCW through PowerShell which has the added bonus of not timing out as often
213the SCW GUI would.
214
215This wizard can do the following things:
216 - Remove unused updates and update revisions
217 The wizard will remove all older updates and update revisions that have not been approved.
218
219 - Delete computers not contacting the server
220 The wizard will delete all client computers that have not contacted the server in thirty days or more.
221
222 - Delete unneeded update files
223 The wizard will delete all update files that are not needed by updates or by downstream servers.
224
225 - Decline expired updates
226 The wizard will decline all updates that have been expired by Microsoft.
227
228 - Decline superseded updates
229 The wizard will decline all updates that meet all the following criteria:
230 The superseded update is not mandatory
231 The superseded update has been on the server for thirty days or more
232 The superseded update is not currently reported as needed by any client
233 The superseded update has not been explicitly deployed to a computer group for ninety days or more
234 The superseding update must be approved for install to a computer group
235
236I recommend that this be done daily.
237
238You can use -WSUSServerCleanupWizard to run this manually from the command-line.
239
240.NOTES
241Name: Clean-WSUS
242Author: Adam Marshall
243Website: http://www.adamj.org
244
245This script has been tested on Server 2008 SP2, Server 2008 R2, Server 2012, and Server 2012 R2.
246
247################################
248# Version History & #
249# Release Notes #
250################################
251
252 Version 1.0 to 1.1
253 - Added "Diskspace Freed (GB)" from example code https://p0w3rsh3ll.wordpress.com/2015/01/26/cleanup-wsus/
254 - Re-formatted output of the email.
255
256 Version 1.1 to 1.2
257 - Created the Adamj Bonus Cleanup Stream. Changed the output email into HTML and formatted the output nicely.
258 - Thanks to Rob Dunn from the Spiceworks forums for reviewing the code and making some suggestions on the
259 code to help enumerate information better.
260
261 Version 1.2 to 1.3
262 - Added the $AdamjBonusCleanupUpdatesCount variable to the output line on the declining updates message, so that
263 a copy/paste can easily be done that shows how many updates were declined when it actually declines them.
264
265 Version 1.3 to 1.5
266 - Added the Adamj Remove Drivers Stream, Adamj Remove Declined WSUS Updates Stream, and the Adamj WSUS DB
267 Maintenance Stream. Renamed the Adamj Bonus Cleanup Stream to Adamj Decline Superseded Updates Stream.
268 - Changed the formulation of the script into modular form. Now the script has 5 modular streams:
269 1. WSUS Server Cleanup Wizard Stream
270 2. Adamj Remove WSUS Drivers Stream
271 3. Adamj Remove Declined WSUS Updates Stream
272 4. Adamj WSUS 3.0 DB Maintenance Stream
273 5. Adamj Decline Superseded Updates Stream.
274
275 Version 1.5 to 2.01
276 - Changed the mail function to allow for authentication, SSL, and SMTP Port
277 - Cleaned up the 'Clean Up Variables' section to account for all Adamj* Variables
278 - Added Clean Up WSUS Synchronization Logs Stream to keep the Sync logs clean
279 - Added SQL Auto-detect with function help from
280 (http://stackoverflow.com/questions/11540445/how-to-verify-whether-the-sql-server-instance-is-correct-or-not-using-powershell)
281 - Redesigned pretty much the entire script into a function driven script.
282 - Added cmdlet options for running each section manually, or by way of the recommended time periods (Daily, Monthly, Quarterly)
283 - Added error checking and throwing better error messages on various things.
284 - Added Test-Administrator to confirm it is being run with elevated rights.
285 - Updated the Prerequisites and Instructions sections.
286 - Updated the output of the SCW.
287
288 Version 2.00 to 2.01
289 - Troubleshooting help by Malil from Spiceworks forums which lead to 2 small but significant code changes
290 for the auto-detection of the $AdamjSQLServer variable, provided by Malil.
291 - Fixed the SCW to also account for hours run, rather than just minutes and seconds.
292
293 Version 2.01 to 2.02
294 - Malil helped troubleshooting further and I made code changes for $AdamjSQLServer on Server 2008.
295 - Added better email logs for the RemoveWSUSDriversSQL Stream. Added console messages to show that the script is still working
296 and what it is doing as this stream will take time. Added console error message summaries, and file/email error message
297 details.
298 - Changed Quarterly months defaults to 1, 4, 7, 10.
299
300 Version 2.02 to 2.03
301 - Properly formatted running time duration to hh:mm:ss
302 - Added calculated duration of each stream, and total script duration.
303 - Removed a duplicated value in the setup configuration
304
305 Version 2.03 to 2.04
306 - Fixed issue with SQL Connection command for issues with connecting to a Windows Server 2008 Internal Database.
307 - Fixed some visual issues with the TXT output relating to the duration..
308 - Clarified some instructional text at the top.
309
310 Version 2.04 to 2.05
311 - Tested on Server 2008 SP2, Server 2008 R2, Server 2012, and Server 2012 R2.
312 - Came up with installation instructions for prerequisites.
313 - Added code for detecting last day of the month if $AdamjScheduledRunStreamsDay
314 is greater than the last day of the month, including leap years for months less than 31 days.
315 - Thank you to Malil for the initial base of the code for checking for $AdamjScheduledRunStreamsDay
316
317 Version 2.05 to 2.06
318 - Added notes regarding a Remote SQL connection such that you must use the computer account to run the script
319 via Schedule Tasks.
320 - Added a -HelpMe Stream for getting troubleshooting data for support reasons.
321 - Check for for replica server and exclude only the DeclineSupersededUpdates as you can only decline superseded updates
322 from the upstream server. Thanks to Jurriaan van Doornik for the help with testing and investigating.
323 - Adjusted the Timeout on the SQL-Ping Function to 60 seconds only if you specify the $AdamjSQLServer variable.
324 - Altered the Versioning notes layout for clarity and separation.
325 - Added a prerequisite of ANSI encoding for troubleshooting.
326 - Adjusted some of the descriptions of the streams information text to be more clear and consistent.
327 - Spelling and grammar fixes.
328
329 Version 2.06 to 2.07
330 - Adjusted the prerequisites wording for clarity regarding the ANSI encoding.
331
332.EXAMPLE
333Clean-WSUS -FirstRun
334Description: Run the routines that are recommended for running this script for the first time.
335.EXAMPLE
336Clean-WSUS -HelpMe
337Description: Run the troubleshooting HelpMe stream to copy and paste from the console window for getting support.
338.EXAMPLE
339Clean-WSUS -DailyRun
340Description: Run the recommended daily routines.
341.EXAMPLE
342Clean-WSUS -MonthlyRun
343Description: Run the recommended monthly routines.
344.EXAMPLE
345Clean-WSUS -QuarterlyRun
346Description: Run the recommended quarterly routines.
347.EXAMPLE
348Clean-WSUS -ScheduledRun
349Description: Run the recommended routines on a schedule having the script take care of all timetables.
350.EXAMPLE
351Clean-WSUS -RemoveWSUSDriversSQL -SaveReport "TXT"
352Description: Only Remove WSUS Drivers by way of SQL and save the output as TXT to the script's folder
353 named with the date and time of execution.
354.EXAMPLE
355Clean-WSUS -RemoveWSUSDriversPS -MailReport "HTML"
356Description: Only Remove WSUS Drivers by way of PowerShell and email the output as HTML to the
357 configured parties.
358.EXAMPLE
359Clean-WSUS -RemoveDeclinedWSUSUpdates -CleanUpWSUSSynchronizationLogs -WSUSDBMaintenance -WSUSServerCleanupWizard -SaveReport "HTML" -MailReport "TXT"
360Description: Remove Declined WSUS Updates, Clean Up WSUS Synchronization Logs based on the configuration
361 variables, Run the SQL Maintenance, and run the Server Cleanup Wizard (SCW) and output to
362 an HTML file in the scripts folder named with the date and time of execution, and then
363 email the report in plain text to the configured parties.
364.EXAMPLE
365Clean-WSUS -DeclineSupersededUpdates -SaveReport "TXT" -MailReport "HTML"
366Description: Decline superseded updates, save the output as TXT to the script's folder, and email the
367 output as HTML to the configured parties.
368
369.LINK
370http://www.adamj.org
371http://community.spiceworks.com/scripts/show/2998-adamj-wsus-cleanup
372
373#>
374
375################################
376# Script Setup Parameters #
377# #
378# DO NOT EDIT!!! SCROLL DOWN #
379# TO FIND THE VARIABLES #
380# TO EDIT #
381################################
382
383[cmdletbinding()]
384param (
385 # Run the recommended daily routines.
386 [Switch]$DailyRun,
387 # Run the routines that are recommended for running this script for the first time.
388 [Switch]$FirstRun,
389 # Run the recommended monthly routines.
390 [Switch]$MonthlyRun,
391 # Run the recommended quarterly routines.
392 [Switch]$QuarterlyRun,
393 # Run the recommended routines on a schedule having the script take care of all timetables.
394 [Switch]$ScheduledRun,
395 # Remove WSUS Drivers by way of SQL.
396 [Switch]$RemoveWSUSDriversSQL,
397 # Remove WSUS Drivers by way of PowerShell.
398 [Switch]$RemoveWSUSDriversPS,
399 # Remove Declined WSUS Updates.
400 [Switch]$RemoveDeclinedWSUSUpdates,
401 # Run the SQL Maintenance.
402 [Switch]$WSUSDBMaintenance,
403 # Decline Superseded Updates.
404 [Switch]$DeclineSupersededUpdates,
405 # Clean Up WSUS Synchronization Logs based on the configuration variables.
406 [Switch]$CleanUpWSUSSynchronizationLogs,
407 # Run the Server Cleanup Wizard (SCW) through PowerShell rather than through a GUI.
408 [Switch]$WSUSServerCleanupWizard,
409 # 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.
410 [String]$SaveReport,
411 # Email the output report to an email address based on the configuration variables. TXT or HTML are valid output types.
412 [String]$MailReport,
413 # Run the troubleshooting HelpMe stream to copy and paste for getting support.
414 [Switch]$HelpMe
415 )
416if (($DailyRun -eq $False -and $FirstRun -eq $False -and $MonthlyRun -eq $False -and $QuarterlyRun -eq $False -and $ScheduledRun -eq $False -and $HelpMe -eq $False) -and ($SaveReport -eq '' -and $MailReport -eq '')) { Throw "You must use -SaveReport or -MailReport if you are not going to use -FirstRun, -DailyRun, -MonthlyRun, -QuarterlyRun, or -ScheduledRun" }
417if ($SaveReport -ne '' -and ($SaveReport -NotIn "HTML","TXT")) { Throw "You must use either HTML or TXT with -SaveReport. You set it as: $SaveReport" }
418if ($MailReport -ne '' -and ($MailReport -NotIn "HTML","TXT")) { Throw "You must use either HTML or TXT with -MailReport. You set it as: $SaveReport" }
419
420################################
421# WSUS Setup Variables #
422################################
423
424# Enter your FQDN of the WSUS server. Example: "server.domain.local"
425[string]$AdamjWSUSServer = "server.domain.local"
426
427# Use secure connection: $True or $False
428[boolean]$AdamjWSUSServerUseSecureConnection = $False
429
430# What port number are you using for WSUS? Example: "80" or "443" if on Server 2008 or "8530" or "8531" if on Server 2012+
431[int32]$AdamjWSUSServerPortNumber = "8530"
432
433################################
434# Mail Report Setup Variables #
435################################
436
437# From: address for email notifications (it doesn't have to be a real email address). Example: "WSUS@domain.com"
438[string]$AdamjMailReportEmailFromAddress = "WSUS@domain.com"
439
440# To: address for email notifications. Example: "firstname.lastname@domain.com"
441[string]$AdamjMailReportEmailToAddress = "firstname.lastname@domain.com"
442
443# Subject: of the results email
444[string]$AdamjMailReportEmailSubject = "WSUS Cleanup Results"
445
446# Enter your SMTP server name. Example: "mailserver.domain.local" or "mail.domain.com" or "smtp.gmail.com"
447[string]$AdamjMailReportSMTPServer = "mailserver.domain.local"
448
449# Enter your SMTP port number. Example: "25" or "465" (Usually for SSL) or "587" or "1025"
450[int32]$AdamjMailReportSMTPPort = "25"
451
452# Do you want to enable SSL communication for your SMTP Server
453[boolean]$AdamjMailReportSMTPServerEnableSSL = $False
454
455# Do you need to authenticate to the server? If not, leave blank.
456[string]$AdamjMailReportSMTPServerUsername = ""
457[string]$AdamjMailReportSMTPServerPassword = ""
458
459################################
460# WSUS Server Cleanup Wizard #
461# Parameters #
462# Set to $True or $False #
463################################
464
465# 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.
466[boolean]$AdamjSCWSupersededUpdatesDeclined = $True
467
468# Decline updates that aren't approved and have been expired my Microsoft.
469[boolean]$AdamjSCWExpiredUpdatesDeclined = $True
470
471# Delete updates that are expired and have not been approved for 30 days or more.
472[boolean]$AdamjSCWObsoleteUpdatesDeleted = $True
473
474# Delete older update revisions that have not been approved for 30 days or more.
475[boolean]$AdamjSCWUpdatesCompressed = $True
476
477# Delete computers that have not contacted the server in 30 days or more.
478[boolean]$AdamjSCWObsoleteComputersDeleted = $True
479
480# Delete update files that aren't needed by updates or downstream servers.
481[boolean]$AdamjSCWUnneededContentFiles = $True
482
483################################
484# Scheduled Run Variables #
485################################
486
487# On what day do you wish to run the MonthlyRun and QuarterlyRun Stream? I recommend on the 1st-7th of the month.
488# This will give enough time for you to approve (if you approve manually) and your computers to receive the
489# superseding updates after patch Tuesday (second Tuesday of the month).
490# (Valid days are 1-31. February, April, June, September, and November have logic to set to the last day
491# of the month if this is set to a number greater than the amount of days in that month, including leap years.)
492[int]$AdamjScheduledRunStreamsDay = "1"
493
494# What months would you like to run the QuarterlyRun Stream?
495# (Valid months are 1-12, comma separated for multiple months)
496[string]$AdamjScheduledRunQuarterlyMonths = "1,4,7,10"
497
498################################
499# Clean Up WSUS #
500# Synchronization Logs #
501# Variables #
502################################
503
504# Clean up the synchronization logs older than a consistency.
505
506# (Valid consistency number are whole numbers.)
507[int]$AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber = "14"
508
509# Valid consistency time are "Day" or "Month"
510[String]$AdamjCleanUpWSUSSynchronizationLogsConsistencyTime = "Day"
511
512# Or remove all synchronization logs each time
513[boolean]$AdamjCleanUpWSUSSynchronizationLogsAll = $False
514
515################################
516# SQL Server Variable #
517################################
518
519# ONLY uncomment and fill out if you are using a dedicated SQL Instance or if the script tells you to
520# otherwise leave this commented for auto-detection of the proper SQL Instance for the Windows Internal Database.
521# Example: "SERVER\INSTANCE" or "SERVER" (if using the Default Instance)
522
523# If you are using a Remote SQL connection, you will need to set the Scheduled Task to use the computer account
524# as the user that runs the script (Instead of searching for it, you must type it in the format of: DOMAIN\COMPUTER$)
525# or run the Scheduled Task as a user account saving credentials so that it can pass them through to the SQL Server.
526
527#[string]$AdamjSQLServer = ""
528
529################################
530# Do not edit below this line #
531################################
532$AdamjScriptTime = Get-Date
533
534# Set the script's current working directory path
535$AdamjScriptPath = split-path -parent $MyInvocation.MyCommand.Definition
536
537function Test-Administrator
538{
539 $CurrentUser = [Security.Principal.WindowsIdentity]::GetCurrent();
540 (New-Object Security.Principal.WindowsPrincipal $CurrentUser).IsInRole([Security.Principal.WindowsBuiltinRole]::Administrator)
541}
542$AdamjTestAdministrator = Test-Administrator
543if ($AdamjTestAdministrator -eq $True) { }
544if ($AdamjTestAdministrator -eq $False) {
545 Write-Host "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`""
546 Throw "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`""
547}
548
549if ($HelpMe -eq $True) {
550 Write-Host ""
551 Write-Host "============================="
552 Write-Host "Clean-WSUS HelpMe Stream"
553 Write-Host "============================="
554 Write-Host ""
555 Write-Host "This is the HelpMe Section for troubleshooting"
556 Write-Host "Please provide this information to get support"
557 Write-Host ""
558 Write-Host ""
559 Write-Host ""
560}
561
562function SQL-Ping-Instance
563{
564 param (
565 [parameter(Mandatory = $true)][string] $ServerInstance,
566 [parameter(Mandatory = $false)][int] $TimeOut = 1
567 )
568
569 $PingResult = $false
570
571 try
572 {
573 $SqlCatalog = "master"
574 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
575 $SqlConnection.ConnectionString = "Server = $ServerInstance; Database = $SqlCatalog; Integrated Security = True; Connection Timeout=$TimeOut"
576 $TimeOutVerbage = if ($TimeOut -gt "1") { "seconds" } else { "second" }
577 Write-Host "Initiating SQL Connection Testing to $ServerInstance with a timeout of $TimeOut $TimeOutVerbage"
578 $SqlConnection.Open()
579 $PingResult = $SqlConnection.State -eq "Open"
580 }
581
582 catch
583 {
584 Write-Host "Connection Failed."
585 }
586
587 finally
588 {
589 $SqlConnection.Close()
590 }
591
592 return $pingResult
593}
594
595[string]$AdamjWID2008 = 'np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query'
596[string]$AdamjWID2012 = 'np:\\.\pipe\MICROSOFT##WID\tsql\query'
597if (-not [string]::isnullorempty($AdamJSQLServer)) {
598 # Test to see if $AdamjSQLServer is set and if it is, with something other than blank.
599 # Then test to see if it can connect.
600 if ((SQL-Ping-Instance $AdamjSQLServer 60) -eq $False) {
601 # If it doesn't work, terminate the script erroring out with a reason.
602 Write-Host "I've tested the server `"$AdamjSQLServer`" in the configuration but can't connect to that SQL Server Instance. Please check the spelling again. Don't forget to specify the SQL Instance if there is one."
603 Throw "I've tested the server `"$AdamjSQLServer`" in the configuration but can't connect to that SQL Server Instance. Please check the spelling again. Don't forget to specify the SQL Instance if there is one."
604 }
605} elseif ((SQL-Ping-Instance $AdamjWID2008) -eq $true) {
606 # For server 2008 & 2008 R2 Windows Internal Database
607 $AdamjSQLServer = $AdamjWID2008
608} elseif ((SQL-Ping-Instance $AdamjWID2012) -eq $true) {
609 # For server 2012 & 2012 R2 Windows Internal Database
610 $AdamjSQLServer = $AdamjWID2012
611} else {
612 if ($HelpMe -ne $True) {
613 #Terminate the script erroring out with a reason.
614 Write-Host "I can't determine the SQL Server Instance. Please find the `"`$AdamjSQLServer`" variable in the configuration and set it as your SERVER\INSTANCE for WSUS"
615 Throw "I can't determine the SQL Server Instance. Please find the `"`$AdamjSQLServer`" variable in the configuration and set it as your SERVER\INSTANCE for WSUS"
616 }
617 else { Write-Host "I can't connect to SQL, and you've asked for help. Connecting to the WSUS Server to get troubleshooting information." }
618}
619
620#Create the connection command variable.
621$AdamjSQLConnectCommand = "sqlcmd -S $AdamjSQLServer"
622
623# Load .NET assembly
624[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration");
625
626# Connect to WSUS Server
627$AdamjWSUSServerAdminProxy = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($AdamjWSUSServer,$AdamjWSUSServerUseSecureConnection,$AdamjWSUSServerPortNumber);
628If ($? -eq $False) {
629 Write-Host "ERROR Connecting to the WSUS Server: $AdamjWSUSServer. Please check your settings and try again."
630 Throw "ERROR Connecting to the WSUS Server: $AdamjWSUSServer. Please check your settings and try again."
631} else {
632 $AdamjConnectedTime = Get-Date
633 $AdamjConnectedTXT = "Connected to the WSUS server $AdamjWSUSServer @ $($AdamjConnectedTime.ToString(`"yyyy.MM.dd hh:mm:ss tt zzz`"))`r`n`r`n"
634 $AdamjConnectedHTML = "<i>Connected to the WSUS server $AdamjWSUSServer @ $($AdamjConnectedTime.ToString(`"yyyy.MM.dd hh:mm:ss tt zzz`"))</i>`r`n`r`n"
635 Write-Host "Connected to the WSUS server $AdamjWSUSServer"
636}
637
638################################
639# Get-DiskFree #
640################################
641
642function Get-DiskFree
643# Taken from http://binarynature.blogspot.ca/2010/04/powershell-version-of-df-command.html
644{
645 [CmdletBinding()]
646 param
647 (
648 [Parameter(Position=0,
649 ValueFromPipeline=$true,
650 ValueFromPipelineByPropertyName=$true)]
651 [Alias('hostname')]
652 [Alias('cn')]
653 [string[]]$ComputerName = $env:COMPUTERNAME,
654
655 [Parameter(Position=1,
656 Mandatory=$false)]
657 [Alias('runas')]
658 [System.Management.Automation.Credential()]$Credential =
659 [System.Management.Automation.PSCredential]::Empty,
660
661 [Parameter(Position=2)]
662 [switch]$Format
663 )
664
665 BEGIN
666 {
667 function Format-HumanReadable
668 {
669 param ($size)
670 switch ($size)
671 {
672 {$_ -ge 1PB}{"{0:#.#'P'}" -f ($size / 1PB); break}
673 {$_ -ge 1TB}{"{0:#.#'T'}" -f ($size / 1TB); break}
674 {$_ -ge 1GB}{"{0:#.#'G'}" -f ($size / 1GB); break}
675 {$_ -ge 1MB}{"{0:#.#'M'}" -f ($size / 1MB); break}
676 {$_ -ge 1KB}{"{0:#'K'}" -f ($size / 1KB); break}
677 default {"{0}" -f ($size) + "B"}
678 }
679 }
680
681 $wmiq = 'SELECT * FROM Win32_LogicalDisk WHERE Size != Null AND DriveType >= 2'
682 }
683
684 PROCESS
685 {
686 foreach ($computer in $ComputerName)
687 {
688 try
689 {
690 if ($computer -eq $env:COMPUTERNAME)
691 {
692 $disks = Get-WmiObject -Query $wmiq `
693 -ComputerName $computer -ErrorAction Stop
694 }
695 else
696 {
697 $disks = Get-WmiObject -Query $wmiq `
698 -ComputerName $computer -Credential $Credential `
699 -ErrorAction Stop
700 }
701
702 if ($Format)
703 {
704 # Create array for $disk objects and then populate
705 $diskarray = @()
706 $disks | ForEach-Object { $diskarray += $_ }
707
708 $diskarray | Select-Object @{n='Name';e={$_.SystemName}},
709 @{n='Vol';e={$_.DeviceID}},
710 @{n='Size';e={Format-HumanReadable $_.Size}},
711 @{n='Used';e={Format-HumanReadable `
712 (($_.Size)-($_.FreeSpace))}},
713 @{n='Avail';e={Format-HumanReadable $_.FreeSpace}},
714 @{n='Use%';e={[int](((($_.Size)-($_.FreeSpace))`
715 /($_.Size) * 100))}},
716 @{n='FS';e={$_.FileSystem}},
717 @{n='Type';e={$_.Description}}
718 }
719 else
720 {
721 foreach ($disk in $disks)
722 {
723 $diskprops = @{'Volume'=$disk.DeviceID;
724 'Size'=$disk.Size;
725 'Used'=($disk.Size - $disk.FreeSpace);
726 'Available'=$disk.FreeSpace;
727 'FileSystem'=$disk.FileSystem;
728 'Type'=$disk.Description
729 'Computer'=$disk.SystemName;}
730
731 # Create custom PS object and apply type
732 $diskobj = New-Object -TypeName PSObject `
733 -Property $diskprops
734 $diskobj.PSObject.TypeNames.Insert(0,'BinaryNature.DiskFree')
735
736 Write-Output $diskobj
737 }
738 }
739 }
740 catch
741 {
742 # Check for common DCOM errors and display "friendly" output
743 switch ($_)
744 {
745 { $_.Exception.ErrorCode -eq 0x800706ba } `
746 { $err = 'Unavailable (Host Offline or Firewall)';
747 break; }
748 { $_.CategoryInfo.Reason -eq 'UnauthorizedAccessException' } `
749 { $err = 'Access denied (Check User Permissions)';
750 break; }
751 default { $err = $_.Exception.Message }
752 }
753 Write-Warning "$computer - $err"
754 }
755 }
756 }
757
758 END {}
759}
760
761################################
762# Setup the Header #
763################################
764
765function CreateAdamjHeader {
766$Script:AdamjBodyHeaderTXT = @"
767################################
768# #
769# Adamj Clean-WSUS #
770# Version 2.07 #
771# #
772# The last WSUS Script you #
773# will ever need! #
774# #
775################################
776
777
778"@
779$Script:AdamjBodyHeaderHTML = @"
780 <table style="height: 0px; width: 0px;" border="0">
781 <tbody>
782 <tr>
783 <td colspan="3">
784 <span
785 style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span>
786 </td>
787 </tr>
788 <tr>
789 <td style="text-align: left;">#</td>
790 <td style="text-align: center;"> </td>
791 <td style="text-align: right;">#</td>
792 </tr>
793 <tr>
794 <td style="text-align: left;">#</td>
795 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Adamj Clean-WSUS</span></td>
796 <td style="text-align: right;">#</td>
797 </tr>
798 <tr>
799 <td style="text-align: left;">#</td>
800 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Version 2.07</span></td>
801 <td style="text-align: right;">#</td>
802 </tr>
803 <tr>
804 <td style="text-align: left;">#</td>
805 <td> </td>
806 <td style="text-align: right;">#</td>
807 </tr>
808 <tr>
809 <td style="text-align: left;">#</td>
810 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">The last WSUS Script you</span></td>
811 <td style="text-align: right;">#</td>
812 </tr>
813 <tr>
814 <td style="text-align: left;">#</td>
815 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">will ever need!</span></td>
816 <td style="text-align: right;">#</td>
817 </tr>
818 <tr>
819 <td style="text-align: left;">#</td>
820 <td> </td>
821 <td style="text-align: right;">#</td>
822 </tr>
823 <tr>
824 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
825 </tr>
826 </tbody>
827 </table>
828
829
830"@
831}
832
833
834################################
835# Setup the Footer #
836################################
837
838function CreateAdamjFooter {
839
840$Script:AdamjBodyFooterTXT = @"
841
842################################
843# End of the WSUS Cleanup #
844################################
845# #
846# Adam Marshall #
847# http://www.adamj.org #
848# #
849# Latest version available #
850# from Spiceworks #
851# #
852################################
853
854http://community.spiceworks.com/scripts/show/2998-adamj-clean-wsus
855"@
856$Script:AdamjBodyFooterHTML = @"
857 <table style="height: 0px; width: 0px;" border="0">
858 <tbody>
859 <tr>
860 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
861 </tr>
862 <tr>
863 <td style="text-align: left;">#</td>
864 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">End of the WSUS Cleanup</span></td>
865 <td style="text-align: right;">#</td>
866 </tr>
867 <tr>
868 <td colspan="3" rowspan="1"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
869 </tr>
870 <tr>
871 <td style="text-align: left;">#</td>
872 <td style="text-align: center;"> </td>
873 <td style="text-align: right;">#</td>
874 </tr>
875 <tr>
876 <td style="text-align: left;">#</td>
877 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Adam Marshall</span></td>
878 <td style="text-align: right;">#</td>
879 </tr>
880 <tr>
881 <td style="text-align: left;">#</td>
882 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">http://www.adamj.org</span></td>
883 <td style="text-align: right;">#</td>
884 </tr>
885 <tr>
886 <td style="text-align: left;">#</td>
887 <td> </td>
888 <td style="text-align: right;">#</td>
889 </tr>
890 <tr>
891 <td style="text-align: left;">#</td>
892 <td style="text-align: center;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">Latest version available</span></td>
893 <td style="text-align: right;">#</td>
894 </tr>
895 <tr>
896 <td style="text-align: left;">#</td>
897 <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>
898 <td style="text-align: right;">#</td>
899 </tr>
900 <tr>
901 <td style="text-align: left;">#</td>
902 <td> </td>
903 <td style="text-align: right;">#</td>
904 </tr>
905 <tr>
906 <td colspan="3"><span style="font-family: tahoma,arial,helvetica,sans-serif;">################################</span></td>
907 </tr>
908 </tbody>
909 </table>
910"@
911}
912
913################################
914# Adamj Remove WSUS Drivers #
915# Stream #
916################################
917
918function RemoveWSUSDrivers {
919 param (
920 [Parameter()]
921 [Switch] $SQL
922 )
923 function RemoveWSUSDriversSQL {
924 $AdamjRemoveWSUSDriversSQLScript = @"
925/*
926################################
927# Adamj WSUS Delete Drivers #
928# SQL Script #
929# Version 1.0 #
930# Taken from various sources #
931# from the Internet. #
932# #
933# Modified By: Adam Marshall #
934# http://www.adamj.org #
935################################
936
937-- Originally taken from http://www.flexecom.com/how-to-delete-driver-updates-from-wsus-3-0/
938-- Modified to be dynamic and more of a nice output
939*/
940USE SUSDB;
941GO
942
943SET NOCOUNT ON;
944DECLARE @tbrevisionlanguage nvarchar(255)
945DECLARE @tbProperty nvarchar(255)
946DECLARE @tbLocalizedPropertyForRevision nvarchar(255)
947DECLARE @tbFileForRevision nvarchar(255)
948DECLARE @tbInstalledUpdateSufficientForPrerequisite nvarchar(255)
949DECLARE @tbPreRequisite nvarchar(255)
950DECLARE @tbDeployment nvarchar(255)
951DECLARE @tbXml nvarchar(255)
952DECLARE @tbPreComputedLocalizedProperty nvarchar(255)
953DECLARE @tbDriver nvarchar(255)
954DECLARE @tbFlattenedRevisionInCategory nvarchar(255)
955DECLARE @tbRevisionInCategory nvarchar(255)
956DECLARE @tbMoreInfoURLForRevision nvarchar(255)
957DECLARE @tbRevision nvarchar(255)
958DECLARE @tbUpdateSummaryForAllComputers nvarchar(255)
959DECLARE @tbUpdate nvarchar(255)
960
961
962
963DECLARE @var1 nvarchar(255)
964
965/*
966This query gives you the GUID that you will need to substitute in all subsequent queries. In my case, it is
967D2CB599A-FA9F-4AE9-B346-94AD54EE0629. I saw this GUID in several WSUS databases so I think it does not change;
968at least not between WSUS 3.0 SP2 servers. Either way, we are setting a variable for this so this will
969dynamically reference the correct GUID.
970*/
971
972SELECT @var1 = UpdateTypeID FROM tbUpdateType WHERE Name = 'Driver'
973
974/*
975The bad news is that WSUS database has over 100 tables. The good news is that SQL allows to enforce referential
976integrity in data model designs, which in this case can be used to essentially reverse engineer a procedure,
977that as far as I know isn’t documented anywhere.
978
979The trick is to delete all driver type records from tbUpdate table – but FIRST we have to delete all records in
980all other tables (revisions, languages, dependencies, files, reports…), which refer to driver rows in tbUpdate.
981
982Here’s how this is done, in 16 tables/queries.
983*/
984
985delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
986SELECT @tbrevisionlanguage = @@ROWCOUNT
987PRINT 'Delete records from tbrevisionlanguage: ' + @tbrevisionlanguage
988
989delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
990SELECT @tbProperty = @@ROWCOUNT
991PRINT 'Delete records from tbProperty: ' + @tbProperty
992
993delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
994SELECT @tbLocalizedPropertyForRevision = @@ROWCOUNT
995PRINT 'Delete records from tbLocalizedPropertyForRevision: ' + @tbLocalizedPropertyForRevision
996
997delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
998SELECT @tbFileForRevision = @@ROWCOUNT
999PRINT 'Delete records from tbFileForRevision: ' + @tbFileForRevision
1000
1001delete 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)))
1002SELECT @tbInstalledUpdateSufficientForPrerequisite = @@ROWCOUNT
1003PRINT 'Delete records from tbInstalledUpdateSufficientForPrerequisite: ' + @tbInstalledUpdateSufficientForPrerequisite
1004
1005delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1006SELECT @tbPreRequisite = @@ROWCOUNT
1007PRINT 'Delete records from tbPreRequisite: ' + @tbPreRequisite
1008
1009delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1010SELECT @tbDeployment = @@ROWCOUNT
1011PRINT 'Delete records from tbDeployment: ' + @tbDeployment
1012
1013delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1014SELECT @tbXml = @@ROWCOUNT
1015PRINT 'Delete records from tbXml: ' + @tbXml
1016
1017delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1018SELECT @tbPreComputedLocalizedProperty = @@ROWCOUNT
1019PRINT 'Delete records from tbPreComputedLocalizedProperty: ' + @tbPreComputedLocalizedProperty
1020
1021delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1022SELECT @tbDriver = @@ROWCOUNT
1023PRINT 'Delete records from tbDriver: ' + @tbDriver
1024
1025delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1026SELECT @tbFlattenedRevisionInCategory = @@ROWCOUNT
1027PRINT 'Delete records from tbFlattenedRevisionInCategory: ' + @tbFlattenedRevisionInCategory
1028
1029delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1030SELECT @tbRevisionInCategory = @@ROWCOUNT
1031PRINT 'Delete records from tbRevisionInCategory: ' + @tbRevisionInCategory
1032
1033delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1))
1034SELECT @tbMoreInfoURLForRevision = @@ROWCOUNT
1035PRINT 'Delete records from tbMoreInfoURLForRevision: ' + @tbMoreInfoURLForRevision
1036
1037delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)
1038SELECT @tbRevision = @@ROWCOUNT
1039PRINT 'Delete records from tbRevision: ' + @tbRevision
1040
1041delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = @var1)
1042SELECT @tbUpdateSummaryForAllComputers = @@ROWCOUNT
1043PRINT 'Delete records from tbUpdateSummaryForAllComputers: ' + @tbUpdateSummaryForAllComputers
1044
1045PRINT CHAR(13)+CHAR(10) + 'This is the last query and this is really what we came here for.'
1046
1047delete from tbUpdate where UpdateTypeID = @var1
1048SELECT @tbUpdate = @@ROWCOUNT
1049PRINT 'Delete records from tbUpdate: ' + @tbUpdate
1050
1051/*
1052If at this point you get an error saying something about foreign key constraint, that will be most likely
1053due to the difference between which reports I ran in my WSUS installation and which reports were ran against
1054your particular installation. Fortunately, the error gives you exact location (table) where this constraint
1055is violated, so you can adjust one of the queries in the batch above to delete references in any other tables.
1056*/
1057"@
1058
1059 # Create a file with the content of the AdamjRemoveWSUSDrivers SQL Script above in the
1060 # same working directory as this PowerShell script is running.
1061
1062 $AdamjRemoveWSUSDriversSQLScriptFile = "$AdamjScriptPath\AdamjRemoveWSUSDrivers.sql"
1063 $AdamjRemoveWSUSDriversSQLScript | Out-File $AdamjRemoveWSUSDriversSQLScriptFile
1064 # Re-jig the $AdamjSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1065 $AdamjSQLConnectCommand = $AdamjSQLConnectCommand.Replace('$','`$')
1066 # Execute the SQL Script and store the results in a variable.
1067 $AdamjRemoveWSUSDriversSQLScriptJobCommand = [scriptblock]::create("$AdamjSQLConnectCommand -i $AdamjRemoveWSUSDriversSQLScriptFile -I")
1068 $AdamjRemoveWSUSDriversSQLScriptJob = Start-Job -ScriptBlock $AdamjRemoveWSUSDriversSQLScriptJobCommand
1069 Wait-Job $AdamjRemoveWSUSDriversSQLScriptJob
1070 $AdamjRemoveWSUSDriversSQLScriptJobOutput = Receive-Job $AdamjRemoveWSUSDriversSQLScriptJob
1071 Remove-Job $AdamjRemoveWSUSDriversSQLScriptJob
1072 # Remove the SQL Script file.
1073 Remove-Item $AdamjRemoveWSUSDriversSQLScriptFile
1074 $Script:AdamjRemoveWSUSDriversSQLOutputTXT = $AdamjRemoveWSUSDriversSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n`r`n"
1075 $Script:AdamjRemoveWSUSDriversSQLOutputHTML = $AdamjRemoveWSUSDriversSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1076
1077 # Variables Output
1078 # $AdamjRemoveWSUSDriversSQLOutputTXT
1079 # $AdamjRemoveWSUSDriversSQLOutputHTML
1080
1081 }
1082 function RemoveWSUSDriversPS {
1083 $Count = 0
1084 $AdamjWSUSServerAdminProxy.GetUpdates() | Where-Object { $_.IsDeclined -eq $true -and $_.UpdateClassificationTitle -eq "Drivers" } | ForEach-Object {
1085 # Delete these updates
1086 $AdamjWSUSServerAdminProxy.DeleteUpdate($_.Id.UpdateId.ToString())
1087 $DeleteDeclinedDriverTitle = $_.Title
1088 $Count++
1089 $AdamjRemoveWSUSDriversPSDeleteOutputTXT += "$($Count). $($DeleteDeclinedDriverTitle)`n`n"
1090 $AdamjRemoveWSUSDriversPSDeleteOutputHTML += "<li>$DeleteDeclinedDriverTitle</li>`n"
1091 }
1092 $AdamjRemoveWSUSDriversPSDeleteOutputTXT += "`n`n"
1093 $AdamjRemoveWSUSDriversPSDeleteOutputHTML += "</ol>`n"
1094
1095 $Script:AdamjRemoveWSUSDriversPSOutputTXT += "`n`n"
1096 $Script:AdamjRemoveWSUSDriversPSOutputHTML += "<ol>`n"
1097 $Script:AdamjRemoveWSUSDriversPSOutputTXT += $AdamjRemoveWSUSDriversPSDeleteOutputTXT
1098 $Script:AdamjRemoveWSUSDriversPSOutputHTML += $AdamjRemoveWSUSDriversPSDeleteOutputHTML
1099
1100 # Variables Output
1101 # $AdamjRemoveWSUSDriversPSOutputTXT
1102 # $AdamjRemoveWSUSDriversPSOutputHTML
1103 }
1104 # Process the appropriate internal function
1105 $DateNow = Get-Date
1106 if ($SQL -eq $True) { RemoveWSUSDriversSQL } else { RemoveWSUSDriversPS }
1107 $FinishedRunning = Get-Date
1108 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1109 # Create the output for the RemoveWSUSDrivers function
1110 $Script:AdamjRemoveWSUSDriversOutputTXT += "Adamj Remove WSUS Drivers:`n`n"
1111 $Script:AdamjRemoveWSUSDriversOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Remove WSUS Drivers:</span></p>`n"
1112 if ($SQL -eq $True) {
1113 $Script:AdamjRemoveWSUSDriversOutputTXT += $AdamjRemoveWSUSDriversSQLOutputTXT
1114 $Script:AdamjRemoveWSUSDriversOutputHTML += $AdamjRemoveWSUSDriversSQLOutputHTML
1115 } else {
1116 $Script:AdamjRemoveWSUSDriversOutputTXT += $AdamjRemoveWSUSDriversPSOutputTXT
1117 $Script:AdamjRemoveWSUSDriversOutputHTML += $AdamjRemoveWSUSDriversPSOutputHTML
1118 }
1119 $Script:AdamjRemoveWSUSDriversOutputTXT += "Remove WSUS Drivers Stream Duration: {0:00}:{1:00}:{2:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1120 $Script:AdamjRemoveWSUSDriversOutputHTML += "<p>Remove WSUS Drivers Stream Duration: {0:00}:{1:00}:{2:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1121
1122 # Variables Output
1123 # $AdamjRemoveWSUSDriversOutputTXT
1124 # $AdamjRemoveWSUSDriversOutputHTML
1125}
1126
1127################################
1128# Adamj Remove Declined WSUS #
1129# Updates Stream #
1130################################
1131
1132function RemoveDeclinedWSUSUpdates {
1133 param (
1134 [Switch]$Display,
1135 [Switch]$Proceed
1136 )
1137
1138 # Log the date first
1139 $DateNow = Get-Date
1140 # Create an update scope
1141 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
1142 # by default the update scope is created for any approval states
1143 $UpdateScope.ApprovedStates = "Any"
1144 # Get all updates that have been Superseded and not Declined
1145 $AdamjRemoveDeclinedWSUSUpdatesUpdates = $AdamjWSUSServerAdminProxy.GetUpdates($UpdateScope) | Where { ($_.isDeclined) }
1146
1147 function RemoveDeclinedWSUSUpdatesCountUpdates {
1148 # First count how many updates will be removed that are already declined updates - just for fun. I like fun :)
1149 $Script:AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount = "{0:N0}" -f $AdamjRemoveDeclinedWSUSUpdatesUpdates.Count
1150 $Script:AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT += "The number of declined updates that would be removed from the database are: $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount.`r`n`r`n"
1151 $Script:AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML += "<p>The number of declined updates that would be removed from the database are: $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount.</p>`n"
1152
1153 # Variables Output
1154 # $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT
1155 # $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML
1156 }
1157
1158 function RemoveDeclinedWSUSUpdatesDisplayUpdates {
1159 # Display the titles of the declined updates that will be removed from the database - just for fun. I like fun :)
1160 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "<ol>`n"
1161 $Count=0
1162 ForEach ($update in $AdamjRemoveDeclinedWSUSUpdatesUpdates) {
1163 $Count++
1164 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputTXT += "$($Count). $($update.title) - https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`r`n"
1165 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "<li><a href=`"https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`">$($update.title)</a></li>`n"
1166 }
1167 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputTXT += "`r`n"
1168 $Script:AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML += "</ol>`n"
1169
1170 # Variables Output
1171 # $AdamjRemoveDeclinedWSUSUpdatesDisplayOutputTXT
1172 # $AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML
1173 }
1174
1175 function RemoveDeclinedWSUSUpdatesProceed {
1176 Write-Host "You've chosen to remove declined updates from the database. Removing $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates."
1177 Write-Host "Please be patient, this may take a while."
1178 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT += "You've chosen to remove declined updates from the database. Removing $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates.`r`n`r`n"
1179 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += "<p>You've chosen to remove declined updates from the database. <strong>Removing $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesCount declined updates.</strong></p>`n"
1180 # Remove these updates
1181 $AdamjRemoveDeclinedWSUSUpdatesUpdates | ForEach-Object {
1182 $DeleteID = $_.Id.UpdateId.ToString()
1183 Try {
1184 $AdamjRemoveDeclinedWSUSUpdatesUpdateTitle = $($_.Title)
1185 Write-Host "Deleting" $AdamjRemoveDeclinedWSUSUpdatesUpdateTitle
1186 $AdamjWSUSServerAdminProxy.DeleteUpdate($DeleteId)
1187 }
1188 Catch {
1189 $ExceptionError = $_.Exception
1190 if ([string]::isnullorempty($AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT)) { $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT = "" }
1191 if ([string]::isnullorempty($AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML)) { $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML = "" }
1192 $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT += "Error: $AdamjRemoveDeclinedWSUSUpdatesUpdateTitle`r`n`r`n$ExceptionError.InnerException`r`n`r`n"
1193 $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML += "<li><p>$AdamjRemoveDeclinedWSUSUpdatesUpdateTitle</p>$ExceptionError.InnerException</li>"
1194 }
1195 Finally {
1196 Write-Host "Errors:" $ExceptionError.Message
1197 }
1198 }
1199
1200
1201 if (-not [string]::isnullorempty($AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT)) {
1202 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT += "*** Errors Removing Declined WSUS Updates ***`r`n"
1203 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT += $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsTXT
1204 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT += "`r`n`r`n"
1205 }
1206
1207 if (-not [string]::isnullorempty($AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML)) {
1208 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += @"
1209<style type="text/css">
1210.error {
1211border: 2px solid;
1212margin: 10px 10px;
1213padding:15px 50px 15px 50px;
1214}
1215.error ol {
1216color: #D8000C;
1217}
1218.error ol li p {
1219color: #000;
1220background-color: transparent;
1221}
1222.error ol li {
1223background-color: #FFBABA;
1224margin: 10px 0;
1225}
1226</style>
1227"@
1228 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += "<div class='error'><h1>Errors Removing Declined WSUS Updates</h1><ol start='1'>"
1229 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += $AdamjRemoveDeclinedWSUSUpdatesProceedExceptionsHTML
1230 $Script:AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML += "</ol></div>"
1231 }
1232
1233
1234
1235
1236 # Variables Output
1237 # $AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT
1238 # $AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML
1239 }
1240
1241
1242 RemoveDeclinedWSUSUpdatesCountUpdates
1243 if ($Display -ne $False) { RemoveDeclinedWSUSUpdatesDisplayUpdates }
1244 if ($Proceed -ne $False) { RemoveDeclinedWSUSUpdatesProceed }
1245 $FinishedRunning = Get-Date
1246 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1247
1248 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += "Adamj Remove Declined WSUS Updates:`r`n`r`n"
1249 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Remove Declined WSUS Updates:</span></p>`n<ol>`n"
1250 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputTXT
1251 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += $AdamjRemoveDeclinedWSUSUpdatesCountUpdatesOutputHTML
1252 if ($Display -ne $False) {
1253 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += $AdamjRemoveDeclinedWSUSUpdatesDisplayOutputTXT
1254 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += $AdamjRemoveDeclinedWSUSUpdatesDisplayOutputHTML
1255 }
1256 if ($Proceed -ne $False) {
1257 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += $AdamjRemoveDeclinedWSUSUpdatesProceedOutputTXT
1258 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += $AdamjRemoveDeclinedWSUSUpdatesProceedOutputHTML
1259 }
1260 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputTXT += "Remove Declined WSUS Updates Stream Duration: {0:00}:{1:00}:{2:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1261 $Script:AdamjRemoveDeclinedWSUSUpdatesOutputHTML += "<p>Remove Declined WSUS Updates Stream Duration: {0:00}:{1:00}:{2:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1262
1263 # Variables Output
1264 # $AdamjRemoveDeclinedWSUSUpdatesOutputTXT
1265 # $AdamjRemoveDeclinedWSUSUpdatesOutputHTML
1266}
1267
1268################################
1269# Adamj WSUS DB Maintenance #
1270# Stream #
1271################################
1272
1273function WSUSDBMaintenance {
1274 Param (
1275 [Switch]$NoOutput
1276 )
1277 $DateNow = Get-Date
1278 $AdamjWSUSDBMaintenanceSQLScript = @"
1279/*
1280################################
1281# Adamj WSUSDBMaintenance #
1282# SQL Script #
1283# Version 1.0 #
1284# Taken from TechNet #
1285# referenced below. #
1286# #
1287# Adam Marshall #
1288# http://www.adamj.org #
1289################################
1290*/
1291-- Taken from https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61
1292
1293/******************************************************************************
1294This sample T-SQL script performs basic maintenance tasks on SUSDB
12951. Identifies indexes that are fragmented and defragments them. For certain
1296 tables, a fill-factor is set in order to improve insert performance.
1297 Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx
1298 and tailored for SUSDB requirements
12992. Updates potentially out-of-date table statistics.
1300******************************************************************************/
1301
1302USE SUSDB;
1303GO
1304SET NOCOUNT ON;
1305
1306-- Rebuild or reorganize indexes based on their fragmentation levels
1307DECLARE @work_to_do TABLE (
1308 objectid int
1309 , indexid int
1310 , pagedensity float
1311 , fragmentation float
1312 , numrows int
1313)
1314
1315DECLARE @objectid int;
1316DECLARE @indexid int;
1317DECLARE @schemaname nvarchar(130);
1318DECLARE @objectname nvarchar(130);
1319DECLARE @indexname nvarchar(130);
1320DECLARE @numrows int
1321DECLARE @density float;
1322DECLARE @fragmentation float;
1323DECLARE @command nvarchar(4000);
1324DECLARE @fillfactorset bit
1325DECLARE @numpages int
1326
1327-- Select indexes that need to be defragmented based on the following
1328-- * Page density is low
1329-- * External fragmentation is high in relation to index size
1330PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
1331INSERT @work_to_do
1332SELECT
1333 f.object_id
1334 , index_id
1335 , avg_page_space_used_in_percent
1336 , avg_fragmentation_in_percent
1337 , record_count
1338FROM
1339 sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
1340WHERE
1341 (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
1342 or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)
1343 or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)
1344
1345PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))
1346
1347PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)
1348
1349SELECT @numpages = sum(ps.used_page_count)
1350FROM
1351 @work_to_do AS fi
1352 INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
1353 INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
1354
1355-- Declare the cursor for the list of indexes to be processed.
1356DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
1357
1358-- Open the cursor.
1359OPEN curIndexes
1360
1361-- Loop through the indexes
1362WHILE (1=1)
1363BEGIN
1364 FETCH NEXT FROM curIndexes
1365 INTO @objectid, @indexid, @density, @fragmentation, @numrows;
1366 IF @@FETCH_STATUS < 0 BREAK;
1367
1368 SELECT
1369 @objectname = QUOTENAME(o.name)
1370 , @schemaname = QUOTENAME(s.name)
1371 FROM
1372 sys.objects AS o
1373 INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
1374 WHERE
1375 o.object_id = @objectid;
1376
1377 SELECT
1378 @indexname = QUOTENAME(name)
1379 , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
1380 FROM
1381 sys.indexes
1382 WHERE
1383 object_id = @objectid AND index_id = @indexid;
1384
1385 IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
1386 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
1387 ELSE IF @numrows >= 5000 AND @fillfactorset = 0
1388 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
1389 ELSE
1390 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
1391 PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
1392 EXEC (@command);
1393 PRINT convert(nvarchar, getdate(), 121) + N' Done.';
1394END
1395
1396-- Close and deallocate the cursor.
1397CLOSE curIndexes;
1398DEALLOCATE curIndexes;
1399
1400
1401IF EXISTS (SELECT * FROM @work_to_do)
1402BEGIN
1403 PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
1404 SELECT @numpages = @numpages - sum(ps.used_page_count)
1405 FROM
1406 @work_to_do AS fi
1407 INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
1408 INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id
1409
1410 PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
1411END
1412GO
1413
1414
1415--Update all statistics
1416PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
1417EXEC sp_updatestats
1418PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
1419GO
1420"@
1421 # Create a file with the content of the AdamjWsusDBMaintenance Script above in the
1422 # same working directory as this PowerShell script is running.
1423 $AdamjWSUSDBMaintenanceSQLScriptFile = "$AdamjScriptPath\AdamjWSUSDBMaintenance.sql"
1424 $AdamjWSUSDBMaintenanceSQLScript | Out-File $AdamjWSUSDBMaintenanceSQLScriptFile
1425
1426 # Re-jig the $AdamjSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1427 $AdamjSQLConnectCommand = $AdamjSQLConnectCommand.Replace('$','`$')
1428 # Execute the SQL Script and store the results in a variable.
1429 $AdamjWSUSDBMaintenanceSQLScriptJobCommand = [scriptblock]::create("$AdamjSQLConnectCommand -i $AdamjWSUSDBMaintenanceSQLScriptFile -I")
1430 $AdamjWSUSDBMaintenanceSQLScriptJob = Start-Job -ScriptBlock $AdamjWSUSDBMaintenanceSQLScriptJobCommand
1431 Wait-Job $AdamjWSUSDBMaintenanceSQLScriptJob
1432 $AdamjWSUSDBMaintenanceSQLScriptJobOutput = Receive-Job $AdamjWSUSDBMaintenanceSQLScriptJob
1433 Remove-Job $AdamjWSUSDBMaintenanceSQLScriptJob
1434 # Remove the SQL Script file.
1435 Remove-Item $AdamjWSUSDBMaintenanceSQLScriptFile
1436 $FinishedRunning = Get-Date
1437 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1438 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1439
1440 if ($NoOutput -eq $False) {
1441 $Script:AdamjWSUSDBMaintenanceOutputTXT += "Adamj WSUS DB Maintenance:`r`n`r`n"
1442 $Script:AdamjWSUSDBMaintenanceOutputTXT += $AdamjWSUSDBMaintenanceSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1443 $Script:AdamjWSUSDBMaintenanceOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj WSUS DB Maintenance:</span></p>`n`n"
1444 $Script:AdamjWSUSDBMaintenanceOutputHTML += $AdamjWSUSDBMaintenanceSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1445 } else {
1446 $Script:AdamjWSUSDBMaintenanceOutputTXT += "Adamj WSUS DB Maintenance:`r`n`r`n"
1447 $Script:AdamjWSUSDBMaintenanceOutputTXT += "The Adamj WSUS DB Maintenance Stream was run with the -NoOutput switch.`r`n`r`n"
1448 $Script:AdamjWSUSDBMaintenanceOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj WSUS DB Maintenance:</span></p>`n`n"
1449 $Script:AdamjWSUSDBMaintenanceOutputHTML += "<p>The Adamj WSUS DB Maintenance Stream was run with the -NoOutput switch.</p>`n`n"
1450 }
1451 $Script:AdamjWSUSDBMaintenanceOutputTXT += "WSUS DB Maintenance Stream Duration: {0:00}:{1:00}:{2:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1452 $Script:AdamjWSUSDBMaintenanceOutputHTML += "<p>WSUS DB Maintenance Stream Duration: {0:00}:{1:00}:{2:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1453
1454 # Variables Output
1455 # $AdamjWSUSDBMaintenanceOutputTXT
1456 # $AdamjWSUSDBMaintenanceOutputHTML
1457}
1458
1459################################
1460# Adamj Decline Superseded #
1461# Updates Stream #
1462################################
1463
1464function DeclineSupersededUpdates {
1465 param (
1466 [Switch]$Display,
1467 [Switch]$Proceed
1468 )
1469
1470 # Log the date first
1471 $DateNow = Get-Date
1472 # Create an update scope
1473 $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
1474 # by default the update scope is created for any approval states
1475 $UpdateScope.ApprovedStates = "Any"
1476 # Get all updates that have been Superseded and not Declined
1477 $AdamjDeclineSupersededUpdatesUpdates = $AdamjWSUSServerAdminProxy.GetUpdates($UpdateScope) | Where { ($_.IsSuperseded) -and -not ($_.isDeclined) }
1478
1479 function DeclineSupersededUpdatesCountUpdates {
1480 # First count how many updates will be declined by Declining Superseded Updates - just for fun. I like fun :)
1481 $Script:AdamjDeclineSupersededUpdatesCountUpdatesCount = "{0:N0}" -f $AdamjDeclineSupersededUpdatesUpdates.Count
1482 $Script:AdamjDeclineSupersededUpdatesCountUpdatesOutputTXT += "The number of Superseded updates that would be declined are: $AdamjDeclineSupersededUpdatesCountUpdatesCount.`r`n`r`n"
1483 $Script:AdamjDeclineSupersededUpdatesCountUpdatesOutputHTML += "<p>The number of Superseded updates that would be declined are: $AdamjDeclineSupersededUpdatesCountUpdatesCount.</p>`n"
1484
1485 # Variables Output
1486 # $AdamjDeclineSupersededUpdatesCountUpdatesOutputTXT
1487 # $AdamjDeclineSupersededUpdatesCountUpdatesOutputTXT
1488 }
1489 function DeclineSupersededUpdatesDisplayUpdates {
1490 # Display the titles of the Superseded updates that will be declined - just for fun. I like fun :)
1491 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "<ol>`n"
1492 $Count=0
1493 ForEach ($update in $AdamjDeclineSupersededUpdatesUpdates) {
1494 $Count++
1495 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputTXT += "$($Count). $($update.title) - https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`r`n"
1496 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "<li><a href=`"https://support.microsoft.com/en-us/kb/$($update.KnowledgebaseArticles)`">$($update.title)</a></li>`n"
1497 }
1498 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputTXT += "`r`n"
1499 $Script:AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML += "</ol>`n"
1500
1501 # Variables Output
1502 # $AdamjDeclineSupersededUpdatesUpdatesDisplayOutputTXT
1503 # $AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML
1504 }
1505 function DeclineSupersededUpdatesProceed {
1506 $Script:AdamjDeclineSupersededUpdatesProceedOutputTXT += "You've chosen to Decline Superseded Updates. Declining $AdamjDeclineSupersededUpdatesCountUpdatesCount Superseded updates.`r`n`r`n"
1507 $Script:AdamjDeclineSupersededUpdatesProceedOutputHTML += "<p>You've chosen to Decline Superseded Updates. <strong>Declining $AdamjDeclineSupersededUpdatesCountUpdatesCount Superseded updates.</strong></p>`n"
1508 # Decline these updates
1509 $AdamjDeclineSupersededUpdatesUpdates | ForEach-Object -Process { $_.Decline() }
1510
1511 # Variables Output
1512 # $AdamjDeclineSupersededUpdatesProceedOutputTXT
1513 # $AdamjDeclineSupersededUpdatesProceedOutputHTML
1514 }
1515
1516 DeclineSupersededUpdatesCountUpdates
1517 if ($Display -ne $False) { DeclineSupersededUpdatesDisplayUpdates }
1518 if ($Proceed -ne $False) { DeclineSupersededUpdatesProceed }
1519 $FinishedRunning = Get-Date
1520 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1521
1522 $Script:AdamjDeclineSupersededUpdatesOutputTXT += "Adamj Decline Superseded Updates:`r`n"
1523 $Script:AdamjDeclineSupersededUpdatesOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Decline Superseded Updates:</span><br>`n"
1524 $Script:AdamjDeclineSupersededUpdatesOutputTXT += $AdamjDeclineSupersededUpdatesCountUpdatesOutputTXT
1525 $Script:AdamjDeclineSupersededUpdatesOutputHTML += $AdamjDeclineSupersededUpdatesCountUpdatesOutputHTML
1526 if ($Display -ne $False) {
1527 $Script:AdamjDeclineSupersededUpdatesOutputTXT += $AdamjDeclineSupersededUpdatesUpdatesDisplayOutputTXT
1528 $Script:AdamjDeclineSupersededUpdatesOutputHTML += $AdamjDeclineSupersededUpdatesUpdatesDisplayOutputHTML
1529 }
1530 if ($Proceed -ne $False) {
1531 $Script:AdamjDeclineSupersededUpdatesOutputTXT += $AdamjDeclineSupersededUpdatesProceedOutputTXT
1532 $Script:AdamjDeclineSupersededUpdatesOutputHTML += $AdamjDeclineSupersededUpdatesProceedOutputHTML
1533 }
1534 $Script:AdamjDeclineSupersededUpdatesOutputTXT += "Decline Superseded Updates Stream Duration: {0:00}:{1:00}:{2:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1535 $Script:AdamjDeclineSupersededUpdatesOutputHTML += "<p>Decline Superseded Updates Stream Duration: {0:00}:{1:00}:{2:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1536}
1537
1538################################
1539# Clean Up WSUS #
1540# Synchronization Logs Stream #
1541################################
1542
1543function CleanUpWSUSSynchronizationLogs {
1544 Param(
1545 [Int]$ConsistencyNumber,
1546 [String]$ConsistencyTime,
1547 [Switch]$All
1548 )
1549 $DateNow = Get-Date
1550 $AdamjCleanUpWSUSSynchronizationLogsSQLScript = @"
1551/*
1552################################
1553# Adamj WSUS Synchronization #
1554# Cleanup SQL Script #
1555# Version 1.0 #
1556# Taken from various sources #
1557# from the Internet. #
1558# #
1559# Modified By: Adam Marshall #
1560# http://www.adamj.org #
1561################################
1562*/
1563$(
1564 if ($ConsistencyNumber -ne "0") {
1565 $("
1566USE SUSDB
1567GO
1568DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389') AND DATEDIFF($($ConsistencyTime), TimeAtServer, CURRENT_TIMESTAMP) >= $($ConsistencyNumber);
1569GO")
1570}
1571elseif ($All -ne $False) {
1572$("USE SUSDB
1573GO
1574DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389')
1575GO")
1576}
1577)
1578"@
1579 # Create a file with the content of the AdamjCleanUpWSUSSynchronizationLogs Script above in the
1580 # same working directory as this PowerShell script is running.
1581 $AdamjCleanUpWSUSSynchronizationLogsSQLScriptFile = "$AdamjScriptPath\AdamjCleanUpWSUSSynchronizationLogs.sql"
1582 $AdamjCleanUpWSUSSynchronizationLogsSQLScript | Out-File $AdamjCleanUpWSUSSynchronizationLogsSQLScriptFile
1583 # Re-jig the $AdamjSQLConnectCommand to replace the $ with a `$ for Windows 2008 Internal Database possiblity.
1584 $AdamjSQLConnectCommand = $AdamjSQLConnectCommand.Replace('$','`$')
1585 # Execute the SQL Script and store the results in a variable.
1586 $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobCommand = [scriptblock]::create("$AdamjSQLConnectCommand -i $AdamjCleanUpWSUSSynchronizationLogsSQLScriptFile -I")
1587 $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJob = Start-Job -ScriptBlock $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobCommand
1588 Wait-Job $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJob
1589 $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobOutput = Receive-Job $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJob
1590 Remove-Job $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJob
1591 # Remove the SQL Script file.
1592 Remove-Item $AdamjCleanUpWSUSSynchronizationLogsSQLScriptFile
1593 $FinishedRunning = Get-Date
1594 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1595
1596 # Setup variables to store the output to be added at the very end of the script for logging purposes.
1597 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT += "Adamj Clean Up WSUS Synchornization Logs:`r`n`r`n"
1598 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT += $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","`r`n"
1599 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT += "Clean Up WSUS Synchronization Logs Stream Duration: {0:00}:{1:00}:{2:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1600
1601 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj Clean Up WSUS Synchornization Logs:</span></p>`r`n"
1602 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML += $AdamjCleanUpWSUSSynchronizationLogsSQLScriptJobOutput.Trim() -creplace'(?m)^\s*\r?\n','' -creplace '$?',"" -creplace "$","<br>`r`n"
1603 $Script:AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML += "<p>Clean Up WSUS Synchronization Logs Stream Duration: {0:00}:{1:00}:{2:00}</p>`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1604
1605 # Variables Output
1606 # $AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT
1607 # $AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML
1608
1609}
1610
1611################################
1612# WSUS Server Cleanup Wizard #
1613# Stream #
1614################################
1615
1616function WSUSServerCleanupWizard {
1617 $DateNow = Get-Date
1618 $WSUSServerCleanupWizardBody = "<p><span style=`"font-weight: bold; font-size: 1.2em;`">WSUS Server Cleanup Wizard:</span></p>" | Out-String
1619 $CleanupManager = $AdamjWSUSServerAdminProxy.GetCleanupManager();
1620 $CleanupScope = New-Object Microsoft.UpdateServices.Administration.CleanupScope ($AdamjSCWSupersededUpdatesDeclined,$AdamjSCWExpiredUpdatesDeclined,$AdamjSCWObsoleteUpdatesDeleted,$AdamjSCWUpdatesCompressed,$AdamjSCWObsoleteComputersDeleted,$AdamjSCWUnneededContentFiles);
1621 $AdamjCleanupResults = $CleanupManager.PerformCleanup($CleanupScope)
1622 $FinishedRunning = Get-Date
1623 $DifferenceInTime = New-TimeSpan –Start $DateNow –End $FinishedRunning
1624
1625
1626 $AdamjCSSStyling =@"
1627<style type="text/css">
1628table.gridtable {
1629 font-family: verdana,arial,sans-serif;
1630 font-size:11px;
1631 color:#333333;
1632 border-width: 1px;
1633 border-color: #666666;
1634 border-collapse: collapse;
1635}
1636table.gridtable th {
1637 border-width: 1px;
1638 padding: 8px;
1639 border-style: solid;
1640 border-color: #666666;
1641 background-color: #dedede;
1642}
1643table.gridtable td {
1644 border-width: 1px;
1645 padding: 8px;
1646 border-style: solid;
1647 border-color: #666666;
1648 background-color: #ffffff;
1649}
1650.TFtable{
1651 border-collapse:collapse;
1652}
1653.TFtable td{
1654 padding:7px;
1655 border:#4e95f4 1px solid;
1656}
1657
1658/* provide some minimal visual accommodation for IE8 and below */
1659.TFtable tr{
1660 background: #b8d1f3;
1661}
1662/* Define the background color for all the ODD background rows */
1663.TFtable tr:nth-child(odd){
1664 background: #b8d1f3;
1665}
1666/* Define the background color for all the EVEN background rows */
1667.TFtable tr:nth-child(even){
1668 background: #dae5f4;
1669}
1670</style>
1671"@
1672 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "Adamj WSUS Server Cleanup Wizard:`r`n`r`n"
1673 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "$AdamjWSUSServer`r`n"
1674 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "Version: $($AdamjWSUSServerAdminProxy.Version)`r`n"
1675 #$Script:AdamjWSUSServerCleanupWizardOutputTXT += "Started: $($DateNow.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))`r`n"
1676 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "SupersededUpdatesDeclined: $($AdamjCleanupResults.SupersededUpdatesDeclined)`r`n"
1677 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "ExpiredUpdatesDeclined: $($AdamjCleanupResults.ExpiredUpdatesDeclined)`r`n"
1678 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "ObsoleteUpdatesDeleted: $($AdamjCleanupResults.ObsoleteUpdatesDeleted)`r`n"
1679 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "UpdatesCompressed: $($AdamjCleanupResults.UpdatesCompressed)`r`n"
1680 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "ObsoleteComputersDeleted: $($AdamjCleanupResults.ObsoleteComputersDeleted)`r`n"
1681 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "DiskSpaceFreed (MB): $([math]::round($AdamjCleanupResults.DiskSpaceFreed/1MB, 2))`r`n"
1682 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "DiskSpaceFreed (GB): $([math]::round($AdamjCleanupResults.DiskSpaceFreed/1GB, 2))`r`n"
1683 #$Script:AdamjWSUSServerCleanupWizardOutputTXT += "Finished: $($FinishedRunning.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))`r`n"
1684 $Script:AdamjWSUSServerCleanupWizardOutputTXT += "WSUS Server Cleanup Wizard Duration: {0:00}:{1:00}:{2:00}`r`n`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1685
1686 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<p><span style=`"font-weight: bold; font-size: 1.2em;`">Adamj WSUS Server Cleanup Wizard:</span></p>`r`n"
1687 $Script:AdamjWSUSServerCleanupWizardOutputHTML += $AdamjCSSStyling + "`r`n"
1688 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<table class=`"gridtable`">`r`n"
1689 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tbody>`r`n"
1690 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><th colspan=`"2`" rowspan=`"1`">$AdamjWSUSServer</th></tr>`r`n"
1691 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>Version:</td><td>$($AdamjWSUSServerAdminProxy.Version)</td></tr>`r`n"
1692 #$Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>Started:</td><td>$($DateNow.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))</td></tr>`r`n"
1693 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>SupersededUpdatesDeclined:</td><td>$($AdamjCleanupResults.SupersededUpdatesDeclined)</td></tr>`r`n"
1694 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>ExpiredUpdatesDeclined:</td><td>$($AdamjCleanupResults.ExpiredUpdatesDeclined)</td></tr>`r`n"
1695 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>ObsoleteUpdatesDeleted:</td><td>$($AdamjCleanupResults.ObsoleteUpdatesDeleted)</td></tr>`r`n"
1696 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>UpdatesCompressed:</td><td>$($AdamjCleanupResults.UpdatesCompressed)</td></tr>`r`n"
1697 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>ObsoleteComputersDeleted:</td><td>$($AdamjCleanupResults.ObsoleteComputersDeleted)</td></tr>`r`n"
1698 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>DiskSpaceFreed (MB):</td><td>$([math]::round($AdamjCleanupResults.DiskSpaceFreed/1MB, 2))</td></tr>`r`n"
1699 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>DiskSpaceFreed (GB):</td><td>$([math]::round($AdamjCleanupResults.DiskSpaceFreed/1GB, 2))</td></tr>`r`n"
1700 #$Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>Finished:</td><td>$($FinishedRunning.ToString("yyyy.MM.dd hh:mm:ss tt zzz"))</td></tr>`r`n"
1701 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "<tr><td>WSUS Server Cleanup Wizard Duration:</td><td>{0:00}:{1:00}:{2:00}</td></tr>`r`n" -f ($DifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1702 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "</tbody>`r`n"
1703 $Script:AdamjWSUSServerCleanupWizardOutputHTML += "</table>`r`n"
1704
1705 Remove-Variable AdamjCleanupResults
1706 # Variables Output
1707 # $AdamjWSUSServerCleanupWizardOutputTXT
1708 # $AdamjWSUSServerCleanupWizardOutputHTML
1709}
1710
1711
1712function AdamjScriptDifferenceInTime {
1713 $AdamjScriptFinishedRunning = Get-Date
1714 $Script:AdamjScriptDifferenceInTime = New-TimeSpan –Start $AdamjScriptTime –End $AdamjScriptFinishedRunning
1715}
1716
1717################################
1718# Create the TXT output #
1719################################
1720
1721function CreateBodyTXT {
1722 $Script:AdamjBodyTXT = "`n"
1723 $Script:AdamjBodyTXT += $AdamjBodyHeaderTXT
1724 $Script:AdamjBodyTXT += $AdamjConnectedTXT
1725 $Script:AdamjBodyTXT += $AdamjDeclineSupersededUpdatesOutputTXT
1726 $Script:AdamjBodyTXT += $AdamjCleanUpWSUSSynchronizationLogsSQLOutputTXT
1727 $Script:AdamjBodyTXT += $AdamjRemoveWSUSDriversOutputTXT
1728 $Script:AdamjBodyTXT += $AdamjRemoveDeclinedWSUSUpdatesOutputTXT
1729 $Script:AdamjBodyTXT += $AdamjWSUSDBMaintenanceOutputTXT
1730 $Script:AdamjBodyTXT += $AdamjWSUSServerCleanupWizardOutputTXT
1731 $Script:AdamjBodyTXT += "Clean-WSUS Script Duration: {0:00}:{1:00}:{2:00}`r`n`r`n" -f ($AdamjScriptDifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1732 $Script:AdamjBodyTXT += $AdamjBodyFooterTXT
1733}
1734
1735################################
1736# Create the HTML output #
1737################################
1738
1739function CreateBodyHTML {
1740 $Script:AdamjBodyHTML = "`n"
1741 $Script:AdamjBodyHTML += $AdamjBodyHeaderHTML
1742 $Script:AdamjBodyHTML += $AdamjConnectedHTML
1743 $Script:AdamjBodyHTML += $AdamjDeclineSupersededUpdatesOutputHTML
1744 $Script:AdamjBodyHTML += $AdamjCleanUpWSUSSynchronizationLogsSQLOutputHTML
1745 $Script:AdamjBodyHTML += $AdamjRemoveWSUSDriversOutputHTML
1746 $Script:AdamjBodyHTML += $AdamjRemoveDeclinedWSUSUpdatesOutputHTML
1747 $Script:AdamjBodyHTML += $AdamjWSUSDBMaintenanceOutputHTML
1748 $Script:AdamjBodyHTML += $AdamjWSUSServerCleanupWizardOutputHTML
1749 $Script:AdamjBodyHTML += "<p>Clean-WSUS Script Duration: {0:00}:{1:00}:{2:00}</p>`r`n" -f ($AdamjScriptDifferenceInTime | % {$_.Hours, $_.Minutes, $_.Seconds})
1750 $Script:AdamjBodyHTML += $AdamjBodyFooterHTML
1751}
1752
1753
1754
1755################################
1756# Save the Report #
1757################################
1758
1759function SaveReport {
1760 Param(
1761 [ValidateSet("TXT","HTML")]
1762 [String]$ReportType = "TXT"
1763 )
1764 if ($ReportType -eq "HTML") {
1765 $AdamjBodyHTML | Out-File -FilePath "$AdamjScriptPath\$(get-date -f "yyyy.MM.dd-HH.mm.ss").htm"
1766 } else {
1767 $AdamjBodyTXT | Out-File -FilePath "$AdamjScriptPath\$(get-date -f "yyyy.MM.dd-HH.mm.ss").txt"
1768 }
1769}
1770
1771################################
1772# Mail the Report #
1773################################
1774
1775function MailReport {
1776 param (
1777 [ValidateSet("TXT","HTML")]
1778 [String] $MessageContentType = "HTML"
1779 )
1780 $message = New-Object System.Net.Mail.MailMessage
1781 $mailer = New-Object System.Net.Mail.SmtpClient ($AdamjMailReportSMTPServer, $AdamjMailReportSMTPPort)
1782 $mailer.EnableSSL = $AdamjMailReportSMTPServerEnableSSL
1783 if ($AdamjMailReportSMTPServerUsername -ne "") {
1784 $mailer.Credentials = New-Object System.Net.NetworkCredential($AdamjMailReportSMTPServerUsername, $AdamjMailReportSMTPServerPassword)
1785 }
1786 $message.From = $AdamjMailReportEmailFromAddress
1787 $message.To.Add($AdamjMailReportEmailToAddress)
1788 $message.Subject = $AdamjMailReportEmailSubject
1789 $message.Body = if ($MessageContentType -eq "HTML") { $AdamjBodyHTML } else { $AdamjBodyTXT }
1790 $message.IsBodyHtml = if ($MessageContentType -eq "HTML") { $True } else { $False }
1791 $mailer.send(($message))
1792}
1793
1794################################
1795# Help Me #
1796################################
1797
1798function HelpMe {
1799 ((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()
1800 Write-Host "PowerShell Version:" $PSVersionTable.PSVersion.ToString()
1801 Write-Host "WSUS Version: $($AdamjWSUSServerAdminProxy.Version)"
1802 Write-Host "Replica Server: "$AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer
1803 Write-Host "The path to the WSUS Content folder is:" $AdamjWSUSServerAdminProxy.GetConfiguration().LocalContentCachePath
1804 Write-Host "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
1805 Write-Host "All Volumes on the WSUS Server:"
1806 (Get-DiskFree -Format | Out-String).Trim()
1807 Write-Host ".NET Installed Versions"
1808 (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()
1809 Write-Host "============================="
1810 Write-Host "All Adamj Variables"
1811 Write-Host "============================="
1812 (Get-Variable | Where-Object { $_.Name -match "Adamj" } | Format-Table -AutoSize | Out-String).Trim()
1813 Write-Host "============================="
1814 Write-Host " End of HelpMe Stream"
1815 Write-Host "============================="
1816
1817}
1818
1819################################
1820# Process the Functions #
1821################################
1822
1823if ($FirstRun -eq $True) {
1824 $DailyRun = $False
1825 CreateAdamjHeader
1826 Write-Host "Executing RemoveWSUSDrivers"
1827 RemoveWSUSDrivers -SQL
1828 Write-Host "Executing DeclineSupersededUpdates"
1829 if ($AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Host "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
1830 Write-Host "Executing CleanUpWSUSSynchronizationLogs"
1831 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
1832 Write-Host "Executing WSUSDBMaintenance"
1833 WSUSDBMaintenance
1834 Write-Host "Executing WSUSServerCleanupWizard"
1835 WSUSServerCleanupWizard
1836 CreateAdamjFooter
1837 AdamjScriptDifferenceInTime
1838 CreateBodyTXT
1839 CreateBodyHTML
1840 SaveReport
1841 MailReport
1842}
1843if ($MonthlyRun -eq $True) {
1844 $DailyRun = $False
1845 CreateAdamjHeader
1846 Write-Host "Executing DeclineSupersededUpdates"
1847 if ($AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Host "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
1848 Write-Host "Executing CleanUpWSUSSynchronizationLogs"
1849 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
1850 Write-Host "Executing WSUSDBMaintenance"
1851 WSUSDBMaintenance
1852 Write-Host "Executing WSUSServerCleanupWizard"
1853 WSUSServerCleanupWizard
1854 CreateAdamjFooter
1855 AdamjScriptDifferenceInTime
1856 CreateBodyTXT
1857 CreateBodyHTML
1858 MailReport
1859}
1860if ($QuarterlyRun -eq $True) {
1861 $DailyRun = $False
1862 CreateAdamjHeader
1863 Write-Host "Executing DeclineSupersededUpdates"
1864 if ($AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Host "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
1865 Write-Host "Executing CleanUpWSUSSynchronizationLogs"
1866 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
1867 Write-Host "Executing RemoveWSUSDrivers"
1868 RemoveWSUSDrivers
1869 Write-Host "Executing RemoveDeclinedWSUSUpdates"
1870 RemoveDeclinedWSUSUpdates -Display -Proceed
1871 Write-Host "Executing WSUSDBMaintenance"
1872 WSUSDBMaintenance
1873 Write-Host "Executing WSUSServerCleanupWizard"
1874 WSUSServerCleanupWizard
1875 CreateAdamjFooter
1876 AdamjScriptDifferenceInTime
1877 CreateBodyTXT
1878 CreateBodyHTML
1879 MailReport
1880}
1881if ($ScheduledRun -eq $True) {
1882 $DailyRun = $False
1883 $DateNow = Get-Date
1884 CreateAdamjHeader
1885 if ($AdamjScheduledRunStreamsDay -gt 31 -or $AdamjScheduledRunStreamsDay -eq 0) { write-host 'You failed to set a valid value for $AdamjScheduledRunStreamsDay. Setting to 31'; $AdamjScheduledRunStreamsDay = 31 }
1886 Write-Host "Executing DeclineSupersededUpdates"
1887 if ($AdamjScheduledRunStreamsDay -eq $DateNow.Day) { if ($AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Host "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."} } else { if ($AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates } else { Write-Host "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."} }
1888 Write-Host "Executing CleanUpWSUSSynchronizationLogs"
1889 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
1890 $AdamjScheduledRunQuarterlyMonths.Split(",") | ForEach-Object {
1891 if ($_ -eq $DateNow.Month) {
1892 if ($_ -eq 2) {
1893 if ($AdamjScheduledRunStreamsDay -gt 28 -and [System.DateTime]::isleapyear($DateNow.Year) -eq $True) { $AdamjScheduledRunStreamsDay = 29 }
1894 else { $AdamjScheduledRunStreamsDay = 28 }
1895 }
1896 if (4,6,9,11 -contains $_ -and $AdamjScheduledRunStreamsDay -gt 30) { $AdamjScheduledRunStreamsDay = 30 }
1897 if ($AdamjScheduledRunStreamsDay -eq $DateNow.Day) {
1898 Write-Host "Executing RemoveWSUSDrivers"
1899 RemoveWSUSDrivers
1900 Write-Host "Executing RemoveDeclinedWSUSUpdates"
1901 RemoveDeclinedWSUSUpdates -Display -Proceed
1902 }
1903 }
1904 }
1905 Write-Host "Executing WSUSDBMaintenance"
1906 if ($AdamjScheduledRunStreamsDay -eq $DateNow.Day) { WSUSDBMaintenance } else { WSUSDBMaintenance -NoOutput }
1907 Write-Host "Executing WSUSServerCleanupWizard"
1908 WSUSServerCleanupWizard
1909 CreateAdamjFooter
1910 AdamjScriptDifferenceInTime
1911 CreateBodyTXT
1912 CreateBodyHTML
1913 MailReport
1914}
1915
1916if ($DailyRun -eq $True) {
1917 CreateAdamjHeader
1918 Write-Host "Executing DeclineSupersededUpdates"
1919 if ($AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates } else { Write-Host "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."}
1920 Write-Host "Executing CleanUpWSUSSynchronizationLogs"
1921 if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime }
1922 Write-Host "Executing WSUSDBMaintenance"
1923 WSUSDBMaintenance -NoOutput
1924 Write-Host "Executing WSUSServerCleanupWizard"
1925 WSUSServerCleanupWizard
1926 CreateAdamjFooter
1927 AdamjScriptDifferenceInTime
1928 CreateBodyTXT
1929 CreateBodyHTML
1930 MailReport
1931}
1932
1933if ($FirstRun -eq $False -and $MonthlyRun -eq $False -and $QuarterlyRun -eq $False -and $ScheduledRun -eq $False -and $DailyRun -eq $False) {
1934 CreateAdamjHeader
1935 if ($RemoveWSUSDriversSQL -eq $True) { Write-Host "Executing RemoveWSUSDrivers using SQL"; RemoveWSUSDrivers -SQL }
1936 if ($RemoveWSUSDriversPS -eq $True) { Write-Host "Executing RemoveWSUSDrivers using Powershell"; RemoveWSUSDrivers }
1937 if ($RemoveDeclinedWSUSUpdates -eq $True) { Write-Host "Executing RemoveDeclinedWSUSUpdates"; RemoveDeclinedWSUSUpdates -Display -Proceed }
1938 if ($WSUSDBMaintenance -eq $True) { Write-Host "Executing WSUSDBMaintenance"; WSUSDBMaintenance }
1939 if ($DeclineSupersededUpdates -eq $True) { Write-Host "Executing DeclineSupersededUpdates"; if ($AdamjWSUSServerAdminProxy.GetConfiguration().IsReplicaServer -eq $False) { DeclineSupersededUpdates -Display -Proceed } else { Write-Host "This WSUS Server is a Replica Server. You can't decline superseded updates from a replica server. Skipping this stream."} }
1940 if ($CleanUpWSUSSynchronizationLogs -eq $True) { Write-Host "Executing CleanUpWSUSSynchronizationLogs"; if ($AdamjCleanUpWSUSSynchronizationLogsAll -eq $True) { CleanUpWSUSSynchronizationLogs -All } else { CleanUpWSUSSynchronizationLogs -ConsistencyNumber $AdamjCleanUpWSUSSynchronizationLogsConsistencyNumber -ConsistencyTime $AdamjCleanUpWSUSSynchronizationLogsConsistencyTime } }
1941 if ($WSUSServerCleanupWizard -eq $True) { Write-Host "Executing WSUSServerCleanupWizard"; WSUSServerCleanupWizard }
1942 CreateAdamjFooter
1943 AdamjScriptDifferenceInTime
1944 CreateBodyTXT
1945 CreateBodyHTML
1946 if ($SaveReport -eq "TXT") { SaveReport }
1947 if ($SaveReport -eq "HTML") { SaveReport -ReportType "HTML" }
1948 if ($MailReport -eq "HTML") { MailReport }
1949 if ($MailReport -eq "TXT") { MailReport -MessageContentType "TXT" }
1950}
1951
1952if ($HelpMe -eq $True) {
1953 HelpMe
1954}
1955
1956<#
1957# All Possible Function Calls
1958
1959CreateAdamjHeader
1960RemoveWSUSDrivers -SQL
1961 RemoveWSUSDriversSQL
1962 RemoveWSUSDriversPS
1963RemoveDeclinedWSUSUpdates -Display -Proceed
1964 RemoveDeclinedWSUSUpdatesProceed
1965 RemoveDeclinedWSUSUpdatesDisplayUpdates
1966 RemoveDeclinedWSUSUpdatesCountUpdates
1967WSUSDBMaintenance -NoOutput
1968DeclineSupersededUpdates -Display -Proceed
1969 DeclineSupersededUpdatesProceed
1970 DeclineSupersededUpdatesDisplayUpdates
1971 DeclineSupersededUpdatesCountUpdates
1972CleanUpWSUSSynchronizationLogs -ConsistencyNumber "14" -ConsistencyTime "Day" -All
1973WSUSServerCleanupWizard
1974CreateAdamjFooter
1975CreateBodyTXT
1976CreateBodyHTML
1977SaveReport -ReportType
1978MailReport -MessageContentType
1979HelpMe
1980#>
1981
1982
1983################################
1984# Clean Up Variables #
1985################################
1986
1987Get-Variable | Where-Object { $_.Name -match "Adamj" } | Remove-Variable
1988
1989################################
1990# End Of Code #
1991################################
1992
1993<#
1994################################
1995# For Future Additions to Code #
1996################################
1997
1998Streamline the functions to be more consistent.
1999
2000Add a powershell function to create scheduled task.
2001
2002
2003In more than how many days that computers have not synced with the WSUS Server
2004$AdamjComputerSearchDays = 30
2005$AdamjComputerSearchTimeSpan = new-object TimeSpan($AdamjComputerSearchDays,0,0,0)
2006$computerScope = new-object Microsoft.UpdateServices.Administration.ComputerTargetScope
2007$computerScope.ToLastSyncTime = [DateTime]::UtcNow.Subtract($AdamjComputerSearchTimeSpan)
2008$AdamjWSUSServerAdminProxy.GetComputerTargets($computerScope) | sort fulldomainname | ft fulldomainname,lastsynctime
2009
2010If you wish to remove them then simply change the last line to:
2011$AdamjWSUSServerAdminProxy.GetComputerTargets($computerScope) | foreach-object {$_.Delete();}
2012
2013# you would have to get the desired TimeSpan values to the Format operator
2014'{0:00}:{1:00}:{2:00}' -f $elapsed.Hours, $elapsed.Minutes, elapsed.Seconds
2015'{0:00}:{1:00}:{2:00}' -f ($elapsed | % {$_.Hours, $_.Minutes, $_.Seconds})
2016
2017#(Get-CimInstance Win32_OperatingSystem) | Select-Object -Property *
2018
2019#>
2020#EOF