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

ALL Wi-Fi Access Points Inventory Report - SQL HELP Please?

ALL Wi-Fi Access Points Inventory Pending…

Great News Fellow SolarWinds THWACK’sters…

I have been fortunate enough to find a SolarWinds SQL Script within the THWACK Community that I have developed to ideally give us the items listed below for an ALL Wi-Fi Access Points Inventory. [BUT].

The ALL Wi-Fi Access Points Inventory Report Table Columns are as follows;

All of the information shown within this Table are from SolarWinds SQL Database Table JOINs.

NOT Manual Entries using the Nodes Custom Properties.

NEW NOTE 3; I believe that to Use Nodes Custom Properties for this All of the Light Weight / Thin Client Access Points will need to be added as ICMP Polled Nodes to give them a unique SolarWinds SQL Database Node ID. As per New Note 2 Below I believe that it is the WLC's Node ID causing the issue with this Report, hence the request for help.

NEW NOTE: Can somebody help me find the Right JOIN, for the Column AP Serial Number?

Please See Further Comments Below.

Access Point Hostname,

AP IP Address,

AP MAC Address,

AP Model,

AP Serial Number,

AP Type,

AP WLC,

AP WLC Group,

SSID

Unfortunately, the SQL Script as Shown Below shows the Serial Number of the WLC and not the Wi-Fi Access Point.

--# The Serial Number within the Report is currently Duplicated indicating that it is the APs WLC Serial Number:-(

01 SELECT DISTINCT

02      [wspm].[Source Host Name]           AS [Access Point Hostname],

03      [wspm].[Source IP Address]          AS [AP IP Address],

04      [wspm].[Source MAC Address]         AS [AP MAC Address],

05      MAX(CASE WHEN cp.UniqueName = 'bsnAPModel' THEN cps.Status ELSE '' END)             AS [AP Model],

06      MAX(CASE WHEN cp.UniqueName = 'bsnAPSerialNumber' THEN cps.Status ELSE '' END)      AS [AP Serial Number],

07      [wlaps].[WirelessType]              AS [AP Type],    

08      [wspm].[Controller Name]            AS [AP WLC],

09      MAX(CASE WHEN cp.UniqueName = 'bsnAPLocation' THEN cps.Status ELSE '' END)          AS [AP WLC Group],

10      [wspm].[Source SSID]                AS [SSID]

11

12 FROM WirelessSwitchPortMapping            AS wspm

13

14 LEFT JOIN Nodes                     AS n       ON [wspm].[NodeID]                      = [n].[NodeID]

15 LEFT JOIN NPM_NV_WL_APS             AS wlaps   ON [wspm].[Source Host Name]            = [wlaps].[AP_Name]

16

17 LEFT JOIN CustomPollerAssignment    AS cpa     ON [n].[NodeID]                         = [cpa].[NodeID]

18 LEFT JOIN CustomPollerStatus        AS cps     ON [cpa].[CustomPollerAssignmentID]     = [cps].[CustomPollerAssignmentID]

19 LEFT JOIN CustomPollers             AS cp      ON [cpa].[CustomPollerID]               = [cp].[CustomPollerID]

20

21 GROUP BY

21      [wspm].[Controller Name], [wspm].[Source Host Name], [wspm].[Source IP Address], [wspm].[Source MAC Address],

22      [wlaps].[WirelessType], [wspm].[Source SSID]

23

24 ORDER BY

25      [AP WLC], [Access Point Hostname] ASC

============================================================================================================

NEW NOTES;

I have found the Access Points Serial Numbers within the [dbo].[CustomPollerLabels] Table but this JOIN did not work.

The new Lines added to the script above are as follows within their applicable position within the SQL Syntax.

         [cpl].[Label] AS [AP Serial Number],

     LEFT JOIN CustomPollerLabels   AS cpl  ON [cpa].[CustomPollerAssignmentID] = [cpl].[CustomPollerAssignmentID]

LINE BELOW OMITED From SQL Script Above;

-- MAX(CASE WHEN cp.UniqueName = 'bsnAPSerialNumber' THEN cps.Status ELSE '' END)  AS [AP Serial Number],

I believe that it is the [Nodes].[NodeID] JOIN that will need to be removed or have a condition within the SQL Syntax to get the Access Points Serial Number within the AP Serial Number Column and not the Same WLC Serial Number that the Access Point is Controlled by.

Your feedback on this will be appreciated.

============================================================================================================

NEW NOTE 2; REF Lines 05, 06 & 09.

As you will see from the Note above I have been fortunate enough to Find a Table with the Light Weight / Thin Client Access Point Serial Number.

Despite this the JOIN shown above has not displayed the Light Weight / Thin Client Access Point Serial Number yet...

As yet I have not found a Table or Tables where Light Weight / Thin Client Access Point Model Number is shown or where the Access Point Location has been configured on the WLC.

============================================================================================================

THWACK URL > https://thwack.solarwinds.com/docs/DOC-176851

When Executing the SQL Script within DOC-176851, the individual Wi-Fi Access Point Serial Numbers are shown.

When adding the Table JOINs for the SQL Report Syntax that I have done the I believe that the Duplicated Serial Number shown within the 'AP Serial Number' Column is the Serial Number of the WLC.

Is there a Fellow SolarWinds THWACK’ster who can help me resolve this issue?

Further details will be provided upon request or THWACK Post Update.

============================================================================================================

My Previous Wi-Fi Access Points Inventory THWACK Post; https://thwack.solarwinds.com/message/405906

Message was edited by: Dan Collins

Labels (3)
0 Kudos
1 Reply
Level 12

305 Views But No Feedback or Comments;-(

0 Kudos