Clean up WSUS synchronization reporting with SQL script

Okay, this one isn't about PowerShell.  But it's a script, and it's for server admins, not database admins, so I'm going to post it here anyway.


When you go to the Synchronizations page of the WSUS console, it takes a long time to load.  If you have been using it for years, it takes a really, really long time to load.  If you have cranked up synchronization frequency, forget it.  Don't even try.  It's a useless report.  Well, it's a useful report, but you can't ever use the report, because it won't load in any reasonable amount of time.

In my environment, we use SCCM to deploy and manage ForeFront Endpoint Protection, but nothing else.  All patching still goes through WSUS, including definition updates for Endpoint Protection.  To be sure we get virus dates a soon as they come out, including and especially emergency out-of-band updates, we synchronize every hour.

So we have lots and lots of synchronizations, and can't use the report unless we clean up the database.

The information displayed in the report is a simple, flat table, summarizing synchronization results.  It would take a trivial amount of disk space to store this summarized data in a table for easy reference.  If it was stored in this format, the amount of CPU and disk I/O and user wait time required to retrieve it would be too small to measure.

Unfortunately, it wasn't somebody's job on the Microsoft WSUS team to care about any of that.  Instead, through many generations of WSUS, they have continued to use an algorithm that is, well, insane.  They query the event table for "started synchronization" events.  Then they find all of the corresponding "finished synchronization" events.  Then, for every single one of those synchronizations, they query the revision table to count up the number of revisions that were received during that time period.

They run a query for each and every line of the report, one for each of the thousands of synchronizations.  And they repeat the whole thing every time you open or refresh the report.

I have seen other scripts that you can run from time to time to delete all synchronizations, if you want to truncate your synchronization history, but if you delete them all before you can look at the report, then you have a blank report, so that doesn't help us much here.

I don't care about synchronizations that found nothing new.  And I don't care about synchronizations that find nothing but virus updates.  (I like to see all of today's synchronizations, so that I can see that they are happening, but I don't need to see older, trivial synchronizations.

So I have created a SQL job that runs at 2 AM every day, and runs the script below.  (We have our WSUS database in a full version of SQL.)

This script finds all of the synchronizations that received no new updates other than virus updates, and deletes the corresponding "synchronization started" entry from the event table.

--
--  CleanupWSUSSynchronizationHistry.SQL
--
--  Delete All Synchronization Started events from the tbEventInstance table
--    so that they don't show up on the Synchronizations page of the WSUS console.
--    (because that takes forever if there are hundreds of them)
--
Delete From [SUSDB]..[tbEventInstance]
Where EventID in ( 381, 382 )
  And [EventInstanceID] in ( Select [EventInstanceID]
   From (  Select [EventInstanceID],
    [TimeAtServer] As StartTime,
    ( Select Top 1 [TimeAtServer]
     From [SUSDB].[dbo].[tbEventInstance] As I
     Where EventID in ( 384, 386, 387 )
       And I.EventOrdinalNumber > E.EventOrdinalNumber ) As EndTime
    From [SUSDB]..[tbEventInstance] As E
    Where EventID in ( 381, 382 )
      ) As O
   Where ( Select COUNT(*)
    From [SUSDB]..[tbUpdate]
    Where LegacyName is not Null
      And LegacyName not like '2461484_Definition%'
      And [ImportedTime] > O.StartTime
      And [ImportedTime] < O.EndTime ) = 0
   )