Our users purchase a fixed monthly data-cap with excess charges if they exceed it. However as can be seen from the above the session-based approach of User-manager means that I clocked up 2.1GiB between Aug 21 and September 17 but have no way of allocating it between my August or September data-caps. Ditto all my other users.
At a very minimum I need a way of triggering session restarts across the board at midnight on the last/first of every month. Can anyone help with a way/trick/script to achieve this?
I’ve got a script that sends out invoices to customers roundabout the end of the month, but I can’t find a way to have a script figure out which day is the last day of the month - 30, 31, or 28 in Feb, and for a leapyear, 29.
So at this stage I have a script that runs every 30 days and 10 hours, starting on 27th of June at 21:00. That way, the script won’t send stuff too late, and it will send it within the month, every month, but close to the end. But I see where this can become a problem, so I’ve just sat down and considered letting the script update it’s own Scheduler entry to make it run on 28th of every month at 23:00 or something. That’s another option.
I’ll give you my script if you want it.
Here’s an official Feature Request for the guys at MT:
Character Handling.
Say for instance I have a date, formatted as “mmm/dd/yyyy”
I want to be able to extract the Month, Day and Year from that.
e.g.
:local date
:set date [/system clock get date] (Suppose it’s today - sep/28/2007)
:put $date(3)
Output: sep
Where 3 tells the Put command to only write the first 3 characters,
which can also be used to :set the first 3 characters of $date to a new variable.
Same goes for any other piece of information.
Extra function: (Suppose again that date is today - sep/28/2007)
:put $date(5,2)
Output: 28
Where 4 states at which character the Putting must begin,
and 2 states how many characters must be Put from that point on.
Of course, with a function like this another thing that would be handy
is a function that can determine and return, as integer, the length in characters of a variable.
If I had that function, I could write a script that calculates the last day of the month,
based on what month it is, and also calculate if it is a leapyear, based on what year it is.
SweetSunday - Let me know if you want my script.
-Krige
edit: In the meanwhile, I’ve discovered the :pick function. Thanks guys!
Yes, I’d like to see the script. Why don’t you put it up on the scripts section of the Wiki for everyone?
My real headache at the moment is keeping subscribers informed of their usage. Billing them excess charges if they go over their monthly limit when they have no way of knowing what their usage is makes for unhappy campers. Perhaps using a script like yours just to send them a note of their usage every few days would be very useful.
However I’ll still need a way of resetting the counters to zero at the beginning of each month.
You can reset counters to Zero with this command:
(Assuming you use User manager - my script is written for User Manager)
/tool user-manager user reset-counters X
Where X is the Index number for the user.
I’m doing this in my script.
It also calculates and informs clients of usage, but I have a package that is “Usage Based” - only in that event will it go ahead and calculate the Cost of your usage,
and put that into the invoice.
I guess what you can do is to let the script just run daily with scheduler,
but put the Reset Counters thing in a separate script, set to run on the last day of the month.
Thanks, I’ll give it a try - however I haven’t done any programming since playing around with BASIC on my good old Amstrad CP64!
One question - does ‘re-set counters’ wipe the history so that it’s no longer possible to check usage for previous months? (In which case I’d better save it somehow, somewhere, first).
Oh, and if someone will kindly take pity on me - I don’t fully (ie in the slightest) understand the relationship between local AAA, RADIUS with accounting and User-Manager. It seems to me from what little I comprehend of the documentation that these are three entirely separate ways of doing much the same thing under RouterOS. The differences, it seems to me, are:
With local AAA it’s all done in the same box which is fine if you have the resources,
With RADIUS you have the advantages of a remote, centralised server, but greater set-up complexity
User-Manager is essentially local AAA with web access.
Local users/AAA does everything dependent on the local MT box. Cannot be run from elsewhere.
Now, with Radius and User Manager, things get tricky.
The “/radius” thingy in RouterOS is simply a Radius Client daemon,
used to point your box to a Radius server - which can be Freeradius, or whatever, or User Manager. It does not have to be a Different machine - it can be the same machine.
But, keep in mind that User Manager runs independently, and is NOT MT’s internal user/aaa.
For remote (or local radius/UM) auth to work, you must set /ppp/aaa use-radius=yes,
and make an entry into /radius pointing to your radius server, whether it’s the same machine or otherwise. Apparently it is also possible to let your User Manager run on a different server, but I have not yet succeeded in achieving this.
User Manager behaves exactly the same as Radius. Essentially, it is a Radius engine.
In one of my previous posts Sergejs said that User Manager listens on ports 1812 and 1813, which is Radius’ default ports of late. The web interface of User Manager is basically the equivalent of Radius’ Dialup-Admin interface.
I haven’t done much programming beyond High School - Most of my stuff I write is mostly made up of spitballs and sticky tape. But I’ll be happy to help with debugging.
I enjoy doing it.
As for your Reset Counters question - As far as I understand Radius, and I’m assuming User Manager is built on the same principles of Accounting - is that there are two separate database tables for users and accounting. Now in User Manager, the Users table show the users’ total downloads, whereas the accounting table shows the downloads per individual session. What I’ve done in my FreeRadius accounting system is to write a PHP script that runs through the entire Accounting table and add up the downloads for each individual users, and separate the months or days in which the session took place. A bit of a drag, but it allows you the freedom of wiping the Total Downloads field in the Users table.
I have NOT TESTED THIS in User Manager. I will do it today.
So my advice - Don’t Run It Yet! until you hear from me again.
My method around that was to send Two E-mails out, as you can see in the script that I wrote - One to the Customer, and one to an E-mail dedicated to Accounts.
I simply store all those E-mails in an archive somewhere. If someone queries something, I can go pull up the E-mail. You can adapt the script to include all user’s sessions for a month in that E-mail as well.
I hope that helps. I’ve made a few tweaks and minor improvements on that Billing script in the meanwhile, will upload the new one to my webserver (and later to Wiki) for your viewing pleasure.
OK but “by does everything” I assume you mean uptime and data accounting kept in databases on the router itself. But where are they? When I do a > tool/userman/database print all I get is a ‘yes’ and a size report. Can they be shadowed to a remote db for security? Can they be directly accessed by a script to report certain potentially useful information like “between what times of day is the network busiest?” Can they be accessed via the userman web-pages even if the user info etc. isn’t entered under tool/userman?
I’ve been a fortnight trying to get FreeRADIUS and MySQL running on a Ubuntu box. I’ve had running servers but the morass of Linux etc. permissions and ownership defeated me as I could not get them to talk to each other let alone the Mikrotik client. I’ve just loaded RouterOS 3.05 onto the same machine in the hope it would do the same job as server but if you haven’t succeeded I don’t think I have a chance.
The problem is that User-manager doesn’t do what I need. My subscribers pay a monthly fee for unlimited time but a data limit. If they go over the limit they have to pay excess charges per MB as the Trust is potentially liable for the excess as it, too, has a data cap from the ISP. Our data cap is tx+rx. So ‘all’ I need is:
A monthly report of tx+rx data usage by subscriber, and
A way for subscribers to check their own usage so they can ration their cap over the month or choose to pay excess MBs.
One of my subscribers is paranoid about wireless radiation and so only powers up his Mikrotik box when he needs to go on-line, switching it off afterwards. In addition very shonky mains electricity in this remote part of New Zealand means we get two or three power interruptions a week at least (plus fried possum each time!) each of which takes most subscribers’ Mikrotik boxes down and creates a new PPPoE session when the power returns. So our user reports:
a) don’t begin and end neatly with the month beginning and end, being session-based, and
b) can add up to 20 - 30 or more sessions per month which I have to sit down and manually total up per customer just to make sure they didn’t exceed their cap.
Your amended script would solve problem 2 above in that I assume I could either get the Mikrotik box running user-manager to email each subscriber once a day with their monthly usage to date, or run it every day set to email the subscriber if their monthly usage is, say, 75%, 90% and 100% of their monthly cap. However my 532 which User-manager is running on tells me that ‘smtp.xtra.co.nz’ is “invalid value for argument server” in /tool e-mail although it is the address we all use in our email clients. What is ‘e-mail server=’ expecting?
I wish I could say that would be an interesting PHP script to see but it would be gobble-de-gook to me. It sounds, though, that I’d find it useful even if I don’t understand it
Most of the information is stored in /tool/user-manager/users
Here you’ll find All User’s personal info, Last Seen time, Total uptime used, total upload used, total download used (particularly handy for month-to-month billing)
This is the only information used in my script. (Oh, and a comment field) to generate my invoices. This information is (mostly) accessible via the Userman Web thingy.
I have no idea if it can be shadowed to a remote DB - I would like that. I’ll explore it a bit.
If you take a look at /tool/user-manager/log, that is basically a log of sessions and accounting. That information can be accessed, pretty much the same way as my script accesses information from /userman/users, and do calculations and generate reports. This is going to be a VERY tricky script, but you can potentially write something that can tell you what time of day is busiest, and how much is downloaded, uploaded, etc etc - But I’ve heard something about a Graphs module (a little dude-related package or something somewhere) that can do that for you. I have no idea what it is though, or where to begin looking for it.
Unfortionately, only the information under tool/user-manager can be accessed through the User Manager web interface. If it’s not there, you can’t access it with web interface.
I’ve run FreeRADIUS and MySQL on a Fedora box, and successfully got Mikrotik to Auth against it. In my opinion, FreeRADIUS/MySQL/PHP will give you a LOT more versatility to do anything you need, and transgress the limitations that UserManager has. If you want to take another stab at it, I know Fedora pretty well, I can help you with that - As far as I know, Ubuntu is’t too much different.
chuckle Sounds a lot like South Africa to me.
The script that I have does that, to a degree, but I flung together a script for another forum user that can lower the rate-limit when a user reaches a certain amount of up/downloads. I’m thinking of adapting my script to do the same. But it is easy to write a script that could:
Datacap-for-user = 3000 MB
Price per 100 mb = 7
User-Downloaded = 4200 MB
downloaded-minus-cap = 1200 MB
Excess-Fee = (1200 / 100) x 7 = 12 x 7 = 84
Base-package-fee= 400
Total-bill= 484
Your Monthly TX/RX report can be done in User Manager - It has that function.
Click on Reports, tick Unlimited Only, tick Amount, tick the two dropdown fields,
select This and Month from the two fields, and click Generate.
As for viewing usage:
Your user can log into User Manager and check it themselves.
Where you would normally type “http://your.router.address/userman”
Try this: “http://your.router.address/user” (No man at the end)
Here, a user can enter their own user/pass and see their own information.
This can also be done under PHP/MySQL. I’ve got something like that running.
User Manager already takes care of all that.
FreeRADIUS with Dialup-Admin can do that too.
I haven’t amended my script yet to send out E-mails when a user reach a percent of cap.
But I’ll probably do that sooner or later. However, with the fact that users can view their own usage on http://your.router.address/user, it’ll make such a script just a fancy extra. However, I’m going to try and do that anyway.
As for your E-mail - Mikrotik doesn’t support entering of FQDN addresses in to the fields - which is HIGHLY annoying. I spent two days trying to figure out how to enter my RADIUS server address, which is behind a NAT firewall with a dynamic IP, running a DynDNS account.
In short, your E-mail server entry is expecting an IP Address, and not an FQDN.
Now don’t get a heart attack - You can write a small script to Resolve the IP address from the FQDN, and put it in there. In my amended script, I’ve included this.
In the line, in the script, where I specify the TO and FROM addresses, and enter the subject and body, you also have to enter the “server=” value.
What I had there for my use was “server=192.168.16.10” because I had a mail server on the same network as the MT box while I was testing my script. But when I implemented my script in a remote location, the IP keeps changing daily. All I did was replace it like this:
“server=[:resolve your.mail.server]”
Problem solved.
When I started, it was gibberish to me too. Didn’t understand a thing of it. So hang in there - It’s loads of fun to spend a weekend or so just ripping apart code and learning what it does, and the get the satisfaction of writing something, using bits of code you got from somewhere else, to make something that you can actually use.
If you need some help on setting up FreeRADIUS and MySQL, I’ll be happy to assist.
I hope I answered all your questions, and perhaps left you with a few more.
The problem with generated reports as per above is that they’re session based, not date based. So if a session spans a month change-over it all gets allocated to the previous month - ie. if I generate a ‘this month’ report it only includes sessions that start during the month. Hence on my userman report my own usage for this month STARTS on September 15 as I have a UPS on my Mikrotik box and the AP is battery-powered anyway. I’m not even sure what happened on the 15th to trigger a new session but the one that ended that day commenced on August 25th. During that one session I clocked up a couple of GB in downloads, but have no way of telling how much was in August and how much in September.
The only subscriber who I have an accurate September usage figure for is the guy who turns his CPE on and off every day!
I did some 'reset-counter’ing today but although it seems to have done away with the 9GiB, 26 week uptime, type of records I get the same userman and individual reports I had before.
It’s now 11.26pm on Sunday 30th September, and the only think I can think to do is to use Winbox to individually reboot every subscriber’s CPE now in the hope it will trigger a new session for an accurate October reading.
How about cooking up a script that kills all connections at midnight on the last day of the month? I’ve just finished writing one that can determine whether or not it is the last day of the month. If it returns “True”, you can tell it to set a Scheduler event for that same day to trigger at 23:59:55 (5 secs before midnight) to kill all PPPoE sessions.
Bless his soul.
I noticed that too. I think it’s because the reports generate it’s information from the info that you’ll see under /tool/user-manager/log, whereas reset-counters only resets the counters under /tool/user-manager/users.
Just disable and re-enable your /interface/pppoe-server/server entry,
or type “/interface print” and remove all PPPoE entries. That will kill the session, and force the CPEs to reconnect - Assuming that they are capable of auto-reconnecting when the connection gets dropped, but judging from what you tell me, I think they should be fine.
If you need help pronto, I’m available on MSN. You can get my address off my forum user profile.
I think this is the way to go - less of a sledgehammer approach than forcing a reboot. From what I understand from the manual I think an attempt to start a new PPPoE session terminates the previous one - as long as there is a ‘one connection only’ limit on the link, I guess. So a simple script that disables the PPPoE interface, waits 5 seconds and then enables it should trigger a new session.
As an alternative to running such a script on each subscriber’s CPE would disabling/enabling the PPPoE interface at the AP which they all connect to have the same effect of forcing a reconnection/new session? Perhaps staggering it on a subscriber basis would be easier on resources than doing it at the AP and getting everyone trying to reconnect at the same time, though.
Yep, you understand correctly - That’ll do the trick - under /interface/pppoe-server/server, you can set the “one-session-per-host” to “yes”. That will allow one connection per MAC. If you’re using PPTP, you can set the “only-one” attribute in the /ppp/profile for your vpn links. I’m using both PPPoE and PPTP, but I allow multiple connections.
Good point, I didn’t even think about that. Of course, implementing it in every single CPE could take a while, and could be difficult to monitor. Possibly a few CPEs might not execute the script. So, my suggestion - Do that. Implement the script in every single CPE, and as a safety measure, should one of them not run the script, implement a little script that kills all remaining PPPoE links under “/interface” or /interface/pppoe-server/
Sorry I haven’t updated the billing script yet - Been busy getting one of my home machines back online that went AWOL on me. Oy.
Well, I have failed totally. Even trying to get a cut-down version of your script running on our spare 532 has failed. I can send emails from it by command-line, but not from script. Can you see why this doesn’t work, 'cos I’m damned if I can?
I’m hoping that I can put each subscriber’s monthly data-cap in their phone-number field and use that with your $gigstotal field to give them a percentage of their data-cap used to date.
Down the way - if I ever get this to work at all - I’d like to use that percentage value to trigger warning emails (“You have used (75)(90)(100)% of your month’s allowance”) rather than an e-mail every day, utilising the location field to set a flag that one has already been sent.
But at the moment I can’t get even the simplest script to work.
Was wondering if you came right, seeing as you haven’t posted in a while.
Small bug in the script - You need to actually draw a list of items for the FOREACH to check against (and not just point to a directory) - So amend your code as shown:
:local date
:local cap
:local clientname
/tool user-manager user
:foreach i in=[/tool user-manager user find subscriber=admin] do={
:set date [/system clock get date]
:set clientname [get $i last-name]
:set cap [get $i phone]
/tool e-mail send to=admin@clovabay.net.nz from=inclova@ihug.co.nz server=210.54.141.2 subject=“test mail” body=“test
mail
$date
$clientname
$cap”
}
And Voila!
That works for me running 3.0rc5 on an AMD machine.
Haven’t tested it on an RB532 yet, but I have a 532A running 3.0rc6, will upload it there pretty soon.
I can’t see anything wrong with it.
I don’t know if it’s possibly the versions that may make the difference…
I’ve just tested it again by retyping every single line of your code, and it works.
What I do often to debug my scripts, is to use the “:put” command.
So after each ‘:set’ command, use a ‘:put’ command to just print what you’ve assigned to a variable on screen, just to make sure everything gets assigned properly.
Perhaps that might show something.
A last resort would be for you to export your scripts to a file, and E-mail me the file.
I’ll load it into my box and see if there’s something I’ve just missed completely.
That didn’t work either, but I haven’t been able to get :put to work in any script and had assumed it required a target for its output whenever it wasn’t being called from the console itself.
However I’ve just found out it still doesn’t work even when using a terminal within Winbox or calling the script in a Putty terminal.
I don’t have 3.0rc on any boxes because despite the fact that the documentation on 2.9 is appallingly lagging the OS it’s still vastly better than the documentation on 3.0 (ie there isn’t any!)
Sure, I still have install files of 2.9.46 on disk here, will load that up and see what happens.
The :put command is extremely simple -
If you have a script - and all the script contains is the following: #Begin Script
:put hello
:put bye #End Script
And you run it, you should see this echoed on screen:
hello
bye
So the fact that :put doesn’t work in that script is indicating to me that something else might be wrong. But I’ll start loading 2.9.46 onto my x86 machine and try it out.
Will post again as soon as I have an answer.
Terminal vt102 detected, using multiline input mode
[admin@St. Omer] > system script
[admin@St. Omer] system script> print
0 name=“test” owner=“admin” policy=ftp,read,write,policy,test,winbox,sniff last-started=oct/12/2007 20:52:13 run-count=3
source=
:local date
:local cap
:local clientname
/tool user-manager user
/tool user-manager user print
:foreach i in=[/tool user-manager user find subscriber=admin] do={
:set date [/system clock get date]
:set clientname [get $i last-name]
:set cap [get $i phone]
/tool e-mail send to=admin@clovabay.net.nz from=inclova@ihug.co.nz server=210.54.141.2 subject="test mail
" body=“test mail
$date
$clientname
$cap”
}
1 name=“hello” owner=“admin” policy=ftp,read,write,winbox run-count=0 source= #Begin Script
:put hello
:put bye #End Script
[admin@St. Omer] system script> run 1
[admin@St. Omer] system script>
Unless there’s some other fundamental and undocumented feature, like a need to enclose the entire script in {}? (That doesn’t work either, but it’s a good eg of what I mean.)
Is there any way of Xfrerring this thread to the scripts forum, as that’s where it belongs now?
OK, I loaded RouterOS 3.0rc5 onto an i386 machine and the hello/bye script runs OK. But it won’t work for me on a 532 running 2.9.46, or 2.9.40. I can’t test it on my own CPE, a 112 with 2.9.46, as the ‘new terminal’ from Winbox won’t fire up - reckons the console has crashed or the system is completely busy! (which it isn’t.)
So it does seem to be a release issue.
I’ll load up some user details on the 3.05 machine and see if I can get the user email script to work in it.
