Automated Usage Script without usermanager
Note: If you like this script please put your email address on the discussion page in format emailaddress [at] domainname [dot] com - then I will mail addresses there upon any major update.
If you have any questions you can also email me: andrew [at] accessplus [dot] com [dot] au
Contents
Summary
I wrote a usage tracking script based on the original Automated Billing Script
The way I run it is using a Mikrotik box setup to pass everything through transparently then I generated a Queue per IP address to keep track of the usage. On my test box (A PowerRouter 732) I currently have 3 class C blocks passing through with only 3% cpu (Multi CPU enabled).
This system is built for download-based billing only and will report the amount of downloads used by an IP address (or group if you setup a queue with multiple IP's) each month. (This could easily be switched around thou)
If you use the alternate version of the monthend script you can set the scheduler to run overseer every 1hour without having to worry about multiple reports coming through.
Additional, I've since written a smaller manual script for accounts so they can run this at any time and have the usage reports emailed through (see the manual usage report script)
Todo
- ability to backup to a file on the mikrotik so it will count both data in the queue currently + data usage saved in the file (in case you lose power to the device or have to restart it at any time) Rethought - now saves downloaded data onto the end of comment, so scripts can also be exported if required.
- modifying the monthend script further so that it can only be triggered on the last hour of the last day of the month. This means you could then run the usage script hourly to get more frequent updates. This has been done, see original and alternate versions of the Monthend Script See below the script for details on how it works.
- modify the usage and monthly report scripts to use kbyte variables instead of bytes (this shouldn't be any major issue thou, as the integer variables mikroik have allowed are 64bit, so a number can be as high as 9223372036854775807.
Current known bugs
none atm, let me know if you find any.
The Scripts
Overseer
Like the automated billing, this also has a calling script "Overseer"
:global found
/system script run monthend
:if ([$found] = "true") do={/system script run monthlyreport; :log info "--Completed Monthly Report--"} else={ /system script run usagereport; :log info "--Completed Usage Report--"}
Monthend
If it is the end of the month, the Monthly report must be run and counters reset, rather than the usage report. This finds our if it is end of month or not.
Original Version
:local date
:local time
:local day
:local month
:local year
:local hour
:local minute
:local yeardiv
:local yearmult
:local leapyear
:local lastday "0"
:global found ""
:set date [/system clock get date]
:set time [/system clock get time]
:set month [:pick $date 0 3]
:set day [:pick $date 4 6]
:set year [:pick $date 7 11]
:set hour [:pick $time 0 2]
:set minute [:pick $time 3 5]
:set yeardiv ($year / 4)
:set yearmult ($yeardiv * 4)
:if ([$yearmult] = $year) do={ :set leapyear true } else={ :set leapyear false }
:if ([$month] = "jan") do={ :set lastday "31" }
:if ([$month] = "feb") do={
:if ($leapyear = true) do={ :set lastday 29 }
:if ($leapyear = false) do={ :set lastday 28 } }
:if ([$month] = "mar") do={ :set lastday 31 }
:if ([$month] = "apr") do={ :set lastday 30 }
:if ([$month] = "may") do={ :set lastday 31 }
:if ([$month] = "jun") do={ :set lastday 30 }
:if ([$month] = "jul") do={ :set lastday 31 }
:if ([$month] = "aug") do={ :set lastday 31 }
:if ([$month] = "sep") do={ :set lastday 30 }
:if ([$month] = "oct") do={ :set lastday 31 }
:if ([$month] = "nov") do={ :set lastday 30 }
:if ([$month] = "dec") do={ :set lastday 31 }
:if ([$lastday] = $day) do={ :set found true } else={ :set found false }
Alternate version
If you want to run this report every hour, I've rewritten the script to check if its 12:00 on the 1st of the month, which is the only time it will then generate the final billing reports.
:local date
:local time
:local day
:local month
:local year
:local hour
:global found ""
:set date [/system clock get date]
:set time [/system clock get time]
:set day [:pick $date 4 6]
:set hour [:pick $time 0 2]
:if ([$day] = "01" ) do={ :if ([$hour] < "01" ) do={:set found true} else={ :set found false } } else={ :set found false }
Usage Report
The main usage report, takes the name of each queue and looks firstly for a hint that any data is contained in this. I have used ! to seperate the initial data block so If it finds a ! it will treat this name as data.
The data is then broken up from a name like this: Any Site name!50#info@yourcompany.com!00#0000 this then gives us $sitename (descriptive name) $gigs (gigabyte download limit) $email (address to email report to) $lastwarning (previous usage warning level) $bytesdownsaved (last total usage recording)
If you just name a script without any ! in it, this will be ignored in the processing (useful for naming devices without reporting)
:local content :local i :local sitename :local gigs :local email :local megstotal :local totalcurrent :local bytesdowncurrent :local bytesupcurrent :local megsdowncurrent :local megsupcurrent :local percentage :local lastwarning :local warninglevel :local warn :local update :local newwarning :local bytesdownsaved :local bytestotal #Script based on Automated billing script at http://wiki.mikrotik.com/wiki/AutomatedBilling #Details: #This script checks all current simple queues and using values stored in the queue name will allow you keep track of usage by each site #The details stored in the queue are broken down and used to determine if a user should also be sent an usage warning at 50 75 90 and 100% #You can create/modify a new entry as long as you follow this format #1) If you just want to name an entry you may do so like "XYZ Router" in the queue name #2) If you want to determine a data limit, enter it like "sitename!gigabytelimit#emailaddress!00" #Breaking it down, sitename is.. a descriptive name, gigabyte limit is the monthy limit, email address is the address you want notified, 00 is the last warning level-set this to 00 when creating a new site. # This version created by Andrew Cox - www.accessplus.com.au :log info "------ Begining Daily Usage Reports -------" #For each queue in the list :foreach i in=[/queue simple find name !=""] do={ #Pull name out of queue and divide up accordingly :set content [/queue simple get $i name] #Determine variables from name #Format is: sitename ! gigsallowed # who-to-email ! last warning level(0-50-75-90-99) :if ([:find $content "!"] != "") do={ :local pos1 [:find $content "!"] :local pos4 [:len $content] :local pos2 ([:find [:pick $content ($pos1+1) $pos4] "#"]+$pos1+1) :local pos3 ([:find [:pick $content ($pos2+1) $pos4] "!"]+$pos2) :set sitename [:pick $content 0 ($pos1)] :set gigs [:pick $content ($pos1+1) $pos2] :set email [:pick $content ($pos2+1) ($pos3+1)] :set totalcurrent [/queue simple get $i bytes] :set lastwarning [:pick $content ($pos3+2) ($pos3+4)] :local pos5 [:find $totalcurrent "/"] :local pos6 [:len $totalcurrent] :set bytesupcurrent ([:pick $totalcurrent 0 ($pos5)]) :set bytesdowncurrent ([:pick $totalcurrent ($pos5+1) $pos6]) :set megsupcurrent ($bytesupcurrent / 1048576) :set bytesdownsaved ([:pick $content ($pos3+5) $pos4]) :set bytestotal ($bytesdowncurrent + $bytesdownsaved) :set megsdowncurrent ($bytestotal / 1048576) #Begin calculating usage percentage :set percentage ( ( $bytestotal * 100) / ($gigs * 1073741824 ) ) :log info "$sitename: $percentage%" :if ([$percentage] < 50) do={ :set warninglevel "00" } :if ([$percentage] > 50) do={ :if ([$percentage] < 75) do={ :set warninglevel "50" } :if ([$percentage] > 75) do={ :set warninglevel "75" } } :if ([$percentage] > 75) do={ :if ([$percentage] < 90) do={ :set warninglevel "75" } :if ([$percentage] > 90) do={ :set warninglevel "90" } } :if ([$percentage] > 90) do={ :if ([$percentage] < 100) do={ :set warninglevel "90" } :if ([$percentage] > 100) do={ :set warninglevel "99" } } # Parse warning necessity :if ([$warninglevel] > $lastwarning ) do={ :set warn "true" ; :set update "true" } :if ([$warninglevel] = $lastwarning ) do={ :set warn "false" ; :set update "false" } :if ([$warninglevel] < $lastwarning ) do={ :set warn "false" ; :set update "true" } #Update Warning Levels :if ([$update] = true ) do={ :set newwarning $warninglevel } else={ :set newwarning $lastwarning } :log info "Email: $email" :if ([$warn] = true ) do={ /tool e-mail send to=$email from=noreply@example.com server=1.3.3.7 subject="$sitename: Usage at $percentage" body="This message is to inform you of the current usage for $sitename The current warning trigger is $warninglevel%. This site has downloaded $megsdowncurrent MB, which is $percentage% of the $gigs GB monthly download allowance. This is an Automatically generated E-mail that is sent out when users reach 50%, 75%, 90% and 100% of their cap. Traffic Monitor System, accounts@example.com Please report any errors in this message to someone@example.com" :log info "Sent Warning Level $warninglevel% to $email" } #Set new warning level on queue name /queue simple set $i name="$sitename!$gigs#$email!$newwarning#$bytestotal" /queue simple reset-counters $i } }
Monthly Report
Called by the overseer at the end of the month, this generates reports for Every site and then resets counters and the warning level back to 00
:local content :local i :local sitename :local gigs :local email :local megstotal :local totalcurrent :local bytesdowncurrent :local bytesupcurrent :local megsdowncurrent :local megsupcurrent :local percentage :local bytesdownsaved :local bytesdowntotal #Script based on Automated billing script at http://wiki.mikrotik.com/wiki/AutomatedBilling # This version created by Andrew Cox - www.accessplus.com.au :log info "------ Begining Monthly Reports -------" #For each queue in the list :foreach i in=[/queue simple find name !=""] do={ #Pull name out of queue and divide up accordingly :set content [/queue simple get $i name] #Determine variables from name #Format is: sitename ! gigsallowed # who-to-email ! last warning level(0-50-75-90-100) :if ( [ :find $content "!" ] != "" ) do={ :local pos1 [:find $content "!"] :local pos4 [:len $content] :local pos2 ([:find [:pick $content ($pos1+1) $pos4] "#"]+$pos1+1) :local pos3 ([:find [:pick $content ($pos2+1) $pos4] "!"]+$pos2) :set sitename [:pick $content 0 ($pos1)] :set gigs [:pick $content ($pos1+1) $pos2] :set email [:pick $content ($pos2+1) ($pos3+1)] :set totalcurrent [/queue simple get $i bytes] :local pos5 [:find $totalcurrent "/"] :local pos6 [:len $totalcurrent] :set bytesupcurrent ([:pick $totalcurrent 0 ($pos5)]) :set bytesdowncurrent ([:pick $totalcurrent ($pos5+1) $pos6]) :set megsupcurrent ($bytesupcurrent / 1048576) :set bytesdownsaved ([:pick $content ($pos3+5) $pos4]) :set bytesdowntotal ($bytesdowncurrent + $bytesdownsaved) :set megsdowncurrent ($bytesdowntotal / 1048576) #Begin calculating usage percentage :set percentage ( ( $bytesdowntotal * 100 ) / ( $gigs * 1073741824 ) ) :log info "$sitename: $percentage%" /tool e-mail send to=$email from=noreply@example.com server=1.3.3.7 subject="$sitename: Monthly Report" body="This message is to inform you of the full monthly usage for $sitename In this month this site has downloaded $megsdowncurrent MB, which is $percentage% of the $gigs GB monthly download allowance. This is an Automatically generated E-mail that is sent out at the end of each month. Traffic Monitor System, accounts@example.com Please report any errors in this message to someone@example.com" #Set warning level on queue name back to 00 and reset counters /queue simple set $i name="$sitename!$gigs#$email!00#0000" /queue simple reset-counters $i } }
Manual Usage Report
This script can be run manually at any time to send through the current usage report.
:local content :local i :local sitename :local gigs :local email :local megstotal :local totalcurrent :local bytesdowncurrent :local bytesupcurrent :local megsdowncurrent :local megsupcurrent :local percentage :local lastwarning :local warninglevel :local warn :local update :local newwarning :local bytesdownsaved :local bytestotal :local text :local combinedtext :set combinedtext "" #Scripts based on Automated billing script at http://wiki.mikrotik.com/wiki/AutomatedBilling # This version created by Andrew Cox - www.accessplus.com.au :log info "------ Begining Manual Usage Reports -------" #For each queue in the list :foreach i in=[/queue simple find name !=""] do={ #Pull name out of queue and divide up accordingly :set content [/queue simple get $i name] #Determine variables from name #Format is: sitename ! gigsallowed # who-to-email ! last warning level(0-50-75-90-99) :if ([:find $content "!"] != "") do={ :local pos1 [:find $content "!"] :local pos4 [:len $content] :local pos2 ([:find [:pick $content ($pos1+1) $pos4] "#"]+$pos1+1) :local pos3 ([:find [:pick $content ($pos2+1) $pos4] "!"]+$pos2) :set sitename [:pick $content 0 ($pos1)] :set gigs [:pick $content ($pos1+1) $pos2] :set email [:pick $content ($pos2+1) ($pos3+1)] :set totalcurrent [/queue simple get $i bytes] :set lastwarning [:pick $content ($pos3+2) ($pos3+4)] :local pos5 [:find $totalcurrent "/"] :local pos6 [:len $totalcurrent] :set bytesupcurrent ([:pick $totalcurrent 0 ($pos5)]) :set bytesdowncurrent ([:pick $totalcurrent ($pos5+1) $pos6]) :set megsupcurrent ($bytesupcurrent / 1048576) :set bytesdownsaved ([:pick $content ($pos3+5) $pos4]) :set bytestotal ($bytesdowncurrent + $bytesdownsaved) :set megsdowncurrent ($bytestotal / 1048576) #Begin calculating usage percentage :set percentage ( ( $bytestotal * 100) / ($gigs * 1073741824 ) ) :log info "$sitename: $percentage% - $megsdowncurrent MB used - Allowance is $gigs GB" :set text "$combinedtext \n $sitename: $percentage% - $megsdowncurrent MB used - Allowance is $gigs GB" :set combinedtext "$text" } } :log info "------ Ending Manual Usage Reports -------" :local time [/system clock get time] /tool e-mail send server="1.3.3.7" from="noreply@example.com" to="accounts@example.com" subject="Manual usage report" body="Site Usage report - Runtime: $time $text Regards, someone@example.com"
Final Notes
For those new to scripts, you can copy and paste these into winbox, but make sure that you at least remove the starting spacing from the comment lines (ones with #) if you don't they aren't treated as comments and will cause the script to error.
Quick trick to generate queues
#change the target address range to match what you want to generate.
:local x
:for x from 2 to 254 do={/queue simple add target-address="10.2.100.$x"}
would generate queues for 10.0.0.2-10.0.0.254 for you.
