I'm trying to create a report that shows the last date a patched was deployed to a computer and successfully installed. Is it possible to get this information?
Can you clarify what your looking for in a report, e.g.:
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.
Option 1:
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?
Hello Mikelbeck,
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:
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).
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.