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

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,

Top Replies

KMSigma 20 days ago +1 suggested

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…

KMSigma 17 days ago in reply to shane.horgan +1

Not a concise document, but there are smatterings all around THWACK about it.

I stand corrected. I seem to have written something a while ago about it and I completely forgot: Decreasing JOINs - the Power…

0 KMSigma 20 days ago

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

0 shane.horgan 20 days ago in reply to KMSigma

Thank you for such a quick reply - you have any docs where the Navigation properties are explained?
Cancel
Up 0 Down

Reply

Verify Answer

Cancel
0 KMSigma 17 days ago in reply to shane.horgan

Not a concise document, but there are smatterings all around THWACK about it.

I stand corrected. I seem to have written something a while ago about it and I completely forgot: Decreasing JOINs - the Power of Navigation Properties - Orion SDK - The Orion Platform - THWACK - SolarWinds Community
Cancel
Up +1 Down

Reply

Verify Answer

Cancel