SWQL query times out referencing Interface Errors table

robert777

SWQL query to pull router metrics.

Query A (below) runs fairly quickly from Solarwinds

But if I add even a single reference to the Errors table, like i.Errors.InErrors, the query times out and does not return any values (I have set the timeout in SWQL Studio to 5 minutes)  Query B below:.

Can anyone offer ideas as to why a single reference to the Interface errors table causes my query to never finish?  I experience the same behavior when I JOIN the InterfaceErrors table with standard SQL syntax.

Thanks in advance for any assistance.

QUERY A

SELECT DISTINCT n.Caption AS DeviceName, i.FullName AS InterfaceName, t.DateTime, t.InAveragebps, t.InMaxbps, t.InMinbps, t.OutAveragebps, t.OutMaxbps, t.OutMinbps, t.Averagebps AS AvgTotalBps, t.InPercentUtil, t.OutPercentUtil FROM Orion.NPM.Interfaces as i INNER JOIN Orion.NPM.InterfaceTraffic as t ON i.InterfaceID=t.InterfaceID INNER JOIN Orion.Nodes n ON i.NodeID = n.NodeID WHERE n.Caption LIKE '[A]%' AND n.Caption LIKE '%INTRTR%' AND t.DateTime >= DATETRUNC('day', GETUTCDATE() - 1) AND t.DateTime < DATETRUNC('day', GETUTCDATE()) AND (HOUR(t.DateTime) BETWEEN 6 AND 11) ORDER BY N.Caption, I.FullName, t.DateTime 

QUERY B 

SELECT DISTINCT n.Caption AS DeviceName, i.FullName AS InterfaceName, t.DateTime,

t.InAveragebps, t.InMaxbps, t.InMinbps, t.OutAveragebps, t.OutMaxbps, t.OutMinbps, t.Averagebps AS AvgTotalBps, i.Errors.InErrors, t.InPercentUtil, t.OutPercentUtil FROM Orion.NPM.Interfaces as i INNER JOIN Orion.NPM.InterfaceTraffic as t ON i.InterfaceID=t.InterfaceID INNER JOIN Orion.Nodes n ON i.NodeID = n.NodeID WHERE n.Caption LIKE '[A]%' AND n.Caption LIKE '%INTRTR%' AND t.DateTime >= DATETRUNC('day', GETUTCDATE() - 1) AND t.DateTime < DATETRUNC('day', GETUTCDATE()) AND (HOUR(t.DateTime) BETWEEN 6 AND 11) ORDER BY N.Caption, I.FullName, t.DateTime

sum_giais

Hello @robert777, try your query with these adjustments below. Basically removed the 2 INNER JOINS as they're not necessary. All this data is accessible through navigational properties in SWQL (that does the join work for you.) The DISTINCT is also unnecessary as every row is going to be unique (they're timestamped) so that's just adding extra stress on the API for the query with little gain.

That said, this is an expensive query... and could take some time (but should finish) under normal conditions.

SELECT 
     i.Node.Caption
    ,i.FullName
    ,i.Traffic.DateTime
    ,i.Traffic.InAveragebps
    ,i.Traffic.InMaxbps
    ,i.Traffic.InMinbps
    ,i.Traffic.OutAveragebps
    ,i.Traffic.OutMaxbps
    ,i.Traffic.OutMinbps
    ,i.Traffic.Averagebps
    ,i.Traffic.InPercentUtil
    ,i.Traffic.OutPercentUtil
    ,i.Traffic.Errors.InErrors
    ,i.Traffic.Errors.OutErrors
FROM Orion.NPM.Interfaces AS i
WHERE i.Node.Caption LIKE '%' 
AND i.Node.Caption LIKE '%' 
AND i.Traffic.DateTime >= DATETRUNC('day', GETUTCDATE() - 1) 
AND i.Traffic.DateTime < DATETRUNC('day', GETUTCDATE()) 
AND (HOUR(i.Traffic.DateTime) BETWEEN 6 AND 11) 
ORDER BY i.Node.Caption, i.FullName, i.Traffic.DateTime