Report for nodes that have been down for more than 30 days.

Question

I'm trying to create a query to show nodes that have been down for greater than 30 days - Found this old query in a post by @timDanner 
 Current Query in SWQL: SELECT NodeID, MAX(Availability) AS MaxAvailability
FROM Orion.ResponseTime
WHERE DateTime > ADDDAY(-30, GETUTCDATE())
GROUP BY NodeID
HAVING MAX(Availability) = 0 
 The problem is I want to join the Orion.Nodes table so I can add the Node Caption + Node IP Address so that the report can be more meaningful for the network team who are not going to be familiar with the Orion.NodeID as an identifying node in the environment. 
 Any help would be appreciated,

KMSigma.SWI · Answer

Navigation Properties is where you do with this. 
 SELECT [RT].NodeID
 , MAX([RT].Availability) AS MaxAvailability
 , [RT].Node.Caption AS [Node Name]
 , [RT].Node.IPAddress AS [IP]
FROM Orion.ResponseTime AS [RT]
WHERE [RT].DateTime > ADDDAY(-30, GETUTCDATE())
GROUP BY [RT].NodeID
 , [RT].Node.Caption
 , [RT].Node.IPAddress
HAVING MAX([RT].Availability) = 0

Report for nodes that have been down for more than 30 days.

Top Replies