Friday, September 29, 2017

Using SQL Queries to Analyze AP Neighbor Information

There's been debate on the state of 2.4GHz Wi-Fi. Some say 2.4 GHz is deceased, that it has kicked the bucket, shuffled off its mortal coil, joined the choir invisible. Others say it's not quite dead yet. I'm in the latter group, primarily because I have devices and applications that rely on it.

One thing most Wi-Fi engineers will agree on: if you have a high-density 5 GHz network of dual-radio APs, you will need to turn off some of the 2.4 GHz radios. There are only 3 non-overlapping channels available, and since 2.4 GHz propagates better than 5 GHz, leaving all radios enabled will result in large amounts of co-channel interference. And that's bad.

*Heavy Sigh*
So how do you decide which radios to turn off?

If you have a Cisco wireless network with lightweight APs, you can leverage RRM to help. RRM uses Neighbor Discovery Packets (NDP) to measure the RF "distance" between radios. The end result is two sets of tables: the receive neighbor table and the transmit neighbor table. The receive neighbor table contains a list, for each radio, of what other radios it can hear NDP packets from. The list contains which channel the neighbor was heard on, and the signal strength it was heard at last. The transmit  neighbor table contains a list, again for each radio, on how well other radios can hear it. The transmit neighbor table also contains channel and power information for each neighbor.

With this information, you could set a criteria for disabling a 2.4 GHz radio. If a radio has more than X receive neighbors on the same channel as it does, with a power greater than Y, that radio should be disabled. Another criteria could be if a radio has more than A transmit neighbors on the same channel as it does with power greater than B, that radio should be disabled.

Getting the information to determine this is not easy through the CLI or web interfaces. You could do it, but it would be very time and tedious. Thankfully, there is WLCCA. WLCCA is a GUI tool that can read the output of the "show run-config" command and parse it for very valuable information. One of the things it can do is export the receive neighbor table in CSV format. Once it is in CSV format, it can be imported into a database and T-SQL can be used to get the information we want.

For WLCCA to work, you need to give it the output of "show run-config." One way of getting the output is logging the output of a CLI session to text file and issuing the command. Another way is to establish a CLI session and use the transfer upload commands to send the output to a TFTP server. This is the preferred method, especially if you have a controller with hundreds of APs. You may need to extend the timeouts on your TFTP server past the defaults for the transfer to complete successfully.

After you have the exported run-config, open WLCCA and import the file.


You'll be prompted with the dialog below to chose certain analysis options. You can uncheck most of the options. After clicking OK, you'll be prompted to select the file.


After importing the file, WLCCA can export the neighbor table. You perform this by going to Report Center menu.


A file dialog will open to allow you to chose the location and name of the exported file. The name you enter will be appended with "-Nearby" and given a .csv extension. Repeat these steps and export the AP Configuration List (CSV). This file will have "-APsConfig" appended to the file name you enter.

The next step is to import the CSV files into a database. For this blog, I chose Microsoft Access. Before importing in Access, the CSV files need to be cleaned up a bit to make the process smoother.

Open the CSV file for the neighbor list with Excel or a text editor. You will need to delete the first and third lines of the text file, and change the column headings so they don't contain spaces. Below is an example of the Nearby file with the edits made.


You'll also need to edit the APsConfig file in a similar way. This file has many columns, but for our purposes only four are necessary: the ID, AP name, 2.4 GHz channel, and 5 GHz channel. I used Excel for this. My polished data looked like this.


Now that the raw data is in an acceptable format, it can be imported in Access. Launch Access and create a new blank desktop database. Click on the External Data tab, then Text File to launch the wizard. First select the Nearby CSV file. Choose "Import the source data into a new table in the current database," and click OK. Next you'll be asked how to parse the file. Select "delimited" as shown and click Next.


Next, select the delimiter as a comma, and check the box to indicate that the first row contains field names.


Click Next, then Next again. Select the option to let Access add a primary key field, then click Next.


Give the new table a name. I use RxNbrs, which I will reference later in queries. Click Finish.

Repeat these steps to import the APsConfig file. There is one different step in the import process for the APsConfig file; you can chose the ID field as the primary key instead of asking Access to add one for you. When you finish, name the table APsConfig.


I promise, we're getting to the good stuff now. The first SQL query I will write will create the transmit neighbor table from the receive neighbor table. Click on the Create tab, then Query Design.

Don't add any tables, just click Close on the Show Table dialog. Switch to SQL view by selecting it in the upper left corner. Here's the query that will get the transmit neighbor table from the receive neighbor table:


SELECT RxNbr as [TxAP], AP AS [TxNbr], Power, Slot, Channel
FROM RxNbrs
ORDER BY RxNbr;

The tx neighbor table is really just the inverse of the rx neighbor table! Click on the Save icon and name the query TxNbrs.

Now the real fun begins. Let's combine the information in the APsConfig table and the RxNbrs table to see what radios have Rx neighbors on the same channel they are configured for. Go to the Create tab again, and click Query Design. Click close on the Show Table dialog, and enter SQL view. Here is the query.

SELECT APsConfig.Name, COUNT(RxNbrs.RxNbr)
FROM APsConfig LEFT JOIN RxNbrs ON (APsConfig.[2dot4channel] = RxNbrs.Channel) AND (APsConfig.Name = RxNbrs.AP)
WHERE RxNbrs.Power > -61
GROUP BY APsConfig.Name
HAVING COUNT(RxNbrs.RxNbr) > 2
ORDER BY APsConfig.Name;

This query will produce a list of 2.4 GHz radios that have 3 or more neighbors on the same channel that it hears at a power greater than -61 dBm. You can tune the power level and count to your liking. I chose -61 because that is the level at which, even if the neighbor was transmitting at minimum power, the radio would hear it at about -82 dBm. (Remember, NDP packets are sent at the maximum power supported by the radio.)  My environment has several radios that meet this criteria.


Save this query as RxCandidates. Next, let do the same analysis for the tx neighbors. Here is the query:


SELECT APsConfig.Name, Count(TxNbrs.TxNbr) AS [CountOfTxNbr]
FROM APsConfig LEFT JOIN TxNbrs ON TxNbrs.Channel = APsConfig.[2dot4channel] AND TxNbrs.TxNbr = APsConfig.Name
WHERE TxNbrs.slot=0 AND TxNbrs.power >-61
GROUP BY APsConfig.Name
HAVING Count(TxNbrs.TxNbr) > 2
ORDER BY COUNT(TxNbrs.TxNbr) DESC;

This query is getting the list of radios that have more than 2 tx neighbors that see the radio with a power greater than -61 dBm. Again, my environment has plenty of those.


Save this query as TxCandidates. Now we have a pretty good picture of  which radios can see other radios at high RSSI, and what radios can be heard by others at high RSSI. We can select radios that meet both criteria by executing the following query:


SELECT Name 
FROM RxCandidates 
WHERE Name IN (SELECT Name FROM TxCandidates);

Out of 480 APs in my sample deployment, 56 matched both my Rx and Tx criteria. This tells me that these 2.4 GHz radios occupy spaces that are already well covered by other radios, and can probably be disabled without affecting coverage. There are always caveats; make sure that the 2.4 GHz radio isn't necessary for RTLS or other services.

I know that the WLCCA tool is awesome, and has built-in reporting to help you find redundant radios. I just like working with data in SQ. If you are interested, try this in your own environment. If you get stuck, reach out to me on Twitter and I'll see if I can help.