Community discussions

MUM Europe 2020
 
dleo
newbie
Topic Author
Posts: 26
Joined: Wed Mar 11, 2009 10:49 pm

Stats over PCQ

Mon Mar 19, 2012 5:46 pm

Hi,

This is my scenario, I'm a wireless ISP and I admin my bandwidth flow with Mikrotik. I have different types of customers, for that I'm using PCQ. I don't work with hotspots.

Now, here is my question, how i could get stats for every single user, like bandwidth usage (daily, weekly, monthly) and the storage for later purposes?

Thanks.

David.
 
User avatar
joshaven
Member
Member
Posts: 439
Joined: Fri May 06, 2011 1:50 am
Location: USA
Contact:

Re: Stats over PCQ

Mon Mar 19, 2012 6:19 pm

You need two steps here, one is data collection on the interface and the other is speed control with the queuing system.

You probably want to use RADIUS to collect the data. You can setup a RADIUS server like FreeRADIUS and have your accounting information returned to RADIUS. Then you will want to setup the PPPoE service and change your users over to using PPPoE which will cause every user to have their own interface on the router with its own stats. This will let you view the current usage via the router but it will also allow the usage data to be regularly returned to your RADIUS server. You can tie your RADIUS server to your billing system if you like or manage your radius server independently. I use FreeRADIUS to do this but there are other alternatives. FreeRADIUS is a free Linux package that works well, however there are windows alternatives if you are not comfortable with Linux.

RADIUS will not replace the PCQ queuing but rather it will replace the interface that your user is using. You will still need to queue the user. You will probably want to supply a RADIUS attribute like mikrotik-rate-limit which would crate a simple queue for each user or possibly mikrotik-address-list which would add the user to an address list upon which you could queue.
Joshaven Potter
Consulting and Business Services Available http://joshaven.com
 
dleo
newbie
Topic Author
Posts: 26
Joined: Wed Mar 11, 2009 10:49 pm

Re: Stats over PCQ

Mon Mar 19, 2012 9:13 pm

Hi joshven,

I know about RADIUS, I'm considering that option but it's problematic change my current schema.

Another important thing, that configuration builds one queue and one PPPoE interface for every customer, right? then, how do you deal with the recourses of your routerboad? I mean, how do you managment 1000 customers in one RB 1100 with that configuration? Can RADIUS and one RB1100 do that?

Thanks.
 
User avatar
joshaven
Member
Member
Posts: 439
Joined: Fri May 06, 2011 1:50 am
Location: USA
Contact:

Re: Stats over PCQ

Thu Mar 22, 2012 7:54 am

I don't have that many users on one RB1100. It can handle 200-300 or maybe more without too much trouble depending on how much your doing with your firewall etc your doing.
Joshaven Potter
Consulting and Business Services Available http://joshaven.com
 
rmichael
Forum Veteran
Forum Veteran
Posts: 718
Joined: Sun Mar 08, 2009 11:00 pm

Re: Stats over PCQ

Thu Mar 22, 2012 1:28 pm

Hi,

This is my scenario, I'm a wireless ISP and I admin my bandwidth flow with Mikrotik. I have different types of customers, for that I'm using PCQ. I don't work with hotspots.

Now, here is my question, how i could get stats for every single user, like bandwidth usage (daily, weekly, monthly) and the storage for later purposes?

Thanks.

David.
Firstly, how do you identify traffic from each of your user? IP, subnet, MAC address(es), interface?
Last edited by rmichael on Thu Mar 22, 2012 9:25 pm, edited 2 times in total.
 
User avatar
joshaven
Member
Member
Posts: 439
Joined: Fri May 06, 2011 1:50 am
Location: USA
Contact:

Re: Stats over PCQ

Thu Mar 22, 2012 3:29 pm

Firstly, can do you identify traffic from each of your user? IP, subnet, MAC address(es), interface?
Hopefully the two attached screenshot will help answer your questions... One is a screenshot of some interesting data from my database and the other is some interesting info from one of my RB1100's

If you want to run more then a couple hundred PPPoE clients on one router then you may be better off running a RB1100AHx2 or a PowerRouter 732.
You do not have the required permissions to view the files attached to this post.
Joshaven Potter
Consulting and Business Services Available http://joshaven.com
 
dleo
newbie
Topic Author
Posts: 26
Joined: Wed Mar 11, 2009 10:49 pm

Re: Stats over PCQ

Thu Mar 22, 2012 5:12 pm

Firstly, can do you identify traffic from each of your user? IP, subnet, MAC address(es), interface?
Hi,

I can identify every single user from my configuration MAC address, IP, subnet, etc, but I don't know what is it traffic per user, download, upload, because I'm working with PCQ, and only thing I see is queues.
 
rmichael
Forum Veteran
Forum Veteran
Posts: 718
Joined: Sun Mar 08, 2009 11:00 pm

Re: Stats over PCQ

Thu Mar 22, 2012 6:16 pm

The easiest way is to use off the shelf Trafficflow/netflow software like manageengine's NetFlow (which is free for one interface AFAIK).

Another, would be to create a passthrough rule for each of the IP/MAC/etc and pull byte stats from each with a script.

Also, I have not done it but I've seen others using script to pull traffic accounting stats via mikrotik's web server and store them in the database.
 
User avatar
joshaven
Member
Member
Posts: 439
Joined: Fri May 06, 2011 1:50 am
Location: USA
Contact:

Re: Stats over PCQ

Thu Mar 22, 2012 7:28 pm

