Mikrotik: Using Firewall Filters to Acquire Wan Data Usage Report via Email

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

1. Schedule this script to run after 5 (or xx) minutes. When DATE change occurs, it will reset the counter file and filter rules counters.
2. 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 !