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 Serial Number,
AP WLC Group,
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]
12 FROM WirelessSwitchPortMapping AS wspm
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]
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]
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]
24 ORDER BY
25 [AP WLC], [Access Point Hostname] ASC
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
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.