cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Can you fill a custom property from a UnDP value?

Jump to solution

Can you fill a custom property from a UnDP value?

Background: I'm part of a new WISP that started up. I got a funny request to where I need to pull a value from a base station via a UnDP SNMP Table and link that to a subscriber's radio.

The Base Stations maintain an SNMP Table of every subscriber attached to it. Nearly everything I want to know about a subscriber's radio is in there. Now, the Subscriber also maintains a table too but there are only two things are relatable. The SSID of the Base Station and it's ID number associated with that subscriber in the Base Station's tables.

Can anyone provide me some direction as to how I can link this stuff together so that I can display things in the customer's "node" in NPM? Things that are recorded that I would like to have reporting in their node and Orion reports are throughput test, RTT and RSS. I understand that I'm going to have to get fancy with SWQL/SQL but I don't even know where to start. I'm struggling with SQL/SWQL because trying to join and do nested queries is kicking my ass. I do know Python and PowerShell though.

0 Kudos
1 Solution

For the most part SWQL and SQL use the same kind of joins, SWQL has some shortcuts but if you aren't already strong in SQL then you can basically ignore that stuff for a bit.  This is the link I started at back when I began to smash me head against the Orion database.

https://www.w3schools.com/sql/sql_join.asp

If you are already solid in PS then this is the basic example for setting properties in the API

https://github.com/solarwinds/OrionSDK/blob/master/Samples/PowerShell/CRUD.SettingCustomProperty.ps1

 

And just to give you an example of how to join between the Custom Poller tables to the Nodes table you can look at this example that I wrote for some custom Netscaler UNDP's I used in the past.  It listed out each node with a specific UNDP assigned, then matched up the rows to show three different columns of that undp tabular poller set.

SELECT n.caption as [Netscaler]  
, name.status as [Server Name] 
, state.Status 
, health.status as [Health %] 
 
FROM Orion.NPM.CustomPollers cp 
join orion.npm.CustomPollerAssignment cpa on cpa.CustomPollerID=cp.CustomPollerID 
join orion.nodes n on n.nodeid=cpa.NodeID 
join (select distinct cps.CustomPollerAssignmentID, cps.rowid, cps.status 
from orion.npm.CustomPollerStatus cps 
join orion.npm.CustomPollerAssignment cpa on cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID 
where cpa.AssignmentName like 'netscaler_vsvrName%' 
) name on name.custompollerassignmentid=cpa.CustomPollerAssignmentID 
join (select distinct cps.CustomPollerAssignmentID, cps.rowid, cps.status 
from orion.npm.CustomPollerStatus cps 
join orion.npm.CustomPollerAssignment cpa on cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID 
where cpa.AssignmentName like 'netscaler_vsvrstate%' and status!='up' 
) state on state.rowid=name.rowid 
join (select distinct cps.CustomPollerAssignmentID, cps.rowid, cps.status 
from orion.npm.CustomPollerStatus cps 
join orion.npm.CustomPollerAssignment cpa on cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID 
where cpa.AssignmentName like 'NETSCALER_VSVRHEALTH%' 
) health on health.rowid=name.rowid 
 
--where n.caption like '%${SEARCH_STRING}%' or name.status like '%${SEARCH_STRING}%' 
 
order by name.status

 

- Marc Netterfield, Github

View solution in original post

7 Replies

I typically try using the Universal Device Poller to bring things together that are not typically provided through the default SNMP pull. Have you looked at the UnDP? It is installed on the SolarWinds server and you can usually Google the manufacture for SNMP MIB to see what you find. I have been able to pull the total number of clients on our Ubiquiti AP's this way.

0 Kudos

I'm not trying to be rude but did you read my question? Was it not clear?

0 Kudos

For the most part SWQL and SQL use the same kind of joins, SWQL has some shortcuts but if you aren't already strong in SQL then you can basically ignore that stuff for a bit.  This is the link I started at back when I began to smash me head against the Orion database.

https://www.w3schools.com/sql/sql_join.asp

If you are already solid in PS then this is the basic example for setting properties in the API

https://github.com/solarwinds/OrionSDK/blob/master/Samples/PowerShell/CRUD.SettingCustomProperty.ps1

 

And just to give you an example of how to join between the Custom Poller tables to the Nodes table you can look at this example that I wrote for some custom Netscaler UNDP's I used in the past.  It listed out each node with a specific UNDP assigned, then matched up the rows to show three different columns of that undp tabular poller set.

SELECT n.caption as [Netscaler]  
, name.status as [Server Name] 
, state.Status 
, health.status as [Health %] 
 
FROM Orion.NPM.CustomPollers cp 
join orion.npm.CustomPollerAssignment cpa on cpa.CustomPollerID=cp.CustomPollerID 
join orion.nodes n on n.nodeid=cpa.NodeID 
join (select distinct cps.CustomPollerAssignmentID, cps.rowid, cps.status 
from orion.npm.CustomPollerStatus cps 
join orion.npm.CustomPollerAssignment cpa on cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID 
where cpa.AssignmentName like 'netscaler_vsvrName%' 
) name on name.custompollerassignmentid=cpa.CustomPollerAssignmentID 
join (select distinct cps.CustomPollerAssignmentID, cps.rowid, cps.status 
from orion.npm.CustomPollerStatus cps 
join orion.npm.CustomPollerAssignment cpa on cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID 
where cpa.AssignmentName like 'netscaler_vsvrstate%' and status!='up' 
) state on state.rowid=name.rowid 
join (select distinct cps.CustomPollerAssignmentID, cps.rowid, cps.status 
from orion.npm.CustomPollerStatus cps 
join orion.npm.CustomPollerAssignment cpa on cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID 
where cpa.AssignmentName like 'NETSCALER_VSVRHEALTH%' 
) health on health.rowid=name.rowid 
 
--where n.caption like '%${SEARCH_STRING}%' or name.status like '%${SEARCH_STRING}%' 
 
order by name.status

 

- Marc Netterfield, Github

View solution in original post

This gives me a HUGH Headstart on figuring this out. Thank you!

0 Kudos

Here is a simple SWSQL query that  pulls _Group and _Severity from my database. Replace these with your custom property names and it should pull them for you. Paste then into Excel then import it in your custom property manager. 

 

Select IP_Address, Caption, NodeID, _Group, _Severity FROM Orion.NodesCustomProperties cp
INNER JOIN Orion.nodes nd on cp.NodeID = nd.NodeID

If its a one time thing I would probably just generate a report that showed IP and the two custom properties that you are looking for and import it using the custom properties import. That should be easy using Report Writer.

This is a good idea. Didn't know that was possible.

0 Kudos