So, who here is using WSUS?
What happens if you create a win10 virtual machine, and under computer properties name it WSUSTEST or TESTWSUS … ?
Does the WSUS server pick it up?
Found wsus on server2016 have better performance with 16Gb RAM than with 4Gb RAM.
And said missing VM… still no happy lucky.
I have two Server2016 VMs with WSUS installed, but for two different domains.
I needed to clear out the synchronization logs, and installed the latest SSMS on both.
One VM I could login and delete the synchronization logs without any issues.
The second denied me access when trying to log in with SSMS in order to run a SWL script to clear out the logs. Reinstalled the WSUS role, still no luck.
Found that I need to run SSMS as administrator.
Doh.
Will look a bit closer at why this is so.
Another thing that I’ve noticed is if you add products snd classifications one by one, and let WSUS synchronize and download the uodates for said product first before adfing other products, it doesn’t fall over easily.
But if you try to download a dozen of everything, it tend to fall over more often.
So good so far.
Every week I do a cleanup on the WSUS DB, will have to see if I can automate this.
1 Like
For as much as computers multitask, sometimes having them do things one at a time, or one group of related tasks at a time, can still be the best way to get the results you need.
1 Like
Automated script completed.
Works as designed. I have decided to keep to a n00b programming style and not do fancy glitzy stuff, as it is easier to learn with n00b programming styles than using allsorts of fancy trix.
Also, easier to modify and tinker with.
Will post it tomorrow.
1 Like
As promised.
Create three folders on your C: drive
C:\Reports
C:\Scripts
C:\sqlscrips
Drop this into c:\scripts\wsuscleanup.ps1
#Run this script with administrative privileges
$reportpath = "C:\reports\wsuscleanup $(get-date -f yyy-MM-dd).htm"
if((test-path $reportpath) -like $false)
{
new-item $reportpath -type file
}
$report = $reportpath
Clear-Content $report
# Formats report document
Add-Content $report "<html>"
Add-Content $report "<head>"
Add-Content $report "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $report '<title>WSUS cleanup script</title>'
add-content $report '<STYLE TYPE="text/css">'
add-content $report "<!--"
add-content $report "td {"
add-content $report "font-family: Tahoma;"
add-content $report "font-size: 11px;"
add-content $report "border-top: 1px solid #999999;"
add-content $report "border-right: 1px solid #999999;"
add-content $report "border-bottom: 1px solid #999999;"
add-content $report "border-left: 1px solid #999999;"
add-content $report "padding-top: 0px;"
add-content $report "padding-right: 0px;"
add-content $report "padding-bottom: 0px;"
add-content $report "padding-left: 0px;"
add-content $report "}"
add-content $report "body {"
add-content $report "margin-left: 5px;"
add-content $report "margin-top: 5px;"
add-content $report "margin-right: 0px;"
add-content $report "margin-bottom: 10px;"
add-content $report ""
# add-content $report "table {"
# add-content $report "border: thin solid #000000;"
# add-content $report "}"
add-content $report "-->"
add-content $report "</style>"
Add-Content $report "</head>"
Add-Content $report "<body>"
add-content $report "<table width='100%'>"
add-content $report "<tr bgcolor='LightBlue'>"
add-content $report "<td colspan='7' height='25' align='center'>"
add-content $report "<font face='tahoma' color='#003399' size='4'><strong>WSUS cleanup script</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
$action = Get-WsusServer "esawsus01" -Port 8530 | Invoke-WsusServerCleanup -CleanupObsoleteUpdates
if($?)
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='LightGreen'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>$action</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
else
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='Red'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>Obsolete updates cleanup failed</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
$action = Get-WsusServer "esawsus01" -Port 8530 | Invoke-WsusServerCleanup -CleanupObsoleteComputers
if($?)
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='LightGreen'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>$action</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
else
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='Red'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>Obsolete computers cleanup failed</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
$action = Get-WsusServer "esawsus01" -Port 8530 | Invoke-WsusServerCleanup -CleanupUnneededContentFiles
if($?)
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='LightGreen'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>$action</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
else
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='Red'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>Obsolete contents cleanup failed</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
$action = Get-WsusServer "esawsus01" -Port 8530 | Invoke-WsusServerCleanup -DeclineSupersededUpdates
if($?)
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='LightGreen'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>$action</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
else
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='Red'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>Obsolete updates cleanup failed</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
$action = Get-WsusServer "esawsus01" -Port 8530 | Invoke-WsusServerCleanup -DeclineExpiredUpdates
if($?)
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='LightGreen'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>$action</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
else
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='Red'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>Expired updates cleanup failed</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
$action = Get-WsusServer "esawsus01" -Port 8530 | Invoke-WsusServerCleanup -CompressUpdates
if($?)
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='LightGreen'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>$action</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
else
{
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='Red'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>Compress updates cleanup failed</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
}
# cleans up number of synchronizations
# you will need to adjust the ServerInstance if you use MSSQL server instead of Windows Internal Database.
# This script assumes WID is used as database.
$ScriptPath = "c:\sqlscripts"
Invoke-Sqlcmd -ServerInstance "\\.\pipe\microsoft##WID\tsql\query" -InputFile "$scriptPath\delsync.sql" -verbose -OutputSqlErrors $true
add-content $report "<table width='25%'>"
Add-Content $report "<tr bgcolor='LightGreen'>"
add-content $report "<td colspan='7' height='25' align='centre'>"
add-content $report "<font face='tahoma' color='#003399' size='2'><strong>Synchronizations count reset</strong></font>"
add-content $report "</td>"
add-content $report "</tr>"
add-content $report "</table>"
# emails the report
# you can use any smtp method you'd prefer, just get the wee beastie to email $report.
# or you can SFTP $report to a server of your choice
# or you can open a web browser instead
# totally your choice
$smtphost = "your.smtp.host"
$from = "from@company.com"
$to = "clever.admin@company.com"
$timeout = "60"
$subject = "WSUS Cleanup"
$body = Get-Content $report
$smtp= New-Object System.Net.Mail.SmtpClient $smtphost
$msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body
$msg.isBodyhtml = $true
$smtp.send($msg)Drop this into c:\sqlscrips\delsync.sql
USE SUSDB;
GO
DELETE FROM tbEventInstance WHERE EventNamespaceID = '2' AND EVENTID IN ('381', '382', '384', '386', '387', '389')Run the main script manually, and check for any errors. If no errors, then you can automate this task with task scheduler.
NOTE : If you have issues cleaning out a specific WSUS record, and it bombs out, the recommendation is to uninstall WSUS completely, and do a fresh install, and then to run this script on a regular (daily/weekly) basis.
PROTIP : I have found that if you do synchronization on a new WSUS server, do not choose a plethora of products and classifications. Rather, choose one product and classification (eg Windows - all Windows 10 stuff only), let WSUS complete synchronization and update, set up a rule for that specific product, and when that’s finished, select another product (eg Server 2016 - all Server 2016 classifications).
This way you will not have an unhappy WSUS server flaking out due to the amount of updates it have to process.
1 Like
This is good stuff! As someone that used to struggle with WSUS being very slow/flaky/running out of disk space all the time, I can really get behind this - but it comes about 1.5 years too late. 
(Currently I use AJTek’s WSUS Automated Maintenance which is extremely efficient and keeps our WSUS server purring.)
Sometime WSUS refuse to properly clean up obsolete items.
To perform a cleanup of obsolete items via SSMS (SQL Server Management Studio) the following scripts can be entered against the WSUS database :
This is an unofficial entry, but it speeds up DB processing on WSUS
USE [SUSDB]
GO
CREATE NONCLUSTERED INDEX [IX_tbRevisionSupersedesUpdate] ON [dbo].tbRevisionSupersedesUpdate
GO
CREATE NONCLUSTERED INDEX [IX_tbLocalizedPropertyForRevision] ON [dbo].tbLocalizedPropertyForRevision
GO
Now, the cleanup script itself does the job properly, but it may take quite a while to run, so patience is needed.
Remember, this line
IF @curitem < 101
can be changed. Start initially with 50 items, and scale it up to 101 if the server can take the load. Ideally run batches that don’t take more than 5-10 minutes to complete, in order to prevent the SQL transaction logs from growing.
If you do want to run a larger batch that may take hours, you should of course stop the WSUS services to do so. Also, don’t run this script if a WSUS Sync is in progress or scheduled to start.
Best practice is to disable all synchronizations before running this script, and stop the WSUS services.
USE SUSDB
DECLARE @var1 INT, @curitem INT, @totaltodelete INT
DECLARE @msg nvarchar(200)
CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)
EXEC spGetObsoleteUpdatesToCleanup
SET @totaltodelete = (SELECT COUNT(*) FROM #results)
SELECT @curitem=1
DECLARE WC Cursor FOR SELECT Col1 FROM #results
OPEN WC
FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
BEGIN SET @msg = cast(@curitem as varchar(5)) + ‘/’ + cast(@totaltodelete as varchar(5)) + ': Deleting ’ + CONVERT(varchar(10), @var1) + ’ ’ + cast(getdate() as varchar(30))
RAISERROR(@msg,0,1) WITH NOWAIT
EXEC spDeleteUpdate @localUpdateID=@var1
SET @curitem = @curitem +1
IF @curitem < 101
FETCH NEXT FROM WC INTO @var1
END
CLOSE WC
DEALLOCATE WC
DROP TABLE #results
If, for some reason this script get interrupted, just run DROP TABLE #results and restart the script.
Original script and write-up can be found here.
Previously I wasted time by doing a WSUS cleanup and reinstall, but this script saved me the hassles of that.