The easiest way is to use off the shelf Trafficflow/netflow software like manageengine's NetFlow (which is free for one interface AFAIK).
Another, would be to create a passthrough rule for each of the IP/MAC/etc and pull byte stats from each with a script.
Also, I have not done it but I've seen others using script to pull traffic accounting stats via mikrotik's web server and store them in the database.
These options would defiantly answer the question and are good alternatives. I was likely reading too much into the question and thinking down the road towards the other benefits of the PPPoE solution like centrally managed speed control.

Furthermore in regards to the point of efficacy, which would defiantly come into play with 1000 users on an RB1100... I am not sure that there is any way to gather usage data without expecting more out of the CPU then it can handle without having a detrimental impact on latency.

I am very happy that we went with PPPoE. It allows me to manage the users speed, gather usage data and aids in troubleshooting connections. Also, many billing systems can be easily configured to display or bill based upon usage from the PPPoE connection and I have experienced that the PPPoE is a great fit for usage information, simple troubleshooting, IP consolidation, and central management. We have even seen improvements in throughput of our nearly overloaded AP's due to centralization of QOS settings etc which is more due to consistency across the network then to some inherent value of PPPoE. When I have a larger tower I use a PPPoE concentrator (ie local router) and a distribution router at that facility (generally an RB1200). With smaller towers I use one router either a RB1200 or a RB493AH. You could also use multiple PPPoE concentrators on one if you wanted to but I would probably just find something with enough CPU power to handle the load. I do not regret converting our network to using PPPoE... We are at the tail end of converting about 3000 connections to PPPoE.
Joshaven Potter
Consulting and Business Services Available http://joshaven.com
 
dleo
newbie
Topic Author
Posts: 26
Joined: Wed Mar 11, 2009 10:49 pm

Re: Stats over PCQ

Thu Mar 22, 2012 11:44 pm

I am very happy that we went with PPPoE. It allows me to manage the users speed, gather usage data and aids in troubleshooting connections. Also, many billing systems can be easily configured to display or bill based upon usage from the PPPoE connection and I have experienced that the PPPoE is a great fit for usage information, simple troubleshooting, IP consolidation, and central management. We have even seen improvements in throughput of our nearly overloaded AP's due to centralization of QOS settings etc which is more due to consistency across the network then to some inherent value of PPPoE. When I have a larger tower I use a PPPoE concentrator (ie local router) and a distribution router at that facility (generally an RB1200). With smaller towers I use one router either a RB1200 or a RB493AH. You could also use multiple PPPoE concentrators on one if you wanted to but I would probably just find something with enough CPU power to handle the load. I do not regret converting our network to using PPPoE... We are at the tail end of converting about 3000 connections to PPPoE.
Hi Mr. Potter,

Your solution is the ideal for a ISP business, really I congratulate you. Your decisition was accertany. Thanks you for shared your experience.

I'm happy working with PCQ, it allows better performance and simplicity. But, I'm thinking about a professional solution for my bussiness, and PPPoE provide that kind of solution.

I have a question for you my friend, I imagine that you are using some kind of billing system, in my company we develop our own system, so I would like heard, please, your recommendations for the integration of the billing system with RADIUS.

Thanks.
 
User avatar
joshaven
Member
Member
Posts: 439
Joined: Fri May 06, 2011 1:50 am
Location: USA
Contact:

Re: Stats over PCQ

Fri Mar 23, 2012 3:40 pm

so I would like heard, please, your recommendations for the integration of the billing system with RADIUS.
I am not sure how much info your looking for so I will give you something of an overview. If you have more questions feel free to ask specifics and I'll be happy to answer.

I use FreeRADIUS and have it setup with both MySQL and MSSQL (freeradius, freeradius-iodbc, & freeradius-mysql debian packages)

You may not need the MSSQL connector but our billing system runs on MSSQL so this was necessary for us. Another approach would be to write to the MySQL database from your billing software when records are changed... basically to do MySQL inserts to update usernames, passwords, speed plans, or assigned IP addresses (statically assigned).

Then in the sql folder, you'll find dialup.conf files that have the sql update commands. You can modify these as needed to write to the columns you need. Then you will need your billing system to read & write to these records/columns.

As far as db organization for auth, its pretty much a username & password field that are matched against. If it is successful then a radius accept message will be returned. A second part of this is that you can assign groups to the user. The group table is then consulted and all of the attributes for that group will be collected. An example of a group parameter could be: "mikrotik-address-list = gold_package" or "Framed-IP-Address = 1.2.3.4"

A finished RADIUS auth reply which informs the MikroTik router to create a simple queue looks like this
Sending Access-Request of id 91 to 127.0.0.1 port 1812
        User-Name = "radiustestaccount"
        User-Password = "testpassword"
        NAS-IP-Address = 204.xxx.xxx.12
        NAS-Port = 1812
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=91, length=108
        Framed-Protocol = PPP
        Framed-Compression = Van-Jacobson-TCP-IP
        MS-Primary-DNS-Server = 204.xxx.xxx.1
        MS-Secondary-DNS-Server = 8.8.8.8
        Mikrotik-Rate-Limit = "768k/3000k 0/0 0/0 0/0 8"
        Framed-IP-Address = 204.xxx.xxx.27
I am also using sqlippool so my IP addresses are tracked and maintained in MySQL as well.
Joshaven Potter
Consulting and Business Services Available http://joshaven.com

Who is online

Users browsing this forum: MSN [Bot] and 106 guests