This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Wireless AP reporting

lclemmons over 11 years ago

Not sure I'm in the right place for this but I couldn't find a reporting group to ask.

We are monitoring a cisco wireless lan controller and, by association, it's attached APs. Our Administration has decided they want a count of how many people are connecting to our wireless across the district TOTAL each month. I've found the reports for average and peak connections and managed to create a new version of it that fit something the non-technical administrators might find useful. However, they still want a total count and I have been unable to figure out how to do this. I would be happy enough to get a count per AP (we have 10 locations, some of which have 5 or 6 APs). Even if I had to run the count daily or weekly and manually add it up I would be fine with that.

I did try using the count function but it was giving me some REALLY funky numbers. Most of our APs report a PEAK usage between 5 and 10 people (these are rural branches) while our urban headqurters location peaks with 15 people on the busiest AP and around 34 total in the entire building. When I ran a report with the count function I was getting something like 36,000 connections a week. that just can't be right. Any suggestions on how to go about this? I'm not a programmer though I can usually make sense of a very basic sql query when I look at one. Doing SQL beyond a query writer is a little out of my league. Is there a way to get this information out of the report writer as it is?

Thanks,

Laura

Top Replies

zzz over 11 years ago +1

I am assuming you are going by MAC addresses and if I connect once at the beginning of the month and once at the end, I count as only 1 instance. Remember that Count (without Distinct) includes all the…

0 zzz over 11 years ago

I am assuming you are going by MAC addresses and if I connect once at the beginning of the month and once at the end, I count as only 1 instance. Remember that Count (without Distinct) includes all the rows including multiple logins, multiple APs, multiple polls/sessions.
Here is a simple report that gives the number of distinct MAC addresses for this week (Monday to today).
Unfortunately, you might need to increase the retention time of that table for monthly data as it only goes to 10 days or so.
SELECT

COUNT(DISTINCT rc.Client_MAC) AS Num_Mac_Address

FROM NPM_NV_WL_REPORT_CLIENTS_V rc
WHERE
( rc.DateTime BETWEEN DATEADD(wk, DATEDIFF(wk,0,CAST(GETDATE() AS DATE)), 0) and getdate() )
Cancel
Vote Up +1 Vote Down

Cancel
0 lclemmons over 11 years ago in reply to zzz

Thank you. Report writer won't allow me to paste in a sql statement but I can use this from sql management studio if need be. At least I can get the numbers manually now until I figure out how to script it. I'll also be able to modify it to give a bit more information based on what I'm seeing in the sql statements in the pre-formatted reports.This was very helpful.
Laura
Cancel
Vote Up 0 Vote Down

Cancel
0 zzz over 11 years ago in reply to lclemmons

? Sure you can.
Just make a new Advanced SQL report or edit one of the existing ones (the ones with the yellow O symbols) and save as a new report.
Note: ALL reports can be seen as SQL by going Report->Show SQL
Btw, if you are going to generate your own monthly totals:
Use DISTINCT rc.Client_Mac AS Mac_Address rather than COUNT(DISTINCT rc.Client_Mac) and then filter those for distinct values or you will be double counting all the MACs that appear in multiple weeks.
Cancel
Vote Up 0 Vote Down

Cancel
0 lclemmons over 11 years ago in reply to zzz

Actually, I don't want to exclude multiple connections from the same mac. Administration would prefer counting one patron per day so the same PC connecting in the morning and afternoon of the same day they want counted as one. However, the same PC connecting the NEXT day would count as a second connection. They'd rather overstate the daily numbers than understate the monthly numbers. I think I can get it figured out now that I have an example that makes sense. Something clicked when I looked at your code.
Oh.. and I forgot there was a different report type for using your own SQL. I was trying to paste it into the show SQL window on a new standard report
Laura
Cancel
Vote Up 0 Vote Down

Cancel