WSUS 3.0 Developer's Blog: Reporting - The Good, the Bad, and the Confusing

The information below is somewhat out of date for the final version of the reporting APIs. Functionality in the UI is very much the same as in RC, but the API behavior was changed to make it easier to get correct results. We'll make another blog post soon. -- Matthew

Blog entry from beta2/pre-RC timeframe:

One of the big areas of feedback for the WSUS 3.0 Beta has been around reporting features.  Looks cool, but so far the results haven't been as predictable (or correct) as they should have been.  I'll cover some of the philosophy behind the feature, give some insights on how the APIs used for reporting work, and build all that together into where we went astray for the Beta. 

WSUS has never, to date, provided direct access to our database.  From the customer's perspective, aside from a few options in setup to configure the DB, we might as well be storing information in CSVs.  We've taken that approach to avoid complexity around publishing and maintaining compatibility around our schemas and stored procedures.  Without published support for the schema, you can't do extended reporting with great products like SQL Reporting Services.  Further, remote console administration is hampered by database engines like the system component DB engine that doesn't allow remoting, and remote DB installs introduce double-hop authentication issues.  Instead, our reporting is exposed via our API. 

Two of the key APIs we use for reporting are available from IUpdateServer: GetSummariesPerUpdate(UpdateScope updatesToInclude, ComputerTargetScope computersToInclude) and a similar GetSummariesPerComputerTarget(UpdateScope, ComputerTargetScope)

Looking over the scopes, you'll see that you can do some really powerful things:  Text searches, items that are (or aren't) in particular states, time based searches, et cetera.

As the old saying goes, however, Absolute Power bring Absolute Corruption.  In our case, maybe that's Absolute Confusion.

Say you want to ask a question like: "For every update in my organization with failures, show me how many computers didn't fail for that update."

You might logically start constructing your API call something like this: GetSummariesPerUpdate(), using an update scope with IncludedInstallationStates = Failed, and a computer scope that has ExcludedInstallationStates = Failed.

To understand what happens next, we'll go back to how the GetSummaries calls work.

GetSummariesPerUpdate() starts off by getting all Updates from the DB that have at least one computer in the state you specified in the IncludedInstallationStates.  Put succinctly, that's every update that has at least one computer that reported a failure for that update. 

Then, a list of computers is generated - every computer that meets the computer criteria.  In our example using ExcludedInstallationStates, that's every computer that has no failures for any update.  Remember this point: no failures for any update, not no failures for a particular update in the first list.

Now, for the final trick:  For every update in the first list, we figure out the state (relevant to each update) for every computer in the second list.  Count up the tally of each state, and that's the summaries returned for each update. 

Once again, you've now got a list of summaries for every update that had at least one computer with a failure, and a summarization of computer states only against computers that had no failures at all for any update

If you haven't figured out why this is bad, I'll go over what you might have expected with the initial question. 

Imagine your organization has 2 computers (Ca, and Cb), and 2 updates (U1, and U2).  Both computers successfully installed U1, and both failed U2.

Our scenario wanted "show me all updates with no failures, and a count of states for all computers that didn't fail that update."  You'd expect to see U1 (no failures), with a count of 2 computer successes for that update.

Instead, you'd see U1 (no failures) and 0s in all summary fields.  All of the computers had at least one failure, and so were excluded from being summarized for all updates.  A detailed report in our UI would show no computers at all, just like the summary counts said.

Getting questions like this scenario right can be a little tricky - and often require some post processing.  I'll just say "this is why we have Betas," and is why we're so busy for the RC release.  I think you'll be pleased with the changes.

-- matthew