Help converting SQL to SWIS

Here's the minimal change to make it SWQL:

SELECT  TOP 10000 Nodes.Caption AS NodeName,

Case InBandwidth

            When 0 Then 0

            Else ((InAveragebps/InBandwidth) * 100)

            End AS Recv_Percent_Utilization,

Case OutBandwidth

            When 0 Then 0

            Else ((OutAveragebps/OutBandwidth) * 100)

            End AS Xmit_Percent_Utilization,

Interfaces.Caption AS Interface_Caption,

InterfaceTraffic.DateTime AS DateTime,

Interfaces.InterfaceID AS InterfaceID

FROM

Orion.Nodes INNER JOIN Orion.NPM.Interfaces ON (Nodes.NodeID = Interfaces.NodeID)  INNER JOIN Orion.NPM.InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

WHERE

( DateTime BETWEEN '2018-11-04 14:00:00' AND '2018-11-05 15:00:00' )

AND

(

  (Nodes.Caption LIKE 'rtr1.dc5024%') AND

  (Interfaces.Caption LIKE '%AT&T%') AND

  (Nodes.CustomProperties.area = 'remote_distributioncenter_rdc')

)

I changed:

* FROM Nodes -> FROM Orion.Nodes

* INNER JOIN Interfaces -> INNER JOIN Orion.NPM.Interfaces

* INNER JOIN InterfacesTraffic -> INNER JOIN Orion.NPM.InterfaceTraffic

* In_Averagebps -> InAveragebps

* Out_Averagebps -> OutAveragebps

* Remove (parenthesis) around FROM clause

* Replace numeric dates with string dates in WHERE clause

* Nodes.area -> Nodes.CustomProperties.area

I get an error and I simplified the query but still get the same error. 

1)Here is the simplified SQL query:

SELECT  TOP 10000 Nodes.NodeID AS NodeID,

Nodes.DNS AS DNS,

Interfaces.InterfaceID AS InterfaceID,

Case InBandwidth

            When 0 Then 0

            Else (In_Averagebps/InBandwidth) * 100

            End AS Recv_Percent_Utilization,

Case OutBandwidth

            When 0 Then 0

            Else (Out_Averagebps/OutBandwidth) * 100

            End AS Xmit_Percent_Utilization

FROM

(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

WHERE

( DateTime BETWEEN 43409.4166666667 AND 43410.4583333333 )

2)Here is my attempt with SWIS:

SELECT TOP 100000 Orion.nodes.NodeID, Orion.Nodes.DNS, Orion.NPM.Interfaces.InterfaceID

FROM Orion.Nodes INNER JOIN Orion.NPM.Interfaces ON (Orion.Nodes.NodeID = Orion.NPM.interfaces.NodeID) INNER JOIN Orion.NPM.Interfaces.Traffic ON (Orion.NPM.Interfaces.InterfaceID = Orion.NPM.InterfaceTraffic.InterfaceID AND Orion.NPM.InterfaceTraffic.NodeID = Orion.Nodes.NodeID)

( DateTime BETWEEN '2018-11-06 14:00:00' AND '2018-11-07 15:00:00' )

3)Here is the error:

That syntax error looks to be due to a missing "WHERE" before the (DateTime BETWEEN ...) part. Adding that highlights the next problem which is that you should only use the namespace qualifier ("Orion." or "Orion.NPM.") when referencing an entity after FROM or JOIN, not in the SELECT, ON, WHERE, or other clauses. In those contexts, just use the entity name (or alias, if you set one like "FROM Orion.Nodes AS N"). Here's a working version of the query:

SELECT TOP 100000 nodes.NodeID, Nodes.DNS, Interfaces.InterfaceID

FROM Orion.Nodes

INNER JOIN Orion.NPM.Interfaces ON (Nodes.NodeID = interfaces.NodeID)

INNER JOIN Orion.NPM.InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

WHERE ( DateTime BETWEEN '2018-11-06 14:00:00' AND '2018-11-07 15:00:00' )

But we can also take advantage of a feature in SWQL that SQL doesn't have: navigation properties. These are shortcuts for common joins. This query is exactly equivalent:

SELECT TOP 100000 N.NodeID, N.DNS, N.Interfaces.InterfaceID

FROM Orion.Nodes AS N

WHERE ( N.Interfaces.Traffic.DateTime BETWEEN '2018-11-06 14:00:00' AND '2018-11-07 15:00:00' )

Ok thanks.  I did have an obvious syntax problem.

SELECT TOP 100000 nodes.NodeID, Nodes.DNS, Interfaces.InterfaceID

FROM Orion.Nodes

INNER JOIN Orion.NPM.Interfaces ON (Nodes.NodeID = interfaces.NodeID)

INNER JOIN Orion.NPM.InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

WHERE ( DateTime BETWEEN '2018-11-06 14:00:00' AND '2018-11-07 15:00:00' )

Here is what I get when I run this query:

Is the syntax error telling me it does not like the timestamp format?

I copied the time format straight from the database and I get the same error:

1)Database query output:

2)SWIS error:

No, it is telling you it does not like the BETWEEN keyword. This was only added to SWQL in the Orion Platform 2018.2 release (NPM 12.3). For earlier versions you can use something like this instead:

WHERE ( DateTime >= '2018-11-06 14:00:00' AND DateTime <= '2018-11-07 15:00:00' )

We coordinated a SDK training session through our sales rep which was extremely helpful.  Kudos to Tim D. from Solarwinds.  He showed us a query that did not utilize a join to pull metric data.  Here is the query:

SELECT TOP 100 I.Node.Caption AS NodeName, I.Caption, DATETRUNC('hour',I.Traffic.ObservationTimestamp) AS Time, AVG(I.Traffic.InPercentUtil) AS InPct, AVG(I.Traffic.OutPercentUtil) AS OutPct

FROM Orion.NPM.Interfaces I

WHERE I.Node.Caption LIKE 'rtr1.dc%' AND I.Traffic.ObservationTimestamp > GETUTCDATE () -2

GROUP BY I.Node.Caption, I.Caption, DATETRUNC('hour',I.Traffic.ObservationTimestamp)

ORDER BY Time DESC