modify SWQL query to include node names

Why not just add it to your select statement and reference a table that includes your device's host name?

This is what your script should look like. I've bolded the differences:

SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], ToLocal(s.DateTime) AS [Last Polled], n.Caption AS [Node Name]

FROM Orion.NPM.CustomPollerAssignment a

JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID

JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

JOIN Orion.NPM.Nodes n ON a.NodeID = n.NodeID

WHERE c.PollerType = 'F' AND a.NodeID = ${NodeID} AND ((c.UniqueName = 'EnvrnHumidity') OR (c.UniqueName = 'Rack1Humidity') OR (c.UniqueName = 'Rack8Humidity')) ORDER BY s.Status DESC

I had to change it a little bit to include some pollers that I actually have in my environment and I had to modify out the ${NodeID} variable, edit out your ToLocal function, and remove your high level database references (orion.NPM.), but ultimately i got it to work in my system as this. Bolded some of my individual edits:

SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], s.DateTime AS [Last Polled], n.Caption AS [Node Name]

FROM CustomPollerAssignment a

JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID

JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

JOIN Nodes n ON a.NodeID = n.NodeID

WHERE a.NodeID = '117' AND ((c.UniqueName = 'upsAdvOutputCurrent') OR (c.UniqueName = 'upsAdvInputFrequency') OR (c.UniqueName = 'upsAdvOutputFrequency'))

ORDER BY s.Status DESC

and returned:

Is this about what you were looking for?

Sorry for the delay in responding.  I was hoping for a summary type of chart in that multiple environmental devices would be listed with their respective results (specifically both nodes that have the pair of pollers assigned).  If I'm not mistaken, your solution is specific to only one node, correct?

You can increase the scope to include the results for the other node as well. Figure out the NodeID for both of the nodes (or all of the nodes you want to alert on) and plug them in, separated by commas, in the bolded area below:

SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], ToLocal(s.DateTime) AS [Last Polled], n.Caption AS [Node Name]

FROM Orion.NPM.CustomPollerAssignment a

JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID

JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

JOIN Orion.NPM.Nodes n ON a.NodeID = n.NodeID

WHERE c.PollerType = 'F' AND a.NodeID IN (#,#,#) AND ((c.UniqueName = 'EnvrnHumidity') OR (c.UniqueName = 'Rack1Humidity') OR (c.UniqueName = 'Rack8Humidity')) ORDER BY s.Status DESC

for instance, if you have 2 nodes that you want to include and they are "17" and "23", then replace it with (17,23).

The table results will show:

Poller Name // Current Status // Last Polled time // Node Name

Are you looking for other details? if this isn't what you are looking for, if you could, can you create an example of the output you want?

Also, your initial query includes ${NodeName} which narrows your results to a specific node name anyway.

Just making a guess, but you're looking for something like:

NodeName // Poller1Status // Poller2Status // Poller3Status // etc.

is that right?

HTH!

dhanson, thank you for your help.  Hard-coding a Node ID may set me up for an uncomfortable conversation when/ if I ever remove and add back a device and forget what is hard coded throughout the application.  Think it would be possible to match on the universal device poller that is providing this information?

Ideal format:

FirstNodeName // Poller1Status // Poller2Status

SecondNodeName // Poller1Status // Poller2Status

Again, thanks for your assistance.

I saw your first version of the message (filter results based on a similarity between node names) and this is very easy to do. Check out the bold below, as it is the only change (this isn't your ideal output, but the output I described before):

SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], ToLocal(s.DateTime) AS [Last Polled], n.Caption AS [Node Name]

FROM Orion.NPM.CustomPollerAssignment a

JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID

JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

JOIN Orion.NPM.Nodes n ON a.NodeID = n.NodeID

WHERE c.PollerType = 'F' AND a.NodeID LIKE '%MYDEVICE%' AND ((c.UniqueName = 'EnvrnHumidity') OR (c.UniqueName = 'Rack1Humidity') OR (c.UniqueName = 'Rack8Humidity')) ORDER BY s.Status DESC

Now, if we wanted to match on the UnDP, we can exclude the a.NodeID from the WHERE statement completely, as we're already filtering on specific UnDP UniqueNames there. (basically remove what is underlined above)

Thank you for specifying what output you want to see. I think I can make this work, just give me a little time and I'll see if I can work out the query to get you your ideal table. =)

Ok, got one for you, and it could potentially be optimized and improved to be shorter and a lot cleaner, but this will get you the results you want. Just replace "POLLER#Name" with the name of the appropriate UnDP's you want to filter on. Don't forget to name your columns properly in the final query (e.g. row 36 below...make sure you edit the poller names there):

WITH a AS
(
SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [Last Polled], n.Caption
FROM CustomPollerAssignment a
JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID
JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
JOIN Nodes n ON a.NodeID = n.NodeID
WHERE c.UniqueName = 'POLLER1Name'
GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status
ORDER BY s.Status DESC
)
,
b AS
(
SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [Last Polled], n.Caption
FROM CustomPollerAssignment a
JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID
JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
JOIN Nodes n ON a.NodeID = n.NodeID
WHERE c.UniqueName = 'POLLER2Name'
GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status
ORDER BY s.Status DESC
)
,
c AS
(
SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [Last Polled], n.Caption
FROM CustomPollerAssignment a
JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID
JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
JOIN Nodes n ON a.NodeID = n.NodeID
WHERE c.UniqueName = 'POLLER3Name'
GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status
ORDER BY s.Status DESC
)
SELECT n.Caption AS NodeName, a.Status AS [Poller1Name], b.Status AS [Poller2Name] c.Status AS [Poller3Name]
FROM Nodes AS n
JOIN a ON n.Caption = a.Caption
JOIN b ON n.Caption = b.Caption
JOIN c ON n.Caption = c.Caption

Let me know if this works out for you!

dhanson, you rock.  Thanks.

Sounds like a positive result. =)

Don't forget to mark questions as answered, if this yields the results you are looking for. =)

dhanson, surely the issue is PEBKAC.  My modified version doesn't work (Error: A query to the SolarWinds Information Service failed):

WITH a AS

( 

SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [LastPolled], n.Caption

FROM CustomPollerAssignment a

JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID

JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID =s.CustomPollerAssignmentID 

JOIN Nodes n ON a.NodeID = n.NodeID 

WHERE c.UniqueName = 'Rack1Humidity' 

GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status 

ORDER BY s.Status DESC

) 

, 

b AS 

(

SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [LastPolled], n.Caption

FROM CustomPollerAssignment a 

JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID 

JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID =s.CustomPollerAssignmentID 

JOIN Nodes n ON a.NodeID = n.NodeID 

WHERE c.UniqueName = 'Rack8Humidity' 

GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status 

ORDER BY s.Status DESC

) 

, 

SELECT n.Caption AS NodeName, a.Status AS [Rack1Humidity], b.Status AS [Rack8Humidity]  

FROM Nodes AS n 

JOIN a ON n.Caption = a.Caption 

JOIN b ON n.Caption = b.Caption

You have an extra comma in there after the last parentheses. Remove this and give it another shot!

(...)

ORDER BY s.Status DESC

)

, <------this can go

SELECT n.Caption AS NodeName

(...)