1 of 1 people found this helpful
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.
COUNT(DISTINCT rc.Client_MAC) AS Num_Mac_Address
FROM NPM_NV_WL_REPORT_CLIENTS_V rc
( rc.DateTime BETWEEN DATEADD(wk, DATEDIFF(wk,0,CAST(GETDATE() AS DATE)), 0) and getdate() )
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.
? 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.
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