API tools faq
paste
djl236

Clean-WSUSv4

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