Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

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.

Find more posts tagged with

Accepted answers

mesverrum

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

All comments

walkerwakawaka

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.

chrisputer

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

mesverrum

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

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

bobmarley

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.

chrisputer

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