join more than 2 tables using SWQL(SolarWinds Query Language) when there is no common field?

Question

I am monitoring my node using SolarWinds Orion NPM (network performance monitoring). And I am using Orion SDK for getting alert information from SWIS (SolarWinds informarion Service) into vb.net console application.

I am using some vb.net code:

Function GetOneAlert(ByVal swis As InformationServiceClient) As AlertInfo
    Dim query As String, result As XElement
    query = "SELECT TOP 1 A.AlertDefID, A.ActiveObject, A.ObjectType FROM Orion.AlertStatus A WHERE A.Acknowledged=0 ORDER BY A.TriggerTimeStamp DESC"
    result = swis.QueryXml(query)

The problem is, I want to get alertinforamtion from three tables named Orion.AlertDefinitions, Orion.AlertStatus andOrion.Nodes in swis (SolarWinds Information Service) and I need to join these 3 tables but I can able to join 2 of them (Orion.AlertDefinitions, Orion.AlertStatus) using swql (SolarWinds Query Language) query shown below

query = "SELECT AlertStatus.AlertDefID, AlertDefinitions.AlertDefID, AlertDefinitions.Name, AlertStatus.State, AlertStatus.ObjectName, 
AlertStatus.TriggerTimeStamp, AlertDefinitions.Description FROM Orion.AlertStatus INNER JOIN Orion.AlertDefinitions
ON AlertStatus.AlertDefID=AlertDefinitions.AlertDefID WHERE AlertStatus.Acknowledged=0"

I know the alert details but i don't know the alert belongs to which node. For this purpose i need to join "orion.node" table with the remaining tables. But 3rd table (Orion.Nodes) has no common field(s) in anyone of other two tables to join. I need to get three field values named as DNS, IPAddress, NodeDescription from Orion.Nodes table.

score 2 · Answer 1 · edited Nov 09 '12 at 10:19

If you are interested in node-related alerts and you want to join node details, then you should select only alerts with AlertStatus.ObjectType='Node' and join using AlertStatus.ActiveObject which is the NodeID of node associated. So the query (with node properties mentioned) would look like:

SELECT AlertStatus.AlertDefID, AlertDefinitions.AlertDefID, AlertDefinitions.Name, AlertStatus.State, AlertStatus.ObjectName, AlertStatus.TriggerTimeStamp, AlertDefinitions.Description, Nodes.IPAddress, Nodes.Dns, Nodes.NodeDescription
FROM Orion.AlertStatus
INNER JOIN Orion.AlertDefinitions
  ON AlertStatus.AlertDefID=AlertDefinitions.AlertDefID
INNER JOIN Orion.Nodes
  ON AlertStatus.ActiveObject=Nodes.NodeID
WHERE AlertStatus.Acknowledged=0 AND AlertStatus.ObjectType='Node'

Anyway, for more specific questions and answers about Orion SDK and SWIS, you might want to take a look at the Orion SDK Thwack Forum.

score 0 · Answer 2 · edited Oct 31 '17 at 14:36

There are also automatic associations made between the tables in SWQL, for example the SWQL below does not use JOINs but calls from several tables:

SELECT E0.[Node].Caption, E0.AssignmentName, E0.CustomPollerName, 
       E0.DetailsUrl, E0.DisplayName, E0.NodeID, E0.Status, E0.StatusDescription, 
       E0.StatusIconHint, E0.StatusLED, E0.UnManaged, E0.UnManageFrom, E0.UnManageUntil, E0.Uri, 
       E0.[Node].Description, E0.[CustomPoller].[OID] = '1.3.6.1.2.1.1.3',
       E0.[CustomPollerStatusScalar].[Status]
 FROM  Orion.NPM.CustomPollerAssignmentOnNode AS E0
 WHERE E0.[Node].[SysObjectID] IS NOT NULL
   AND E0.StatusDescription NOT LIKE 'Unmanaged'

This site provides some more details and examples.

join more than 2 tables using SWQL(SolarWinds Query Language) when there is no common field?

2 Answers2