Last Update: 10-April-2019
Task:
An Operator [from Amsterdam] wanted to receive email on a daily basis for the Daily usage of WAN link on the Mikrotik router.
The script should send daily email with reporting as shown below
- Daily usage report for specific interface
- Monthly usage report for specific interface
- Use human friendly values based on the values, auto convert values in KB/MB/GB/TB
- Download breakup details in KB/MB/GB/TB
- Daily Download Limit Alert , e.g: if daily download crosses X MB’s, warning should be added
- Monthly Download Limit Alert , e.g: if daily download crosses X MB’s, warning should be added
- Reset Daily/Monthly counters as per date/month change
- Most importantly: the statistics should be saved in a file so that reboot should not affect any reporting.
Solution:
Use following Script. All sections are well explained in comments sections. Make sure you read it carefully line by line, and modify items required.
In this example I used FILTER rules approach to collect the bytes. Surely there are more elegant ways to fetch the data like … Linux base MRTG, NMS like DUDE, above all SNMP and many others. I just used this method. You may select other as it suits you. This one worked neatly for me 🙂
You can customize it as per your requirements, like multi wan report, monthly report etc.
Note: This is unfinished script and requires improvement in many section, example addition of array is required to minimize coding in various section specially in month / bits byte selection algorithm. Tiem is short, will write more on it later.
Script Requirements:
Tested with Mikrotik 6.4x
- Schedule this script to run after 5 (or xx) minutes. When DATE change occurs, it will reset the counter file and filter rules counters.
- Create two FILTER rules in Mikrotik as following. SFP1 is the wan interface, so do change it accordingly.
/ip firewall filter add action=passthrough chain=forward comment=WAN1IN in-interface=sfp1 add action=passthrough chain=forward comment=WAN1OUT out-interface=sfp1
the Scripts !
First create a supporting script which will actually get previous date day/date.
1-) Script Name: func_shiftDate
# func_shiftDate - add days to date # Input: date, days # date - "jan/1/2017" # days - number # correct only for years >1918 uncomment for testing #:local date "jan/01/2100" #:local days 2560 ######################################## #:put "$date + $days" :local mdays {31;28;31;30;31;30;31;31;30;31;30;31} :local months {"jan"=1;"feb"=2;"mar"=3;"apr"=4;"may"=5;"jun"=6;"jul"=7;"aug"=8;"sep"=9;"oct"=10;"nov"=11;"dec"=12} :local monthr {"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"} :local dd [:tonum [:pick $date 4 6]] :local yy [:tonum [:pick $date 7 11]] :local month [:pick $date 0 3] :local mm (:$months->$month) :set dd ($dd+$days) :local dm [:pick $mdays ($mm-1)] :if ($mm=2 && (($yy&3=0 && ($yy/100*100 != $yy)) || $yy/400*400=$yy) ) do={ :set dm 29 } :while ($dd>$dm) do={ :set dd ($dd-$dm) :set mm ($mm+1) :if ($mm>12) do={ :set mm 1 :set yy ($yy+1) } :set dm [:pick $mdays ($mm-1)] :if ($mm=2 && (($yy&3=0 && ($yy/100*100 != $yy)) || $yy/400*400=$yy) ) do={ :set dm 29 } }; :local res "$[:pick $monthr ($mm-1)]/" :if ($dd<10) do={ :set res ($res."0") } :set $res "$res$dd/$yy" :return $res
Now create main script that we will schedule to run every five minutes.
2) wan1-total
# Script to collect WAN DATA USAGE using Firewall FILTER rules, and send data to admin by Email Daily. (or whenever dates changes) # And reset the counters on daily basis in night (or when date changes) . it will preserve the data in a file even if the router reboots. # Syed Jahanzaib / aacable @ hotmail . com # https://aacable.wordpress.com # 1 Revision : MAY-2015 # 2 Revision : JAN-2018 # 3 Revision : ARP-2019 # Calling another script which have datefunction :local shiftDate [:parse [/system script get func_shiftDate source]] :local DT ([/system clock get date]) :local LASTDAY [$shiftDate date=$DT days=-1] :local curTime [/system clock get time] :local curDate [/system clock get date] :local curYear [:pick $curDate 7 13] :local curMon [ :pick $curDate 0 3 ] :local curDay [:pick $curDate 4 6] :local CURRENTMONTH "$curMon" :local HOSTNAME ([/system identity get name]) # Set comments for firewall filter rules, change them as required :local INT # Set interface name which we want to monitor :local INT "sfp1" # I have used MB as limit value as it have more granular controls, modify it as required ! :global DAILYALERT :global MONTHLYALERT ## SETTING QUOTA FOR DAILY AND MONTHLY TRAFFIC (value in MB) :local DOWNLOADLIMITPERDAY "5120" :local DOWNLOADLIMITPERMONTH "153600" # Get Uptime :local UPTIME [/system resource get uptime] :local WAN1INCOMMENT :local WAN1OUTCOMMENT # Change the interface name accordingly, and make sure to enter the matching comments too. :local WAN1INCOMMENT "WAN1IN" :local WAN1OUTCOMMENT "WAN1OUT" # Acquire in/out counters from firewall filter rule :local BYTESOUT [/ip firewall filter get [/ip firewall filter find comment="$WAN1INCOMMENT"] bytes] :local BYTESIN [/ip firewall filter get [/ip firewall filter find comment="$WAN1OUTCOMMENT"] bytes] :local DAILYTOTAL :local DAILYTOTALKB :local DAILYTOTALMB :local DAILYTOTALGB :local DAILYTOTALTB :local DAILYTOTVALUEHUMANREADABLE :local COMPANY "ZAIB-COMPANY" :loca curMonNumber :local LASTMONTH :local LASTMONTHNAME :local 1stexectution :local FOOTER "This report was generated @ $curTime $curDay $curMon $curYear" if ($curMon = "jan") do={ :set $LASTMONTHNAME "December" } if ($curMon = "feb") do={ :set $LASTMONTHNAME "January" } if ($curMon = "mar") do={ :set $LASTMONTHNAME "Febraury" } if ($curMon = "apr") do={ :set $LASTMONTHNAME "March" } if ($curMon = "may") do={ :set $LASTMONTHNAME "April" } if ($curMon = "jun") do={ :set $LASTMONTHNAME "May" } if ($curMon = "jul") do={ :set $LASTMONTHNAME "June" } { :if ([:len [/interface find name="$INT" disabled=no]] != 0) do={ :local IPADDRESS "Wan interface has no IP address" :if ([:len [/ip address find actual-interface="$INT" disabled=no]] != 0) do={ :set IPADDRESS [/ip address get [:pick [/ip address find interface="$INT"] 0] address] } # :put $IPADDRESS } else={ :set $IPADDRESS "x.x.x.x" } } #:local IPADDRESS [/ip address get [find interface="$INT"] address] # set DATE TIME :local date :local time :set date [/system clock get date]; :set time [/system clock get time]; # SET GMAIL for sending email, make sure you have configured in /TOOLS,EMAIL option of mikrotik. :global gmailid :set gmailid "XXX@gmail.com" :global gmailsmtp :set gmailsmtp [:resolve "smtp.gmail.com"]; # Set your GMAIL Account Password :local gmailpass :set gmailpass XXXXX # Set your email where you want to receieve the alert :local mailsendto1 :set mailsendto1 aacable@XXXXXX.com #Starting Script Functions :if ([:len [/file find name="dailycounter.txt"]] > 0) do={ :log warning "Previous Data found, script is not running forthe first time..."; :set 1stexectution "N"; } else { :set 1stexectution "Y"; :log warning "It seems the script is running for the first time. creating necessary files to store counters.."; /file print file=dailycounter.txt where name=dailycounter.txt /delay delay-time=1; /file print file=monthlycounter.txt where name=monthlycounter.txt /delay delay-time=1; /file print file=counterslastupdate.txt where name=counterslastupdate.txt /delay delay-time=1; /file print file=counterslastupdatenormalformat.txt where name=counterslastupdatenormalformat.txt /delay delay-time=1; /file print file=counterslastupdatemonthnormalformat.txt where name=counterslastupdatemonthnormalformat.txt /delay delay-time=1; /file set monthlycounter.txt contents="1"; /delay delay-time=1; /file set dailycounter.txt contents="1"; /delay delay-time=1; /file set counterslastupdatenormalformat.txt contents="0"; /delay delay-time=1; /file set counterslastupdate.txt contents="0"; /delay delay-time=1; /file set counterslastupdatenormalformat.txt contents="0"; /delay delay-time=1; /file set counterslastupdatemonthnormalformat.txt contents="0"; /delay delay-time=1; } # Setting variables :local CURRENTDATE "$curDay$curMon$curYear" :local CURRENTMONTH "$curMon" :local LASTUPDATEDATE value=[/file get counterslastupdate.txt contents] :local LASTUPDATEDATENORMAL value=[/file get counterslastupdatenormalformat.txt contents] :local LASTUPDATEDATEMONTH value=[/file get counterslastupdatemonthnormalformat.txt contents] /file set counterslastupdatemonthnormalformat.txt contents=$curMon # Update counters last update with current date time /file set counterslastupdate.txt contents=$CURRENTDATE # Calculate data in MB to be displayed in LOG and email :set $DAILYTOTAL ($BYTESOUT+$BYTESIN) :set $DAILYTOTALKB ($DAILYTOTAL / 1024) :set $DAILYTOTALMB ($DAILYTOTAL / 1024 / 1024) :set $DAILYTOTALGB ($DAILYTOTAL / 1024 / 1024 /1024) :set $DAILYTOTALTB ($DAILYTOTAL / 1024 / 1024 /1024 /1024) ####### UPDATE USAGE in FILES FUNCTION START ######### # Get value from stored data :local BEFOREDAILYVALUE value=[/file get dailycounter.txt contents] :set $DAILYTOTALINFILE ($DAILYTOTAL+$BEFOREDAILYVALUE) /file set dailycounter.txt contents=$DAILYTOTALINFILE :local BEFOREMONTHLYVALUE value=[/file get monthlycounter.txt contents] :set $MONTHLYTOTALINFILE ($DAILYTOTAL+$BEFOREMONTHLYVALUE) /file set monthlycounter.txt contents=$MONTHLYTOTALINFILE ####### UPDATE USAGE in FILES FUNCTION END ######### :local DAILYTOTALFINAL value=[/file get dailycounter.txt contents] # Calculate data in MB to be displayed in LOG and email :set $DAILYTOTALKB ($DAILYTOTALFINAL / 1024) :set $DAILYTOTALMB ($DAILYTOTALFINAL / 1024 / 1024) :set $DAILYTOTALGB ($DAILYTOTALFINAL / 1024 / 1024 /1024) :set $DAILYTOTALTB ($DAILYTOTALFINAL / 1024 / 1024 /1024 /1024) #1000000000000 if (($DAILYTOTALFINAL>1) && ($DAILYTOTALFINAL<1000000)) do={ #:log warning "Daily Data is in KB" :set $DAILYTOTVALUEHUMANREADABLE "$DAILYTOTALKB KB" } if (($DAILYTOTALFINAL>1000000) && ($DAILYTOTALFINAL<1000000000)) do={ #:log warning "Daily Data is in MB" :set $DAILYTOTVALUEHUMANREADABLE "$DAILYTOTALMB MB" } if (($DAILYTOTALFINAL>1000000000) && ($DAILYTOTALFINAL<1000000000000)) do={ #:log warning "Daily Data is in GB" :set $DAILYTOTVALUEHUMANREADABLE "$DAILYTOTALGB GB" } if ($DAILYTOTALFINAL>1000000000000) do={ #:log warning "Daily Data is in TB" :set $DAILYTOTVALUEHUMANREADABLE "$DAILYTOTALTB TB" } # MONTHLY - Calculate data in MB to be displayed in LOG and email :set $MONTHLYTOTALKB ($MONTHLYTOTALINFILE / 1024) :set $MONTHLYTOTALMB ($MONTHLYTOTALINFILE / 1024 / 1024) :set $MONTHLYTOTALGB ($MONTHLYTOTALINFILE / 1024 / 1024 /1024) :set $MONTHLYTOTALTB ($MONTHLYTOTALINFILE / 1024 / 1024 /1024 /1024) #1000000000000 if (($MONTHLYTOTALINFILE>1) && ($MONTHLYTOTALINFILE<1000000)) do={ #:log warning "MONTHLY Data is in KB" :set $MONTHLYTOTVALUEHUMANREADABLE "$MONTHLYTOTALKB KB" } if (($MONTHLYTOTALINFILE>1000000) && ($MONTHLYTOTALINFILE<1000000000)) do={ #:log warning "MONTHLY Data is in MB" :set $MONTHLYTOTVALUEHUMANREADABLE "$MONTHLYTOTALMB MB" } if (($MONTHLYTOTALINFILE>1000000000) && ($MONTHLYTOTALINFILE<1000000000000)) do={ #:log warning "MONTHLY Data is in GB" :set $MONTHLYTOTVALUEHUMANREADABLE "$MONTHLYTOTALGB GB" } if ($MONTHLYTOTALINFILE>1000000000000) do={ #:log warning "MONTHLY Data is in TB" :set $MONTHLYTOTVALUEHUMANREADABLE "$MONTHLYTOTALTB TB" } :if ($MONTHLYTOTALMB>$DOWNLOADLIMITPERMONTH) do={ :set MONTHLYALERT "- WARNING: Your Monthly download limit have reached that is $DOWNLOADLIMITPERMONTH MB per month ..." } else { :set MONTHLYALERT "" #:log error "$MONTHLYALERT"; } ##### MONTHLY REPORT SECTION #:if ([:pick [/system clock get date] ([:find [/system clock get date] "/" ] + 1) 6 ] = "01") do={:set $1stday value= "Y"} #if ($1stday = "Y") do={ #:log error "1stday $1stday"; :if ($1stexectution = "N") do={ :if ($CURRENTMONTH = $LASTUPDATEDATEMONTH) do={ :log warning "MONTH not changed. No need to send email."; } else={ :log warning "Sending Email for monthly Report & Resetting all counters..."; :local es "$HOSTNAME - $IPADDRESS - Monthly Report - $LASTMONTHNAME - $MONTHLYTOTVALUEHUMANREADABLE was downloaded via $INT link." # Set Email Body :local eb "HOSTNAME: $HOSTNAME / $IPADDRESS \n\n$MONTHLYALERT\n\n- $MONTHLYTOTVALUEHUMANREADABLE were downloaded via $INT link on Month $LASTMONTHNAME (guessing year is $curYear\n\nMore Details:\nMonthly Download Breakup Details (in KB/Mb/GB/TB separetely) :\n\n-KB: $MONTHLYTOTALKB\n-MB: $MONTHLYTOTALMB\n-GB: $MONTHLYTOTALGB\n-TB: $MONTHLYTOTALTB\n\n\n $COMPANY\n\n\n $FOOTER" # Finally send email :log warning "Sending Email ..."; /tool e-mail send to=$mailsendto1 subject=$es body=$eb start-tls=yes user=$gmailid password=$gmailpass # Resetting Monthly Counter file because month has changed... #/file set dailycounter.txt contents="1"; /file set monthlycounter.txt contents="1"; } } :if ($DAILYTOTALMB>$DOWNLOADLIMITPERDAY) do={ :set DAILYALERT "- WARNING: Your Daily download limit have reached that is $DOWNLOADLIMITPERDAY MB per day ..." } else { :set DAILYALERT "" #:log error "$DAILYALERT"; } ##### DAILY REPORT SECTION # If date is changed (usually in night) , then send email using GMAIL , with the Data :if ($1stexectution = "N") do={ :if ($CURRENTDATE = $LASTUPDATEDATE) do={ :log warning "Date not changed. No need to send email." } else { :log warning "DATE changed, sending email for last day data usage and also reset the Firewall Counters ..."; :if ($DAILYTOTALMB>$DOWNLOADLIMITPERDAY) do={ :local DAILYALERT "- WARNING: Your Daily download limit have reached that is $DOWNLOADLIMITPERDAY MB per day ..." } # Set Email Subject :local shiftDate [:parse [/system script get func_shiftDate source]] :local DT ([/system clock get date]) :local LASTDAY [$shiftDate date=$DT days=-1] :local es "$HOSTNAME - $IPADDRESS - Daily Report - $LASTDAY - $DAILYTOTVALUEHUMANREADABLE downloaded via $INT link." # Set Email Body :local eb "HOSTNAME: $HOSTNAME / $IPADDRESS \n\n$DAILYALERT\n$MONTHLYALERT\n\n- $DAILYTOTVALUEHUMANREADABLE were downloaded via $INT link YESTERDAY (guessing Date is $LASTDAY ) \n- $MONTHLYTOTVALUEHUMANREADABLE were downloaded via $INT link on Month $curMon/$curYear\n\nMore Details: \n\n\ Daily Download Breakup Details (in KB/Mb/GB/TB separetely) : \n-KB: $DAILYTOTALKB\n-MB: $DAILYTOTALMB\n-GB: $DAILYTOTALGB\n-TB: $DAILYTOTALTB\n\n\n $COMPANY\n\n $FOOTER" # Finally send email :log warning "Sending Email for Daily Report ..."; /tool e-mail send to=$mailsendto1 subject=$es body=$eb start-tls=yes user=$gmailid password=$gmailpass /file set dailycounter.txt contents="1"; } } # Update Date time stamp in both files / zaib /file set counterslastupdate.txt contents=$CURRENTDATE /file set counterslastupdatenormalformat.txt contents=$curDate :log warning "DAILY $DT REPORT: $DAILYTOTVALUEHUMANREADABLE have been download via $INT interface $DAILYALERT"; :log warning "MONTH $curMon REPORT: $MONTHLYTOTVALUEHUMANREADABLE have been download via $INT interface $MONTHLYALERT"; /ip firewall filter reset-counters [find comment=$WAN1INCOMMENT] /ip firewall filter reset-counters [find comment=$WAN1OUTCOMMENT] :log error "Script Ends Here ..."; # Regard's # Syed Jahanzaib
End Results !
I am using scripts to collect data usage on daily basics but i won’t used counter to reset after 24H
date = may/23/2016
Time = 17:41:13
Uptime = 6d00:00:49
CPU Load = 7
WAN 1 = 0 GB WAN 1 RX= 0 MB WAN 1 TX= 0 MB
WAN 2 = 58 GB WAN 2 RX= 52321 MB WAN 2 TX= 7882 MB
WAN 3 = 0 GB WAN 3 RX= 0 MB WAN 4 TX= 0 MB
WAN 4 = 20 GB WAN 4 RX= 18417 MB WAN 4 TX= 2643 MB
Total RX+TX= 78 GB
LikeLike
Comment by UmmarHussain — May 24, 2016 @ 11:51 AM
for multiwan?
LikeLike
Comment by antonio — May 24, 2016 @ 12:10 PM
it can be modified for multi wan.
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — May 24, 2016 @ 1:12 PM
How did you run script? with scheduler ? which interval ? 24 hours ?
LikeLike
Comment by Damir — May 24, 2016 @ 2:43 PM
[…] https://aacable.wordpress.com/2016/05/2 … via-email/ […]
LikeLike
Pingback by Beginner Basics • Why is the data usage difference appearing? – Mags Forum Technology — August 15, 2019 @ 12:41 AM
not working again after im update ros 6.47.1 😦
LikeLike
Comment by inunaninu — July 19, 2020 @ 5:47 AM
Thanks for such a nice script. How I can check if this script is really sending email? Because I am not getting any email neither any error in Mikrotik Log window. I am using RB951G-2HnD with Version 6.48
LikeLike
Comment by Hassan Latif — January 30, 2021 @ 7:06 PM
enable EMAIL DEBUG in LOGGGIN section & re-run the script. You will see the email debug output via which you can troubelshoot.
LikeLike
Comment by Syed Jahanzaib / Pinochio~:) — February 9, 2021 @ 2:27 PM