CUC (6) CUCM (26) Jabber (6) Python (2) Routing (3) Solarwinds Orion NPM (4) switching (1) Video (6) voice (2)

Tuesday, 17 May 2016

Solarwinds exporting custom pollers from SQL

Solarwinds NPM poller results get stored in a  SQL database. Now I found something funky, when working with pollers and using their historic data. I set a poller with a 1 minute interval, which Solarwinds started averaging out to 5 minute interval data. This is OK if you are looking at for instance interface in/out stats, but no good if you are looking at active calls going across a voice gateway. I mean a value of 5.33 calls as a 5 minute average, (deducted from 5 1 minute poll values) is simply no good.  The reason why Solarwinds does this is so the size of historic data, will not get a huge footprint on your storage. Which sounds fair to me, it just happened that for monitoring active calls on a gateway historically, averages are just no good when it comes to capacity planning. For this you need real values or maximum values at least.   So I started looking into ways of moving the daily and non-averaged data away from the SQL DB, before they got lost.  This querying and storing of data away from Solarwinds, goes further than hitting the EXPORT button in your Solarwinds NPM web interface. 

Now I have explained the background of why i decided to spend some time on writing this up; let's get cracking.

Now, I am by no means an SQL export, so as always this post is most of all a reference for myself to know enough NOT to be dangerous.

Step 1 - Find the poller assignment's unique ID

For this you need to open up the DataBase Manager on the Solarwinds sever itself.  The Database where all the Performance monitoring poller data is stored is called NetperfMon.

Scroll down and search for a table called "CustomPollerAssignment" and hit Execute query, (see Below).

Fig. 1 - Finding out your poller assignment ID

This will return all the pollers that are design on each individual device, each with their own ID (long numbered string), see screenshot above. This poller ID is unique and it will form part of the SQL query. Please note that if you are after transform rule data, you will not find them in your database, as they are a mathematically derived product of poller data.

Step 2 - Performing the actual query

Now let us assume that after step 1, we are interested in DFJTotalActiveCalls-voipgw  which as assignment ID <456ce102-e765-4550-b655-6f0c003311f1>

then run a query on the customerpoller-hourly table, filtering out based on the previously found assignment ID.

Fig.2 - custom poller data queries

You can also query the CustomPollerStatistics_Daily and CustomPollerStatistics_Detail   (see Figure 2).  

so you would execute the following queries.

SELECT DateTime, RawStatus FROM [dbo].[CustomPollerStatistics_Daily]

where custompollerassignmentid ='456ce102-e765-4550-b655-6f0c003311f1'

SELECT DateTime, RawStatus FROM [dbo].[CustomPollerStatistics_Detail]

where custompollerassignmentid ='456ce102-e765-4550-b655-6f0c003311f1'

<add export, steps>

1 comment:

  1. Hello,
    Appreciate your work, very informative blog on Solarwinds exporting custom pollers from SQL. I just wanted to share information about SolarWinds Online Training. Hope it helps community here.