Count users with api

Tue Sep 23, 2014 4:36 am

How can I automatically daily users count with api

Tue Sep 23, 2014 12:35 pm

What do you mean "daily users"? Could you be a little more detailed please?

Tue Sep 23, 2014 2:08 pm

How many users per day used having the accesspoint

Tue Sep 23, 2014 5:02 pm

How would you check that from the command line? Or Winbox? Or anywhere?

Tue Sep 23, 2014 5:06 pm

I want to check that anyware

Tue Sep 23, 2014 5:36 pm

How do you distinguish between users? Different MAC addresses? Hotspot logins? PPPoE logins? Do you count the same user accessing the AP twice, or just once?

Tue Sep 23, 2014 5:54 pm

On MAC addresses
I want to count the users one time a day.

Tue Sep 23, 2014 6:20 pm

You just HAD to pick the most difficult thing to measure... *sigh*...

OK... I'm assuming the clients are getting their IPs via DHCP? If so, this is a good point at which to potentially count them.

Here's the idea: Every time the DHCP server leases an IP, send a request to a web server. This web server will in turn write the MAC address in a DB. The web server will be configured so that once per day, it counts the distinct MAC addresses in the DB, writes that count in a separate table (that you can then read if you want to), and then removes everything in the MAC address DB. This way, you can store the counts for previous days, or maybe even look up the count "up until now today" by doing the same thing that would happen at the end of the day, minus the MAC address deletion.

Strictly speaking, you don't have to delete the MAC addresses from the DB each day - you could just store a timestamp, and then count the distinct MAC addresses within a certain time period. However, doing that requires more HDD from your database/web server.

Add the following lease script in your DHCP server:

/tool fetch url=("http://192.168.0.254/add.php\?mac=" . $leaseActMAC)

(replace "192.168.0.254" with the actual IP or domain of your web server; where you were originally planning to run the API from)

I'll assume at the web server, you have PHP and MySQL, connect to it via the MySQLi extension.
(Adjust accordingly if you prefer something else)

At the web server, first have a file called db.php, with the following contents:

<?php
$mysqli = new mysqli('localhost', 'root', '', 'stats');
$mysqli->set_charset('utf8');

(adjust DB credentials and schema name at line 2)

And then have the file add.php look something like:

<?php
require_once 'db.php';

$insertStatement = $mysqli->prepare('INSERT INTO `macs` (`mac`) VALUES(?)');
$insertStatement->bindParam('s', $_GET['mac']);
$insertStatement->execute();

But before actually running this, you should set up a database schema (called "stats" above) with a table "macs", that will have at least one column for the mac address. Also, the aforementioned table for each of the previous days' number of users, i.e. run this in MySQL Workbench (or phpMyAdmin, or a separate PHP file... whatever):

CREATE TABLE `daily_users` (
  `day` date NOT NULL,
  `count` int(10) unsigned NOT NULL,
  PRIMARY KEY (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `macs` (
  `mac` char(17) NOT NULL,
  PRIMARY KEY (`mac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To actually read the number of users for a day, run this script (saved as count.php; will become important shortly):

<?php
require_once 'db.php';

echo $users = $mysqli->query('SELECT COUNT(`macs`.`mac`) AS `users` FROM `macs`')->fetch_object()->users;

Finally, to clear the count each day, and save the count for previous days, schedule this to run every 24 hours, starting at whatever time is your "day" cut off point:

<?php
require_once 'db.php';

$mysqli->begin_transaction();

require_once 'count.php';
if ($mysqli->query('TRUNCATE `macs`')) {
    $stmt = $mysqli->prepare('INSERT INTO `daily_users` (`day`, `count`) VALUES(?, ?)');
    $stmt->bindParam('si', $date = date('Y-m-d'), $users);
    if ($stmt->execute()) {
        $mysqli->commit();
    } else {
        $mysqli->rollback();
    }
}

Thu Sep 25, 2014 6:17 pm

BTW, I'd be curious to know if you succeed with the above or not... as I haven't actually tested it.

Fri Sep 26, 2014 9:09 pm

I let you know.

Count users with api

Count users with api

Re: Count users with api

Re: Count users with api

Re: Count users with api

Re: Count users with api

Re: Count users with api

Re: Count users with api

Re: Count users with api

Re: Count users with api

Re: Count users with api

Who is online