WSUS: Reports are slow or fail with an OutOfMemoryException error
Do you get the occasional OutOfMemoryException error when running reports in WSUS? Are you looking to customize the reporting capabilities. How about improving the overall performance of the reports? If you answered yes to any of these questions then take a look at these tips from Joe Tindale, a Support Escalation Engineer in our WSUS group:
========
Usually there are two kinds of reports people run: The default WSUS reports accessed from the WSUS MMC console and the reporting tools located in the API Samples and Tools:
The reporting tools are:
- ApprovedUpdatesToXML - Creates an XML file with a list of approved updates and details of their approval including the groups that the update is approved for, and the approval action.
- ComputerStatusToXML - Creates an XML file with a list of computers on the WSUS server and the status of the updates approved for each computer. The list can optionally include computers that are rolled up from downstream WSUS servers.
- ListApprovedUpdates - Displays a list of updates that have been approved after a specified date and also creates a text report that lists the updates.
- UpdateStatusToCSV - Creates a simple CSV file with a list of approved updates and a summary of the status of the updates on each computer.
- UpdateStatustoXML - Creates an XML file with a list of updates, with update status for each computer client.
- UpdatesToXML - Creates an XML file with a list of updates and information about each update, including ID, title, description, KB articles etc.
At times you may experience a problem with the reports mentioned above. The default reports created within WSUS are time and memory intensive and if a large number of clients are present on the WSUS server and detailed reports are chosen, the report may fail with an OutOfMemoryException. Our operations guide outlines this problem partly in the reporting section:
Important: Exporting a large report can be extremely time consuming. If you are planning to export your report, consider limiting the size of the report to 200 pages or fewer. You can use different filters to reduce the size of the report, or you can choose the tabular format rather than the detailed format to reduce the number of pages to export.
The reporting tools in the API Samples and Tools utility can be very time consuming as well when dealing with a large number of clients and updates. Some of those tools use a For loop to get a computer object and then within that For loop there is a inner For loop to go through all the updates. It repeats that cycle for all computers so if you have 10,000 clients and 12,000 updates you can see that can take a while. The good thing about the tools is that the source code is given so they can be customized fairly easily.
A faster and more customizable solution is to use the SQL views we export in WSUS3: https://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx
These queries are much more efficient since we are querying the database directly and they can easily be customized. We have two sample queries given in the link above. You can easily modify these queries to fit your specific needs. For example, the following query was taken from the above link but then I changed the computers group to be "all computers" rather than "unassigned computers":
===========
USE SUSDB
DECLARE @securityUpdates uniqueidentifier
DECLARE @unassignedGroup uniqueidentifier
DECLARE @allComputers uniqueidentifier
SET @securityUpdates = '0FA1201D-4330-4FA8-8AE9-B877473B6441'
SET @allComputers = 'a0a08746-4dbe-4a37-9adf-9e7652c0b421'
SELECT
u.UpdateId
, u.DefaultTitle
, usc.State
, COUNT(*)
FROM
PUBLIC_VIEWS.vUpdate AS u
INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfo AS usc ON u.UpdateId =
usc.UpdateId
WHERE
usc.ComputerTargetId IN (
SELECT ComputerTargetId FROM PUBLIC_VIEWS.vComputerGroupMembership WHERE
ComputerTargetGroupID = @allComputers)
GROUP BY
u.UpdateID, u.DefaultTitle, usc.State
=========
Thanks Joe, and for more information on scripts and examples see:
Baseline compliance report, using public WSUS views
and
Finding machines not compliant with a specific security bulletin
J.C. Hornbeck | Manageability Knowledge Engineer