• State Verified Answer
  • Locked Locked
  • Replies 5 replies
  • Subscribers 24 subscribers
  • Views 1977 views
  • Users 0 members are here
Options
Related
This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

keoki

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,   

Parents
  • +1

    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).

  • 0 in reply to shuth

    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!

  • 0 in reply to keoki

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

Reply Children
No Data

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification Media Vault Link Accounts
About THWACK Blogs For Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2024 SolarWinds Worldwide, LLC. All Rights Reserved.