I have seen several Node Down Duration reports which links to the event table to derive the duration. This is a bit limited as the events are stored for just 30 days. The disadvantage is that for down nodes which happened more than the last 30 days would not show up.
A better way would be to link it to the response table. The response time is retained for a year so there are good chances that it will show the down date.
SELECT TOUPPER(SUBSTRING (n.Caption,1, CASE WHEN CHARINDEX('.',n.Caption,1) <=4 THEN LENGTH(n.Caption) ELSE (CHARINDEX('.',n.Caption,1)-1) END)) AS [Node Name]
,n.DetailsUrl AS [_LinkFor_Node Name]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node Name]
,n.IP_Address as IP_Address
,n.DetailsUrl AS [_LinkFor_IP_Address]
,'/NetPerfMon/Images/Vendors/' + n.VendorIcon as [_IconFor_IP_Address]
,case when min(rt.DateTime) is NULL
then 'More than a year'
else concat(substring(tostring(min(rt.DateTime)),6,2),'-',substring(tostring(min(rt.DateTime)),9,2),'-',substring(tostring(min(rt.DateTime)),1,4),' ',substring(tostring(min(rt.DateTime)),12,2),':',substring(tostring(min(rt.DateTime)),15,2))
end as [Date]
,case when min(rt.DateTime) is NULL
then ' More than a year'
else concat((hourDIFF(tolocal(min(rt.DateTime)),getdate())/24), ' Days '
,(Ceiling((HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0-Floor(HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0))*24)),' h '
,(Ceiling((MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0-Floor(MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0))*60),' m'))
end AS Duration
FROM Orion.Nodes n
left join orion.responsetime rt on rt.nodeid=n.nodeid
--LEFT JOIN (SELECT nn.nodeid, count(*) AS drops FROM Orion.nodes nn WHERE (nn.events.NetObjectType='N') and (nn.Events.EventType=1) and (daydiff(nn.Events.EventTime,tolocal(getdate()))=0) group by nn.nodeid) drops on drops.nodeid=n.NodeID
WHERE n.STATUS = 2 and rt.AvgResponseTime is null
--and n.caption like '%${SEARCH_STRING}%' or n.IP_Address like '%${SEARCH_STRING}%'
GROUP BY n.caption, n.StatusIcon, n.DetailsUrl, n.nodeid, n.ip_address, n.VendorIcon
ORDER BY MinuteDiff(tolocal(min(rt.DateTime)),getdate()) desc, n.caption asc
Thank you,
Amit Shah
Loop1 Systems
