I am trying to create a Top 10 Availability SWQL Query for Nodes in a group.
Currently the following query takes about 2 min to run or times out:
SELECT Top 10 Nodes.Caption, Round(AVG(Nodes.ResponseTimeHistory.Availability), 3) as [Availability]
FROM Orion.Groups AS Groups
JOIN Orion.Nodes AS Nodes ON Nodes.Uri = Groups.Members.MemberUri
WHERE Groups.DisplayName = 'Branch Routers' AND Nodes.ResponseTimeHistory.DateTime >= ADDDAY(-7, GETDATE())
GROUP BY Nodes.Caption
ORDER BY Availability
The query without the group only takes a couple seconds to run:
SELECT Top 10
Nodes.Caption as [Source],
'/Orion/images/StatusIcons/small-' + ToString(Nodes.StatusIcon) AS [_IconFor_Source],
'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a'+ToString(Nodes.NodeID) AS [_LinkFor_Source],
Concat(Round(AVG(Nodes.ResponseTimeHistory.Availability), 3), '%') as [Availability]
FROM Orion.Nodes AS Nodes
WHERE Nodes.CustomProperties._DeviceType = 'Branch Router'
AND Nodes.ResponseTimeHistory.DateTime >= ADDDAY(-30, GETDATE())
AND Nodes.Status <> 9
GROUP BY Nodes.Caption, Nodes.StatusIcon, Nodes.NodeID
ORDER BY AVG(Nodes.ResponseTimeHistory.Availability)
I do not have too much experience with SQL or SWQL and do not know why the once query is taking so much longer to run to get the same information but filtering using the group name instead of a custom property. I appreciate any help.
