Get latest value of two custom pollers for list of nodes

Hello!

I've been throw into the world of Solarwinds and very green to SQL and SWQL, and I've trying to figure out creating this SWQL query.

I have a bunch of nodes that have two custom pollers going, all nodes belonging to one vendor. I want to create a table that lists each node, and the latest value of the two pollers.

So far I've worked out this:

SELECT TOP 1000 n.Caption
         , cps.Status
FROM Orion.Nodes n
INNER JOIN Orion.NPM.CustomPollerAssignmentOnNode cpaon ON n.NodeID = cpaon.NodeID
INNER JOIN Orion.NPM.CustomPollerStatistics cps ON cpaon.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
WHERE n.Vendor = 'Name of Vendor' AND cpaon.DisplayName = 'FirstCustomPoller'
GROUP BY n.Caption, cps.Status

Which returns turns all historical values, and I don't know how to only get the latest. I also have a second customer poller (eg. SecondCustomPoller) that I also want to include in the results. But I can't work out how to do that!

Anyone kow what I'm doing wrong?

Find more posts tagged with

SWQL

custompoller

Accepted answers

All comments

jaed

Did you try changing the "SELECT TOP 1000" to "SELECT TOP 1"

And add "ORDER BY cps.DateTime DESC" to the bottom?

deevee

All good, I think I worked it out.

SELECT TOP 100 [Nodes].DisplayName
        , (SELECT TOP 1 CurrentValue
            FROM Orion.NPM.CustomPollerAssignmentOnNode AS cPon
            INNER JOIN Orion.NPM.CustomPollerStatistics AS cPs ON cPon.CustomPollerAssignmentID = cPs.CustomPollerAssignmentID
            WHERE cPon.DisplayName = 'FirstCustomPoller' AND [Nodes].NodeID = cpon.NodeID
            ORDER BY cPs.DateTime
        ) AS Poller1
        , (SELECT TOP 1 CurrentValue
            FROM Orion.NPM.CustomPollerAssignmentOnNode AS cPon
            INNER JOIN Orion.NPM.CustomPollerStatistics AS cPs ON cPon.CustomPollerAssignmentID = cPs.CustomPollerAssignmentID
            WHERE cPon.DisplayName = 'SecondCustomPoller' AND [Nodes].NodeID = cPon.NodeID
            ORDER BY cPs.DateTime
        ) AS Poller2
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Vendor = 'Name of Vendor'
ORDER BY [Nodes].DisplayName ASC

There's probably a better way of doing this, but that's what I've worked out.