Exported Report: When Were Your Servers Last Patched?

Last week my good friend Dan asked me if he could get a report in SolarWinds® that displays the last time the Windows servers were patched, and, if possible, which the patches were deployed at that time. Now I must point out that Dan does not have the SolarWinds Patch Manager solution, which provides this information natively, oh and the ability to perform all system patching tasks, but Dan does not have Patch Manager. Still, the answer was ‘sure it does, it’s SolarWinds!’

The company Dan works for has an installation of SolarWinds with several modules, including SolarWinds Server and Application Monitor (SAM), which includes a feature named Asset Inventory that tells us the software and hardware configured on the servers monitored in SolarWinds.

This is something cool, for example, you can check if there are DIMM slots available to expand the RAM memory, or check what software is installed or even the serial number and warranty status!

Moreover, if you are using WMI or the agent to monitor that server (sorry SNMP, you’ve let me down), it will also tell you the OS patches installed, including install date. That is what Dan needed, to create a report that shows the OS patches (at least the last one installed).

The thing is, even though the reporting tool in SolarWinds is quite powerful, I couldn’t see a way to get the information Dan wanted in the format he wanted. Therefore, I created a custom SQL query that brings back the required information:

[code];With cte AS
(SELECT *, row_number() over (partition by nodeid ORDER BY installdate DESC) AS rn
FROM assetinventory_osupdates
WHERE installdate IS NOT NULL)

SELECT n.caption, a.name, a.installdate, a.type,
‘/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:’+cast(n.nodeid as varchar(256)) AS [detailsURL]
FROM assetinventory_osupdates a
INNER JOIN nodes n ON n.nodeid=a.nodeid
INNER JOIN cte on cte.nodeid = a.nodeid AND cte.installdate = a.installdate
WHERE cte.rn = 1

ORDER BY 1 asc[/code]

You can use this query as the data source for a custom table on any dashboard of the web console or as a report in SolarWinds. You can also use it with any other type of SQL reporting tool that you might have.

On the page, you wish to display this information, Select Customize page and add the Custom Table resource.

This script could be edited to display just the last time the server was patched, regardless of the patch applied. The following is the replacement query for this purpose:

[code];With cte AS
(SELECT *, row_number() over (partition by nodeid order by installdate DESC) AS rn
FROM assetinventory_osupdates
WHERE installdate IS NOT NULL)

SELECT DISTINCT n.caption, a.installdate,
‘/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:’+cast(n.nodeid as varchar(256)) as
[detailsURL]
FROM odes n
INNER JOIN assetinventory_osupdates a ON n.nodeid=a.nodeid
INNER JOIN cte on cte.nodeid = a.nodeid AND cte.installdate = a.installdate
WHERE cte.rn = 1

ORDER BY 2 desc[/code]

Also, we can use this script to create an alert if a specific server or groups of servers have not been patched for x days or months, by adding this to the where clause:

This will show only servers where last OS patches were applied more than three months ago and you could adjust this WHERE clause to fit in with whatever condition you wish.

Guys, I hope this has been informative for you, and don’t hesitate to contact me for any question or ideas that you may have regarding SolarWinds