:developers:
I have 375 wireless controllers and autonomous APs, and I need to see which ones are the biggest offenders in not providing me with IP address information for their connected clients. In my NAC solution, I can export a table that shows every device without an IP address and the IP of the offending WLC/AP.
Thank, you, NAC. You did your job. :thankyou: :joy: :woohoo:
OK, Mr. Excel... your turn... of the 3929 rows, please tell me how many times an IP appears in that column so that I know which WLCs are the biggest offenders and I can get the biggest benefit in fixing first.
:developers: :developers: :developers: :developers: :developers: :developers: :developers: :developers:
Apparently, getting Excel to do something as simple as COUNT how many times a value shows up in a column is a feature the programmers never thought was necessary to include or, if they did include that feature, it is in the most inaccessible place. I am out of my mind, trying to find something that works.
Worst was the "Quick analyis" tool. Running it one way told me that, yes, I have 3929 rows. Running it the other way told me that, yes, each value appears once per row.
:printer: :flipdesk: :no:
Try this, dean:
https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
You might have missed it as it was the second hit on Google
Sort and group by autonomous ap ip address, then use subtotals? you should get a subtotal after every group (autonomous IP) and a total on the bottom
Actually, what the MSFT article failed to mention was that, in order to really make the COUNTIF work, is to make a second column of the data, remove duplicates ONLY in that column, and then use those as indexes, so i got
=COUNTIF(A2:A3929,B2)
in the first line (data has headers) and then do a CTRL+D all the way down the index values and it counts how many times that value shows up in the main list (column A).
Now I know which WLCs to take a whack at first. 172.31.170.132, it's ON, MOTHERTRUCKER!!! (IP address changed so as not to provide recon info on my network.)
PS: I called my dad, who's an Excel programmer, for the solution. Thanks, dad.
PPS: I'm still pissed off that there isn't just a "COUNT" button to click and then say how many damn times a damn value shows up in a damn column.
Delete all columns except the WLC IP address. Save as CSV. Put that file on a linux box.
sort wlc.csv | uniq -c
-Otanx
Quote from: Otanx on November 30, 2016, 12:41:06 PM
Delete all columns except the WLC IP address. Save as CSV. Put that file on a linux box.
sort wlc.csv | uniq -c
-Otanx
Honestly, that would have taken less time than what I put into solving this problem. :doh:
There is not much latest app, O/S, web pages that I dont hate. Apps crashing, O/S crashing, web pages unresponsive, everything fial.
Quote from: Otanx on November 30, 2016, 12:41:06 PM
Delete all columns except the WLC IP address. Save as CSV. Put that file on a linux box.
sort wlc.csv | uniq -c
-Otanx
And you learn something new every day. I would have spent an hour writing a hacky python script LOL
for the interests of keeping my scripting chops up, do you mind sending the data through for me to have a whack at it via python? I presume there's noething sensitive in there?
Quote from: wintermute000 on November 30, 2016, 09:06:36 PM
Quote from: Otanx on November 30, 2016, 12:41:06 PM
Delete all columns except the WLC IP address. Save as CSV. Put that file on a linux box.
sort wlc.csv | uniq -c
-Otanx
And you learn something new every day. I would have spent an hour writing a hacky python script LOL
for the interests of keeping my scripting chops up, do you mind sending the data through for me to have a whack at it via python? I presume there's noething sensitive in there?
mmmm.. attempted hack via social engineering.
It's just all the IP addresses of our wireless controllers and autonomous APs... Sure, let me post it here in a public forum... :)
Gmail sent because it was too much for the PM.