Hello all,
As many of you know SolarWinds has not yet implemented a means of being able to track the historical data regarding Cisco access points that are handled through a Cisco wireless controller (from now on I'll call them Cisco WCs). This became a sticking point for us when we began to have some of our access points get over saturated and needed the ability to see if high usage was mere peaks during different times of day or was sustained usage. Thus, I decided to try and find a way over the last couple days to pull it off. What I ended up coming up with works great, and is all done via means supported by SolarWinds. I'll try and break this down into logical steps for the sake of readability, and ease of following along.
Example Graph:
The caveats for this guide are as follows:
- This currently only lets you view the data from a report, and not on a node view. Starting in NPM 10.7 Custom Charts are supposed to be able to support the same techniques that I use below, but the functionality is bugged as of the current release.
- I don't know if this will work on all available Cisco WCs. I assume it will since their MIB isn't broken down by model, but I can't vouch for their whole range of products. For the record ours is a "Cisco 5500 Series Wireless Controller".
- This assumes that you've enabled SNMP on your Cisco WC and have already added it to Orion.
- You have read access to the SQL backend database of Orion to be able to look up some identification information on the pollers.
- You have access to SQL Management Studio and/or Orion's Database Manager utility, and have credentials to query the SolarWindsOrion database.
- You are running at least NPM 10.6, which I believe is the first version of NPM to let you use SQL queries in the web report builder.
- This information will only be able to be accessed as an Orion report. However you can easily add a link to the report on the node page for the Cisco WC in Orion.
Step 1 - Import and Assign the Universal Device Pollers
This step is pretty easy. Download the UnDP file attached to this article and import it into your environment. Once imported, assign them to your Cisco WC and test them to make sure that data is getting returned from them.
Contained within are a pair of UnDPs; WC_APName and WC_NumberOfUsers. Cisco uses some sort of goofy unique identifier (UID) to identify access points via SNMP, and it ties this UID to the access point's assigned name in a different area in SNMP altogether than in where it reports the client count. Thus, the need for two UnDPs. WC_APName contains all access point UIDs and their assigned names. Meanwhile, WC_NumberOfUsers contains the UID of each access point as well as their current client count.
Important Note: If you rename these UnDPs after importing them keep track of what they were named. Sometimes the database still considers them by their old name, so the next step won't make a lot of sense if you change the name and forget what it was called previously.
Step 2 - Wait for some data to come in
You may as well go do other stuff for at least 15 minutes. By default the UnDPs will gather info every 10 minutes and you'll want there to be some data gathered before moving on that you can test off of.
Step 3 - Find the identifier that Orion has associated with the "WC_NumberOfUsers" UnDP
Fire up SQL Management Studio and run the following query (if using Database Manager add the SQL server that hosts the Orion database, expand the SolarWindsOrion database, and right-click CustomPollerAssignment to query the table. Once the query window appears, replace the default query with the new one below):
SELECT [CustomPollerAssignmentID],[AssignmentName],[CustomPollerID] FROM [SolarWindsOrion].[dbo].[CustomPollerAssignment] ORDER BY AssignmentName
In the results look through the AssignmentName column and find the entry that is named "WC_NumberOfUsers on CiscoWCNodeName". Once you've found it make note of the corresponding CustomPollerAssignmentID value for that row. You do not need the value for the other poller assignment; just this one.
Step 4 - Run a test query on the data
Note: As far as I can tell, this step can only be performed in SQL Management Studio; Database Manager returns no results when this step is conducted with it. If you do not have SQL Management Studio I would recommend skipping this step, and using the "Preview" functionality of the report builder while performing the next step to make sure you have valid data.
Run the following query in SQL Management Studio but ensure that you change the CustomPollerAssignmentID in line 12 to the one you gathered in the last step:
SELECT
Detail.[DateTime] AS Timestamp,
Status.Status AS 'AP Name',
SUM(Detail.[RawStatus]) AS 'Client Count'
FROM
[SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail]
AS Detail
INNER JOIN [SolarWindsOrion].[dbo].[CustomPollerStatus]
AS Status
ON Left(Detail.[RowID], Len(Detail.[RowID]) - 2) = Status.[RowID]
WHERE
Detail.[CustomPollerAssignmentID] = '6C4E621B-A7D3-439C-8402-D692BE67743A'
GROUP BY
Detail.[DateTime],
Status.Status
ORDER BY Timestamp
There are numerous issues in trying to pull the data we need from the database including such hurdles as SNMP reporting on each radio within a single access point and adding a .0 or .1 to the UID for each one found, the need to combine the radios for an access point into a single value, the need to resolve a UID to an access points readable name, etc. This query (using aliases for the sake of readability) pulls the data that we want, removes the radio identifier (thus making all radios in one access point have the same UID in the results), grouping (or adding together) the client count of all radios on an access point into one value, and then finally doing a join to replace the UID of the access point in the results with its actual name. If successful you should see a ton of rows, grouped in ten minute intervals with one row for each of your access points and its corresponding client count at the time of polling.
Step 5 - Create the report in Orion Web Report Builder
Go into Manage Reports, create a new report, and select Custom Chart when you are asked to add content. Set the "Selection method" to "Advanced DataBase Query (SQL, SWQL)" and set the "Query Type" to "SQL". Before we get into the query itself, put in something for the "Selection Name" below. I highly recommend settling on some sort of naming scheme for these as you will likely be making more than one. Something like "AP_ClientCount_StreetAddress_Floor" can work. You can always rename these later, however, so at least get something in here for now. Finally, in the query box, paste the following but ensure that you change the CustomPollerAssignmentID in line 12 to the one you gathered in Step 3:
SELECT
Detail.[DateTime] AS Timestamp,
Status.Status AS 'AP Name',
SUM(Detail.[RawStatus]) AS 'Client Count'
FROM
[SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail]
AS Detail
INNER JOIN [SolarWindsOrion].[dbo].[CustomPollerStatus]
AS Status
ON Left(Detail.[RowID], Len(Detail.[RowID]) - 2) = Status.[RowID]
WHERE
Detail.[CustomPollerAssignmentID] = '6C4E621B-A7D3-439C-8402-D692BE67743A'
AND Detail.[DateTime] >= ${FromTimeUTC}
AND Detail.[DateTime] < ${ToTimeUTC}
GROUP BY
Detail.[DateTime],
Status.Status
This is largely the same query as we ran in the previous step, but with one important change. We've added two more items to the WHERE clause in the form of the time macros that Orion uses. This is what makes this one query able to support whatever from and to times are thrown at it. Note that this also makes this query not function in SQL Management Studio / Database Manager, as these macros are only valid when ran as part of an Orion report.
Click "Preview Results" to make sure that you see expected data, but know that the preview only shows the first 15 rows regardless of how many the query would actually pull. If you see data, click the "Add To Layout" button.
Click the "Edit Chart" button. Under "Left Y-AXIS" set the "Chart type" to Line. Then, click the "Add Data Series" button to select the "Client Count" option and then click the "Add Data Series" button on that window to confirm it. Now click on the plus button next to "More" next to the new "Client Count" data series. For the "Time Column" click the "Browse" button and select the "Timestamp" option before clicking the "Add Column" button. Next, down in the "Multiple objects in datasource" section click the "Browse" button next to "Object ID Column" and select the "AP Name" option before clicking the "Add Column" button. Finally, click the "Preview" button to see that the chart is populated with data and the "Submit" button to confirm all changes to the chart.
Back on the report screen you may wish to change the "From" dropdown to a different time frame. Then, click the "Next" button at the bottom and then proceed through the rest of the wizard before finally clicking the "Submit" button to save the report.
Optional Step - Dealing with more than ten access points
By default Orion will only show ten lines in a line chart, and really you don't want to increase it as the colors start repeating and it starts to get noisy regardless. Thus it is best have multiple charts, such as one per building or one per floor so that each one has ten or less access points on it. However, this is ENTIRELY reliant upon a good naming scheme for your access points as found within the Cisco WC. For example, if your building address is 5647 and it has one access point on each of its floors you'd have access points named AP-5647-1, AP-5647-2, AP-5647-3, etc.
To do this you will need a unique data source for each of the charts. This data source will be the same except with the following changes to the WHERE clause in which you will need to add another line (this example a continuation of the example scheme above):
WHERE
Detail.[CustomPollerAssignmentID] = '6C4E621B-A7D3-439C-8402-D692BE67743A'
AND Status.Status LIKE '%-5647-%'
AND Detail.[DateTime] >= ${FromTimeUTC}
AND Detail.[DateTime] < ${ToTimeUTC}
This data source will include all access points that have a name that contains -5647- (in this case including the dashes), with the percent sign signifying zero or more wildcard characters. From here you just have to play with that LIKE search string to help break up your access points into more sizable chunks.
Finally, I would recommend that if you do create charts with their own data sources based on LIKE statements in the WHERE clause that you make one more chart to catch everything else. All you need to do is copy that line from each of the data sources you've made, and just throw it into notepad. For the catch-all chart set up the query the same as the others but its WHERE clause will look like this:
WHERE
Detail.[CustomPollerAssignmentID] = '6C4E621B-A7D3-439C-8402-D692BE67743A'
AND Status.Status NOT LIKE '%-5647-%'
AND Status.Status NOT LIKE '%-HQ-%'
AND Status.Status NOT LIKE '%-3457-%'
AND Status.Status NOT LIKE '%-MAIN-%'
AND Detail.[DateTime] >= ${FromTimeUTC}
AND Detail.[DateTime] < ${ToTimeUTC}
The idea is that in this example we've made four other charts, each grabbing one that is LIKE one of the search strings above. This would be the WHERE clause where it is finding the NOT LIKES for all four of those (aka anything that doesn't match the rest).
I really hope this helps someone else. I'm mainly documenting this for my own purposes (because like I'm going to be able to remember half of this down the road) but I know that I've seen others lamenting Orion's inability to do this. This could also be adapted to graph and keep historical data on other metrics like bandwidth utilization, likely by adding in other UnDPs and changing the SQL queries around a bit (As I imagine you may need to change some column names). To find out what OIDs are useful in this regard SolarWinds has a great page here: SolarWinds Knowledge Base :: What object IDs (OIDs) does Orion NPM poll for information about Cisco wireless controllers? What types of information about Cisco wireless controllers does Orion NPM poll?. Another useful link that arguably got me started in dealing with Cisco wireless controller SNMP OIDs is: MikroTik RouterOS • View topic - Cisco WiSM/440x Wireless LAN Controller - No. Clients per AP.
Top Comments