Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Help converting SQL to SWIS

wlouisharris

We are trying to convert the following query to SWIS and quite frankly having trouble finding examples that utilize joins like this. We built the query using the Orion Report Writer.

Query:

SELECT TOP 10000 Nodes.Caption AS NodeName,

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,

Interfaces.Caption AS Interface_Caption,

InterfaceTraffic.DateTime AS DateTime,

Interfaces.InterfaceID AS InterfaceID

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 43406.5833333333 AND 43407.625 )

AND

(

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

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

(Nodes.area = 'remote_distributioncenter_rdc')

)

It would be really nice to have a SWIS converter as our customers are requesting more and more to pull data from the Solarwinds API without having to write in the SWQL/SWIS langauge.

Find more posts tagged with

Accepted answers

wlouisharris

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

All comments

tdanner

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

wlouisharris

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:

tdanner

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' )

wlouisharris

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?

wlouisharris

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

1)Database query output:

2)SWIS error:

tdanner

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' )

wlouisharris

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