Can you clarify what your looking for in a report, e.g.:
- A report for the last time a specific update was deployed to any machine
- A report for a specific machine showing the last time any update was successfully deployed to that machine
- A report for a specific machine showing the last time a specific update was deployed to that machine
A list of all of the computers that Patch Manager knows about and the last time a patch (any patch) was deployed successfully to each.
A couple of options here:
If it is for one machine, you can left-click a machine to select it, then choose the option for Computer Explorer and go to the Windows Update History tab. If that tab is able to pull back data from the machine, you can just filter/group/sort that data to see the latest successful install time.
If it is for any update deployed to any machine, to get that kind of data from all the machines, you'll need to do an Inventory task against the machines. Once you have inventoried the machines you care about, you'd run a report (WSUS Reports -> Windows Update -> Windows Update Agent History).
In the report results, you have some options on how to group/sort the data.
You could group by the Title (windows update history) column header (if you are looking for a particular update) and then expand the update title name that you care about to see all the installation task items for that update. To group that way, grab the column header itself and drag it slightly up to the dark grey area right above the column headers.
or you could group by the Update Operation (Friendly Name), Result Code (Friendly Name) fields in that order and then Sort by the Date (Windows Update History) column. Then you could sort by the Date (Windows Update History) column to see the newest ones first.
That's still giving me more data than I actually need.
What I really need is just the computer name and the date of the last successful patch installation. I don't need any of the detail of the patches themselves. Is it possible to just get that data?
There's multiple different ways to get that information. For one thing, if you're using Update Management or the Update Management Wizard to handle your patching from Patch Manager, you can export the task information to find out which machines were updated and on which date.
That may still be too much info for just the two fields that you're looking for, so you can remove the unnecessary data or you can work with creating a new report (I suggest this because it's probably easier to add two fields rather than delete the unnecessary fields from an existing report).
Here are the fields that I used:
Domain (Update Server)
Name (Update Server)
Computer Name (Computer)
Total Installed (Percent) (Computer)
Last Reported Status Time (Computer)
This is a Windows Server Update Services report and I used the Update Server and Computer data sets (in parenthesis).
It's more fields than you wanted for two reasons:
- PM required the Update Server information to save the report.
- I added the Total Installed Percent so that you could verify the machines are actually updated.
In order to report on successful patching (other than percentages) you need to report on the patches individually. Otherwise you're only able to tell when the computer last checked in and how updated it is (each individual patch has its own counters for date installed and success/failure otherwise).
That's showing the last report date which may be different from the last patch date.
I'm thinking the best way to do this may be to just pull the data from the database directly.
Looks like this query will get the data that I need:
SELECT DISTINCT t1.[Computer Name], [Completion Time]
FROM ewtaskviewhistory t1
WHERE [Completion Time] =
(SELECT MAX([Completion Time]) FROM ewTaskViewHistory WHERE [Computer Name] = t1.[Computer Name]
AND Operation = 'Install'
AND Result = 1)
ORDER BY t1.[Computer Name]
Is there a way to create a report with custom SQL like this or am I stuck with just choosing the pre-defined fields?
There's no simple way to run your query from Patch Manager reports that I'm aware of.
If you wanted to put in the work for it, you could likely export a report to the XDQ format and modify it to include your fields and give that a try, but I've never seen it done, so I'm not 100% sure it would work.
Correct, despite the tantalizing fact that patch manager reports will show you the sql they use to get their reports, you aren't given an option to load in your own SQL.
Thanks for the help with this, everybody.
With the help of your previous query and the DBAs at my job, this query was very helpful:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [Task State], [Operation Result], [Computer Name], [Operation], [Details], MAX([Completed]) AS CompletionTime
WHERE [Operation Result] = 'Success' AND Operation = 'Install'
GROUP BY [Task State], [Operation Result], [Computer Name], [Operation], [Details]
ORDER BY CompletionTime DESC