Overwhelmed by report options

FormerMember

Okay, I'm a little overwhelmed by all the reporting options and I do have a decent knowledge of SQL queries but for the life of me I cannot figure out how to generate a report to tell me which computers have critical or severe updates that haven't been patched in the last 30 days or 60 days or 90 days. I can generate a list of machines that have not reported in the last 30/60/90 days but what I am looking for are all of the machines that have a critical or severe patch missing over 30/60/90 days old. This has to be a lot easier than it seems but bear in mind, it is a Monday today.

Thanks for any feedback.

LGarvin

With a couple of minor modifications, the system report "Computer Update Status Approved Updates Not Installed" should get you exactly what you need.

The key is how you identify the updates that are "missing for at least X days", and there are a few different ways you might approach this, depending on your specific need.

1. You can identify "missing updates" based on the day they were Approved for installation.

2. You can identify "missing updates" based on the day they Arrived on the WSUS Server.

3. You can identify "missing updates" based on the day they were Released.

The first step in the process will be to convert the existing report to a new one that uses a richer datasource that contains the above date fields.

1. Select the Windows Server Update Services Analytics reporting group node.

2. In the Available Datasources pane, select "Computer Update Status With Approval Information".

3. In the field list, select one or more of "Release Date", "Arrival Date", or "Approval Creation Date".

4. In the report list, select the report "Computer Update Status Approved Updates Not Installed", right click and select "Edit and Schedule Report". This will generate a dialog warning that multiple  datasources are selected. This is expected and we'll fix that in the next step. Click on OK on the dialog and the Report Definition Builder will open.

5. For the first six fields listed in the top pane, and both of the filter fields in the bottom pane, we need to change the datasource from "Computer Update Status" to "Computer Update Status With Approval Information". For each of those eight fields, right click on the hyperlinked field name (e.g. "Domain or Workgroup"), select the new datasource from the flyout menu (e.g. "Computer Update Status With Approval Information", and select the same field from the flyout fields menu (e.g. "Domain or Workgroup"). Repeat this process for the other seven fields (five in the top; two in the bottom).

6. You'll need to 'fixup' the field values in the filter pane. The field "Update Approval Action" should be set to test for EQUALS 0 and the field "Update Installation State" should be set to test for EQUALS 2. (Once you complete Step 8 you can refer back to the original instance of the System Report to double check these values.)

7. Add a filter for your date value(s). (e.g. "Approval Creation Date 'was not in this month') which would give you any updates approved in previous months that are not yet installed. You could also define an explicit date in the filter using "Approval Creation Date IS LESS THAN <declaredDate>" (which would require you to change the report definition regularly). You can also PROMPT for the date, interactively, at report execution by choosing "Approval Creation Date IS LESS THAN <Prompt for value>", and simply entering the desired oldest approval date when prompted.

8. Click on Next and change the Report Name to match the modifications you've made (e.g. "Computer Update Status Approved Updates Not Installed By Approval Date".