Someone asked for a node up report and I came up with this one. The person asked for the ability to search for vendor as well as node name and IP address. And duration in a easy to read format.
SELECT
n.Caption AS [Node Name]
,DetailsUrl AS [_LinkFor_Node Name]
,'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name]
,n.IP_Address as IP_Address
,DetailsUrl AS [_LinkFor_IP_Address]
,'/NetPerfMon/Images/Vendors/' + n.VendorIcon as [_IconFor_IP_Address]
,tostring(tolocal(n.LastBoot)) as LastBoot
,CONCAT(HOURDIFF(tolocal(n.LastBoot),getdate())/24,' Day(s) ',
HOURDIFF(tolocal(n.LastBoot),getdate())-(HOURDIFF(tolocal(n.LastBoot),getdate())/24)*24,'h ',
MINUTEDIFF(tolocal(n.LastBoot),getdate())-(MINUTEDIFF(tolocal(n.LastBoot),getdate())/60)*60,'m') AS Duration
FROM Orion.Nodes n
WHERE n.LastBoot IS NOT NULL
and
(n.caption like '%${SEARCH_STRING}%'
or n.ip_address like '%${SEARCH_STRING}%'
or n.vendor like '%${SEARCH_STRING}%')
ORDER BY MINUTEDIFF(tolocal(n.LastBoot),getdate()) desc
Thanks
Amit
