Custom UNDP Poller Report

Question

I have created few UNDP to poll serial number, model, OS versions of checkpoints devices. Now I want to see these in Columns wise report. The current report I have is showing all these information in one column but I want it separate columns for each UNDP polled results. Any suggestion to achieve this.

stuartd · Answer

Neither am I a coder. Most of what I have done has been cobbled together and by asking around.

What I can tell you is that the above code works (in SQL) in our environment. Things you might need to adjust at your end are: removing the references to NPM.Client or adjusting to suit your setup.

Also, any Custom Poller name would need to be replaced with yours.
   - CP names used in above code are: aiAPName, aiAPSerialNum and aiAPIPAddress

You will likely need to adjust column names, order by name, etc as well.

My suggestion would be to try reducing the query to its most basic - i.e. one custom poller. So something like:

SELECT 
	DISTINCT(n.caption) AS 'Controller'
	    , t1.status AS 'AP Name'

FROM CustomPollerStatus AS cps
JOIN CustomPollerASsignment AS cpa ON cps.CustomPollerASsignmentID = cpa.CustomPollerASsignmentID
JOIN Nodes AS n ON n.NodeID = cpa.NodeID
JOIN NodesCustomProperties AS npm ON npm.NodeID = n.NodeID

--t1 section
JOIN 
	(
	SELECT 		cps.status		, cps.CustomPollerASsignmentID		, cps.RowID
	FROM 		CustomPollerStatus AS cps
	JOIN 		CustomPollerASsignment AS cpa ON cps.CustomPollerASsignmentID = cpa.CustomPollerASsignmentID
	JOIN 		Nodes AS n ON n.NodeID = cpa.NodeID
	WHERE 		cpa.AssignmentName = 'Your_Custom_Poller_Name'	) 
AS t1 ON cps.RowID = t1.RowID

ORDER BY
	'AP Name' ASC

Vidyadip91 · Answer

I have modified the script but its giving me kind of messy outputs. I am not very much SQL expert. Is there any oher script available.

stuartd · Answer

So we have a report that does this for Thin APs. It's pretty basic but if you can code (I can't) then you should be able to reverse engineer this to do what you need.

Code is:

/* 
Query to get AP Information from the Controllers through the use of UnDPs, because APs are not nodes in Solarwinds but an extra to the Controllers
*/
SELECT 
	DISTINCT(n.caption) AS 'Controller'
	, t1.status AS 'AP Name'
        , t2.status AS 'Serial number'
        , t3.status AS 'IP Address'
        ,npm.Client
FROM 
	CustomPollerStatus AS cps
JOIN 
	CustomPollerASsignment AS cpa ON cps.CustomPollerASsignmentID = cpa.CustomPollerASsignmentID
JOIN 
	Nodes AS n ON n.NodeID = cpa.NodeID
JOIN
    NodesCustomProperties AS npm ON npm.NodeID = n.NodeID

--t1 section
JOIN 
	(
	SELECT 		cps.status		, cps.CustomPollerASsignmentID		, cps.RowID
	FROM 		CustomPollerStatus AS cps
	JOIN 		CustomPollerASsignment AS cpa ON cps.CustomPollerASsignmentID = cpa.CustomPollerASsignmentID
	JOIN 		Nodes AS n ON n.NodeID = cpa.NodeID
	WHERE 		cpa.ASsignmentName LIKE 'aiAPName%'	) 
AS t1 ON cps.RowID = t1.RowID

-- t2 section
JOIN 
	(
	SELECT 
		cps.status
		, cps.CustomPollerASsignmentID
		, cps.RowID
	FROM 
		CustomPollerStatus AS cps
	JOIN 
		CustomPollerASsignment AS cpa ON cps.CustomPollerASsignmentID = cpa.CustomPollerASsignmentID
	JOIN 
		Nodes AS n ON n.NodeID = cpa.NodeID
	WHERE 
		cpa.ASsignmentName LIKE 'aiAPSerialNum%'
	) AS t2 ON cps.RowID = t2.RowID

--t3 section
JOIN 
	(
	SELECT 
		cps.status
		, cps.CustomPollerASsignmentID
		, cps.RowID
	FROM 
		CustomPollerStatus AS cps
	JOIN 
		CustomPollerASsignment AS cpa ON cps.CustomPollerASsignmentID = cpa.CustomPollerASsignmentID
	JOIN 
		Nodes AS n ON n.NodeID = cpa.NodeID
	WHERE 
		cpa.ASsignmentName LIKE 'aiAPIPAddress%'
	) AS t3 ON cps.RowID = t3.RowID
ORDER BY
	'AP Name' ASC

Apart from editing to suit your environment and UnDP names you will also need to remove or adjust the Custom Property entries.