I have read this: viewtopic.php?t=78145
But it is not working for me

My application:
I have a MySQL db of all ppp secrets, these get sent to the mikrotik via the PEAR2 API.
I need to see how many of the ppp clients are active and then mark them as active on the local mysql db.

Or, any other way that this can be accomplished.
In other words, I basically just need to compare with and get the result

By the way, I know this is a php question, but its related in a way, and maybe someone has come across this while using the api; When I send a large amount of data, like 1000 ppp secrets, the script 'hangs' until its complete. How would I run this in the background?

What is your actual issue?
You are a cut-and-paste PHP programmer and a script discussed on the forum does not work for you?
Why don't you duscuss it in that other topic then?
Or you want to start something new? I would think you can retrieve those lists, store them in (temporary) tables in your db, and
run some SQL query to get the result you want.

If you have shell access to your web server, you can run a PHP from the command line. You can also add it as a startup script if you want it to also restart with a server restart. Or use cron if you want to run it at regular intervals rather than continuously.

If you run it continuously, you can use the "follow" argument to make the "/ppp active print" command never stop, and keep return replies when new users become active or stop being active. With that information in place, you can mark them in your DB accordingly and/or do whatever else you need to do.

e.g.

I have a MySQL db of all ppp secrets, these get sent to the mikrotik via the PEAR2 API.
I need to see how many of the ppp clients are active and then mark them as active on the local mysql db.

Is there a reason you aren't just using RADIUS (ex. FreeRADIUS), which does all this for you (and more)? 1000 PPP secrets is a lot, I would use RADIUS well before that point. What you are doing now is inventing some strange substitute for a wheel that doesn't work as well as a wheel.

I have a MySQL db of all ppp secrets, these get sent to the mikrotik via the PEAR2 API.
I need to see how many of the ppp clients are active and then mark them as active on the local mysql db.

Is there a reason you aren't just using RADIUS (ex. FreeRADIUS), which does all this for you (and more)? 1000 PPP secrets is a lot, I would use RADIUS well before that point. What you are doing now is inventing some strange substitute for a wheel that doesn't work as well as a wheel.

Yes, I am sure radius is better, if you have any idea on how to pull this information from freeradius or If you would be so kind as to point me in the correct direction, I will appreciate it.
Even with radius, I would like to have an alternative solution too for smaller or different scenarios. For example, some rural ISPs using satellite backhauls have unstable connections to radius servers, for them ppp secrets work better.

If you have shell access to your web server, you can run a PHP from the command line. You can also add it as a startup script if you want it to also restart with a server restart. Or use cron if you want to run it at regular intervals rather than continuously.

If you run it continuously, you can use the "follow" argument to make the "/ppp active print" command never stop, and keep return replies when new users become active or stop being active. With that information in place, you can mark them in your DB accordingly and/or do whatever else you need to do.

e.g.

Code: Select all

$client = new RouterOS\Client('192.168.88.1', 'admin', 'password');
$mysqli = new mysqli('127.0.0.1', 'root', 'password', 'mydb');

//NOTE: "username" should be unique and/or primary key in the "pppoe" table
$markStmt = $mysqli->prepare('
    INSERT INTO `pppoe` SET `username` = ?,
        `ip` = ?, `mac` = ?, `active` = ?
    ON DUPLICATE KEY UPDATE
        `ip` = ?, `mac` = ?, `active` = ?
');
$activeFollowRequest = new RouterOS\Request('/ppp active print follow=""');
$client->sendAsync(
    $activeFollowRequest,
    function (RouterOS\Response $response) use ($markStmt)  {
        $markStmt->bind_param(
            'sssissi',
            $response->getProperty('name'),
            $response->getProperty('address'),
            $response->getProperty('caller_id'),
            'true' === $response->getProperty('.dead'),
            $response->getProperty('address'),
            $response->getProperty('caller_id'),
            'true' === $response->getProperty('.dead')
        );
        $markStmt->execute();
    }
);
$client->loop();

Thank you very much for this info.

Yes, I am sure radius is better, if you have any idea on how to pull this information from freeradius or If you would be so kind as to point me in the correct direction, I will appreciate it.
Even with radius, I would like to have an alternative solution too for smaller or different scenarios. For example, some rural ISPs using satellite backhauls have unstable connections to radius servers, for them ppp secrets work better.

We use RADIUS over satellite but we have QoS over satellite so we can prioritize the RADIUS packets. With the prioritization, if bandwidth is limited, the RADIUS packets will not get dropped and we have not lost any packets as a result.

Set up FreeRADIUS 3 with MySQL backend, configured for RADIUS accounting to SQL, and set up your PPPoE to authenticate against this. You will then get usage data per customer that let you create simple bandwidth usage graphs, and can see what customers are online by querying from the database, if the customer's accounting session has NULL stop time then they are currently online. There are multiple web interfaces available for FreeRADIUS, we use DaloRADIUS. If you use DaloRADIUS as a web interface, do not use their freeradius schema or their combined daloradius/freeradius schema to set up your SQL database because they are both wrong. Instead use the SQL schema bundled with FreeRADIUS 3 itself and then add the daloradius-specific schema (mysql-daloradius.sql)

You will then get usage data per customer that let you create simple bandwidth usage graphs,

Thank you, I understand all that you have said, I will now setup a freeradius server and check it out.
So, instead of sending the ppp secrets I will just be sending the same details to the Radius MySQL database.
If possible (and not against forum rules) please elaborate on how to get daily, weekly or monthly bandwidth usage. If I understand correctly, the session data is stored in the 'radacc' table, which includes traffic stats for the session.
I already can create graphs, I just need the data.

If I understand correctly, the session data is stored in the 'radacc' table, which includes traffic stats for the session.
I already can create graphs, I just need the data.

Yes, radacct contains the data, the fields acctinputoctets and acctoutputoctets contain the number of bytes input and output respectively during the session so far. These values will be updated each time the MIkroTik sends an interim update to the RADIUS server (how often, or whether or not, the interim update is set is configured on the MikroTik under PPP->Secrets tab->"PPP Authentication & Accounting" button, at the top). Simply set the interim update interval to 15 minutes or something reasonable like that, and then every 15 minutes, check the acctinputoctets and acctoutputoctets value and find the difference between those and the values from 15 minutes before for the same session ID, then multiply by 8 (for bits) and divide by 15*60 (15 minutes * 60 seconds) to get the bits/sec rate for that 15 minute period. You can set it lower, ex. to 5 minutes, if you want to get better resolution in the graph, then divide the value appropriately.

Keep in mind also, a new session will result in a new session ID and therefore a new record in radacct, so if the user disconnects and connects three times in an hour, you will need to check three records to add up the total usage in the hour.

You might want to set up your code so that when it checks the value it divides by the number of minutes since the last update from the device made it to the radius server (I believe that is the acctupdatetime field), that way if you have it set for 15 minutes and say one packet is lost and the next one is 30 minutes later, you calculate the change in byte count over 30 minutes instead of 15 minutes, otherwise you will have an unnatural spike in the graph.

Also, when using RADIUS, it is safest to have a script that runs on a regular basis that checks for radacct table entries that have a NULL acctstoptime but have not had any updates for a long time, since (as long as the MikroTik interim update interval is set properly) this indicates the customer is no longer connected but that the Acct-Stop packet was dropped. This script would simply set the acctstoptime (instead of it being NULL) so that the session is not left hanging, as a false-open session.

Even though it uses UDP, this setup can be pretty reliable as long as you use QoS to prioritize the RADIUS accounting update packets to avoid drops.