what is the most efficient way to fetch current data from a custom poller in swql?

So I'm comfortable using subqueries to get the data I need, however I'm going to be running this query on 250 nodes every 2 minutes.  

The subqueries take the node caption to get the nodeid, and then to get the Custom Poller assignment ID and onto the label row and actual data.  However that outputs all of the data collected, and what I want is just the most recent poll.

So I plan to take the queries that drill down to the data, and cache the output of those so I only run them infrequently so that those queries don't generate load when the data isn't changing.

But when I hit the Orion.NPM.CustomPollerStatusOnNode table, I am presented with data going back to the begining of time.  What is the best way to query this table in a way I am not touching every row to find the most current row for each node.

If I have 250 to 400 node ID's (a reasonable growth estimate) what is the most efficient way to pull that data over?  I can do a query for each node, or I can combine the node ID's in one query and pull it all over together.  But just because *I* think one method is more efficient than another doesn't make it true, so I'm hoping to get some insight regarding the best way to do this.

To put some context on it, we are one of the largest retailers, with a highly scrutinized and professionally optimized set of SolarWinds installations, with the database engines on the best purpose built database hardware.  And even dividing the environment across two different SolarWinds instances, we are extremely bottlenecked at the database...  So every query I make is on top of an already saturated database engine has a cost.  The data extraction is a legal requirement that must happen.  The heart of the requirement is to retain the fine resolution trend data 20 times longer than SolarWinds possibly can by moving it into a big-data cloud.

So I think the question boils down to the lightest database touch possible while fetching the most recent entries for a particular family of nodes from the Orion.NPM.CustomPollerStatusOnNode table.  I'm pretty good with sql, but my methods probably resemble brute force more than optimized queries.  And it doesn't help that additional layers of abstraction may exist between me and the data,   

  • Can you share the query?

    If you are running it within SWQL Studio, you could optionally add WITH QUERYSTATS or WITH QUERYPLAN to see the actual calls behind the scenes.  I'm not a DBA, so I generally need to hand that kind of information off to someone else who's smarter than I.

  • I've pulled data from Orion.NPM.CustomPollerStatusOnNode before and I'm pretty sure it only includes the most recent poll data. If your UnDP returns multiple rows of data (i.e. a tabular poller), then you will see the same number of rows in that table.

    I'm not sure why you're seeing data going back so far.

    You could also use Orion.NPM.CustomPollerStatus however you'd need to manually join to other tables to filter via custom poller or NodeID which might reduce your efficiency (that data is already in columns in Orion.NPM.CustomPollerStatusOnNode).

  • Yes you are correct, that table is the answer to my query.  The reason I didn't see that at first is because someone on another team had onboarded something improperly that was stuffing 100,000 entries into that table every cycle, and the "SELECT TOP 1000" exploratory select you get from right clicking a table was not looking like Orion.NPM.CustomPollerStatusOnNode contained my data, so I was getting the status from Orion.NPM.CustomPollerStatus, because I could recognize my data in there...  But I did eventually dig deeper into Orion.NPM.CustomPollerStatusOnNode and realized that my issue is my "top 1000" select was hiding my data, I wrote queries tracking the CustomPollerAssignmentID and exposed my data that way...  So it was there all along, I just couldn't see it.

    An engineer from another team had implemented polling on a family of nodes after creating a custom poller for every OID in the MIB.  Some of the OID's were tables containing 5000 entries that return a "0", and there were several of those for each of over a hundred nodes.  So Orion.NPM.CustomPollerStatusOnNode had so much crap data in it that I couldn't see the real data.  I eventually wised up and hit the table with a query that worked for me.  I'm still challenged by swql not working the same as sql, so I spend a lot of time trying to write queries that are unsupported...  But I found my data, and you confirmed it, so thanks!

  • That will definitely do it!  Everything is great and then "Why are my custom poller tables 100+ GB?" 

  • WITH QUERYSTATS and WITH QUERYPLAN look like great tools I should be using, thanks!