IT Management Solutions

13 Apr

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 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.

Asset Inventory

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!

Asset Inventory 2

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).

Operating System Updates Applied

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:

;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

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.

Selection Method

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

;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

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:

....

where cte.rn = 1 and datediff(month,a.installdate,getdate())>3

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.

 

>>>DOWNLOAD EXPORTED REPORT - When Were Your Servers Last Patched<<<

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

 

Training Courses for SolarWinds Customers

Prosperon Networks are the UK's leading authority on SolarWinds IT Management Solutions. We run training courses that suit a number of roles in your organisation, these courses cater for engineers, helpdesk operators, and management personnel who all use monitoring platforms differently. The SolarWinds products retain their simplicity and ease of use, however, product training in some form is recommended to get the most out of the tools we use every day.

>>>Register Course Interest<<<