cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Please help to conver SQL to SWQL


Can anyone help me to convert below SQL query to  SWQL,

Select Nodes.nodeID, Nodes.Caption as NodeName,Nodes.StatusDescription As Status, DATEDIFF(hh, DATEADD(hh,-5, LastSystemUpTimePollUtc), GetDate()) as [Down Time], Convert(varchar, DATEADD(hh,-5, LastSystemUpTimePollUtc),110) As [Last Date Up]

From Nodes
where
Nodes.Status != '1' and
DATEDIFF(Mi, DATEADD(hh,-5, LastSystemUpTimePollUtc), GetDate())>=180
Order by [Down Time] Asc

I am able to fetch the result on web page by using "Select Nodes.nodeID, Nodes.Caption as NodeName,Nodes.StatusDescription As Status From Orion.Nodes"

But i am not able to find right  query for  "Down Time" and  "last date Up"

7 Replies
Level 8

TomJose -

See if this get you what your looking for

::

SELECT AvgResponseTime, MinResponseTime, MaxResponseTime, ResponseTime, PercentLoss, CPULoad, MemoryUsed, PercentMemoryUsed, LastBoot, SystemUpTime, NodeID

FROM Orion.NodesStats

::

Regards,

Peter

0 Kudos
Level 19

How about this?

Select Nodes.nodeID, Nodes.Caption as NodeName, Nodes.StatusDescription As Status,

  HourDiff(LastSystemUpTimePollUtc, GetUtcDate()) as [Down Time],

  ToString(ToLocal(LastSystemUpTimePollUtc)) As [Last Date Up]

From Orion.Nodes

where Nodes.Status != '1' and

  MinuteDiff(LastSystemUpTimePollUtc, GetUtcDate())>=180

Order by [Down Time] Asc

I came across this post trying to convert the exact same thing and your solution works great. Is there any way to make the node names clickable in the chart?

0 Kudos

Where are you using this query? Some places that accept a custom query have ways to make links and some don't.

0 Kudos

I'm using it in a Custom Query resource on a Summary view

0 Kudos

Good, that one makes it easy to add links. Just add "DetailsUrl AS [_LinkFor_NodeName]" to the SELECT clause. Like this:

Select Nodes.nodeID, Nodes.Caption as NodeName, Nodes.StatusDescription As Status,

  HourDiff(LastSystemUpTimePollUtc, GetUtcDate()) as [Down Time],

  ToString(ToLocal(LastSystemUpTimePollUtc)) As [Last Date Up],

DetailsUrl AS [_LinkFor_NodeName]

From Orion.Nodes

where Nodes.Status != '1' and

  MinuteDiff(LastSystemUpTimePollUtc, GetUtcDate())>=180

Order by [Down Time] Asc

Exactly what I needed. Thanks!

0 Kudos