Join SWQL query issue

JustinD

Hello,

I am very new to SWQL and SQL all together. I'm attempting to join an existing query we have with another table to include a custom property value. Both tables share the NodeID column but I am having difficulty in getting them to join together to include the 'Current Value' field. 

I'm utilizing this in a dashboard to display the firewalls based on percent packet loss and would like to add the HA status of active or passive as well. Apologies in advance if this is a simple fix but I cant seem to get the multiple join statements to work

This is the current query used in the dashboard:

SELECT TOP 10 Nodes.Caption, Nodes.status, Nodes.PercentLoss, Nodes.NodeID FROM Orion.Nodes

INNER JOIN Orion.ContainerMembers ON Nodes.Caption = ContainerMembers.Name

WHERE ContainerID = 160 OR ContainerID = 161 OR ContainerID = 162 OR ContainerID = 163 OR ContainerID = 164 OR ContainerID = 165 OR ContainerID = 169 OR ContainerID = 171 OR ContainerID = 172

ORDER BY PercentLoss DESC

This is the query I'm utilizing to gather the HA status:

SELECT TOP 10 NodeID, CustomPollerOid, CurrentValue, CustomPollerName

FROM Orion.NPM.CustomPollerAssignment

Where CustomPollerOID = '1.3.6.1.4.1.25461.2.1.2.1.11'

I've attempted:

SELECT TOP 10 Nodes.Caption, Nodes.status, Nodes.PercentLoss, Nodes.NodeID

FROM Orion.Nodes

INNER JOIN Orion.ContainerMembers ON Nodes.Caption = ContainerMembers.Name

WHERE ContainerID = 160 OR ContainerID = 161 OR ContainerID = 162 OR ContainerID = 163 OR ContainerID = 164 OR ContainerID = 165 OR ContainerID = 169 OR ContainerID = 171 OR ContainerID = 172

OUTER JOIN Orion.NPM.CustomPollerAssignment on Nodes.NodeID = CustomPoller.NodeID

WHERE NodeID=NodeID

ORDER BY PercentLoss DESC

I know this is wrong but cant seem to get it to work properly. any guidance or help on this is much appreciated/

jm_sysadmin

I don't have your OID around here to test, but does this help:

SELECT TOP 10 N.Caption, N.status, N.PercentLoss, N.NodeID, CM.Container.DisplayName, CPA.CustomPollerName, CPA.CurrentValue
FROM Orion.Nodes N 
INNER JOIN Orion.ContainerMembers CM ON N.Caption = CM.Name
JOIN Orion.NPM.CustomPollerAssignment CPA on N.NodeID = CPA.NodeID
WHERE 
        CM.ContainerID in (160,161,162,163,164,165,169,171,172)
    And CPA.CustomPollerOid like '1.3.6.1.4.1.25461.2.1.2.1.11'
ORDER BY PercentLoss DESC

I used an alias for each view so that you can more easily pull the column from the correct spot (things like displayname are in many places) you generally join data right after the From statement, although more complicated SQL is possible. And I keep the where statement together, ideally in the simplest form I can. 

 Hopefully this helps you