SWQL report to generate Serial and Model number of WAP devices

Is there a script (SWQL) that can generate the WAP devices serial and model number?

Parents
  • So, you can do this but it is a two step process.

    You need to create a UnDP (and assign it) to extract the serial numbers from the WLC. The second step is you need to then create a custom SWQL report to list the details out.

    For Cisco WLC's the OID we've been using is: 1.3.6.1.4.1.14179.2.2.1.1.17 - so go and create your UnDP (https://solarwindscore.my.site.com/SuccessCenter/s/article/Create-a-Universal-Device-Poller-UnDP?language=en_US)  and these are the options we've used:

    Then you need to create your repot.

    The code we used 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 'Model number'
    	,t4.STATUS AS 'Software'
    	,t5.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
    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 'bsnAPName%'
    	) AS t1 ON cps.RowID = t1.RowID
    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 'bsnAPSerialNumber%'
    	) AS t2 ON cps.RowID = t2.RowID
    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 'bsnAPModel%'
    	) AS t3 ON cps.RowID = t3.RowID
    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 'bsnAPSoftwareVersion%'
    	) AS t4 ON cps.RowID = t4.RowID
    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 'bsnApIpAddress%'
    	) AS t5 ON cps.RowID = t5.RowID
    ORDER BY 'AP Name' ASC
    

    You may need to edit the report code as we used several UnDP names to get a little more info returned. If you change your UnDP names from the default, then you will need to edit them in the above code.

    We named ours:

    bsnApIpAddress - OID: 1.3.6.1.4.1.14179.2.2.1.1.19
    bsnAPModel - OID: 1.3.6.1.4.1.14179.2.2.1.1.16
    bsnAPName - OID: 1.3.6.1.4.1.14179.2.2.1.1.3
    bsnAPSerialNumber - OID: 1.3.6.1.4.1.14179.2.2.1.1.17
    bsnAPSoftwareVersion - OID: 1.3.6.1.4.1.14179.2.2.1.1.8

  • Hi Stuartd,

    The OIDS or UDP are already set it's just the report that I can't make it work, tried the script but getting Query is not Valid. Thanks

  • Hi Stuartd,

    It's working now using SQL good thing I was able to read some of your old post. Thank you very much

  • Ahh yes - apologies. That one was written in SQL but glad you got it working.

Reply Children