4 Replies Latest reply on Dec 19, 2012 3:38 PM by lclemmons

    Wireless AP reporting

    lclemmons

      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

        • Re: Wireless AP reporting
          zzz

          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() )

          1 of 1 people found this helpful
            • Re: Wireless AP reporting
              lclemmons

              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

                • Re: Wireless AP reporting
                  zzz

                  ? 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.

                    • Re: Wireless AP reporting
                      lclemmons

                      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