MIKROTIK BANDWIDTH MONITOR – SIMPLE

<<< Install Apache >>>

sudo apt install apache2
----------------------------------------
sudo systemctl restart apache2
sudo systemctl reload apache2
sudo systemctl disable apache2
sudo systemctl enable apache2
-----------------------------------------

<<< Install MySQL --- SQLite >>>

sudo apt install sqlite3
or
sudo apt-get install mysql-server

<<< Install PHP 8 >>>

wget -q https://packages.sury.org/php/apt.gpg -O- | sudo apt-key add -
sudo echo "deb https://packages.sury.org/php/ stretch main" | tee /etc/apt/sources.list.d/php.list
sudo apt update
sudo apt install php php-sqlite3 libapache2-mod-php
or
sudo apt install php php-mysql libapache2-mod-php

<<<<<<<<<< Cấu hình PHP để nhận SQLite >>>>>>>>>>

sudo nano /etc/php/8.0/apache2/php.ini
Tìm dòng “;extension=sqlite3” >> bỏ dấu “;” >> “extension=sqlite3”
<<< Tạo database chứa record bandwidth của Mikrotik >>>
<<<<<< thư mục chứa file php, database là /var/www/html/mikrotik >>>>>>
sqlite3 /var/www/html/mikmonitor/mikstats.db

CREATE TABLE "devices" (

"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"sn" TEXT,
"comment" VARCHAR,
"last_check" DATETIME,
"last_tx" INT,
"last_rx" INT
);

CREATE TABLE "traffic" (

"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"device_id" INT,
"timestamp" DATETIME,
"tx" INT,
"rx" INT
);

<<< Phân quyền cho thư mục chứa database >>>

Chmod 777 /var/www/html/mikmonitor

<<< Phân quyền truy cập cho file database mikstats.db >>>

Chmod 0766 /var/www/html/mikmonitor/mikstats.db

<<< SCRIPT >>>
:local sysnumber [/system routerboard get value-name=serial-number]
:local txbytes [/ip firewall mangle { get [find comment=wan-tx] bytes }]
:local rxbytes [/ip firewall mangle { get [find comment=wan-rx] bytes }]
/tool fetch url=("http://192.168.103.246/mikmonitor/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [/ip firewall mangle find comment=wan-tx]
/ip firewall mangle reset-counters [/ip firewall mangle find comment=wan-rx]
:log info ("WAN traffic counters have been reset!")

<<<<<<<<<< SCRIPT copy to TERMINAL MIKROTIK >>>>>>>>>>

/system script
add name=MonitorBandwidth \
policy=read,test source=":local sysnumber [/system routerboard get value-n\
ame=serial-number]\r\
\n:local txbytes [/ip firewall mangle { get [find comment=wan-tx] bytes }]\
\r\
\n:local rxbytes [/ip firewall mangle { get [find comment=wan-rx] bytes }]\
\r\
\n/tool fetch url=(\"http://192.168.103.246/mikmonitor/collector.php\\\?sn\
=\$sysnumber&tx=\$txbytes&rx=\$rxbytes\") mode=http keep-result=no\r\
\n/ip firewall mangle reset-counters [/ip firewall mangle find comment=wan\
-tx]\r\
\n/ip firewall mangle reset-counters [/ip firewall mangle find comment=wan\
-rx]\r\
\n:log info (\"WAN traffic counters have been reset!\")"
<<<<< SCHEDULE >>>>>

/system scheduler
add interval=30m name=MonitorBandwidth on-event=":local sysnumber [/system rou\
terboard get value-name=serial-number]\r\
\n:local txbytes [/ip firewall mangle { get [find comment=wan-tx] bytes }]\
\r\
\n:local rxbytes [/ip firewall mangle { get [find comment=wan-rx] bytes }]\
\r\
\n/tool fetch url=(\"http://192.168.103.246/mikmonitor/collector.php\\\?sn\
=\$sysnumber&tx=\$txbytes&rx=\$rxbytes\") mode=http keep-result=no\r\
\n/ip firewall mangle reset-counters [/ip firewall mangle find comment=wan\
-tx]\r\
\n/ip firewall mangle reset-counters [/ip firewall mangle find comment=wan\
-rx]\r\
\n:log info (\"WAN traffic counters have been reset!\")" policy=read,test
# id------devicename
# 1------VNPT-01 -------- comment: vnpt1-down/up
# 2------VNPT-02 -------- comment: vnpt2-down/up
# 3------VLAN-103 -------- comment: vlan103-down/up
# 4------VLAN-105 -------- comment: vlan105-down/up
# 5------DESK-01 -------- comment: desk-01-down/up
# 6------VLAN-099 --------- comment: vlan099-down/up

:local sysnumber "DESK-01"

:local rxbytes [/ip firewall mangle { get [find comment=desk-01-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=desk-01-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=desk-01-down]
/ip firewall mangle reset-counters [find comment=desk-01-up]

:local sysnumber "VLAN-105"

:local rxbytes [/ip firewall mangle { get [find comment=vlan105-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=vlan105-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=vlan105-down]
/ip firewall mangle reset-counters [find comment=vlan105-up]

:local sysnumber "VLAN-103"

:local rxbytes [/ip firewall mangle { get [find comment=vlan103-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=vlan103-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=vlan103-down]
/ip firewall mangle reset-counters [find comment=vlan103-up]

:local sysnumber "VLAN-099"

:local rxbytes [/ip firewall mangle { get [find comment=vlan099-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=vlan099-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=vlan099-down]
/ip firewall mangle reset-counters [find comment=vlan099-up]

:local sysnumber "VNPT-01"

:local rxbytes [/ip firewall mangle { get [find comment=vnpt1-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=vnpt1-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no

:local sysnumber [/system routerboard get value-name=serial-number]

:local txbytes [/ip firewall mangle { get [find comment=vnpt1-up] bytes }]
:local rxbytes [/ip firewall mangle { get [find comment=vnpt1-down] bytes }]
/tool fetch url=("http://192.168.103.246/mikmonitor/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no

/ip firewall mangle reset-counters [find comment=vnpt1-down]

/ip firewall mangle reset-counters [find comment=vnpt1-up]
/ip firewall mangle
add action=passthrough chain=forward comment=vnpt1-up out-interface=\
pppoe-out1
add action=passthrough chain=forward comment=vnpt1-down in-interface=\
pppoe-out1
add action=passthrough chain=forward comment=desk-01-up src-address-list=\
speclanip
add action=passthrough chain=forward comment=desk-01-down dst-address-list=\
speclanip
add action=passthrough chain=forward comment=vlan105-up in-interface=VLAN105
add action=passthrough chain=forward comment=vlan105-down out-interface=\
VLAN105
add action=passthrough chain=forward comment=vlan103-up in-interface=VLAN103
add action=passthrough chain=forward comment=vlan103-down out-interface=\
VLAN103
add action=passthrough chain=forward comment=vlan099-up in-interface=VLAN099
add action=passthrough chain=forward comment=vlan099-down out-interface=\
VLAN099
:local sysnumber "DESK-01"
:local rxbytes [/ip firewall mangle { get [find comment=desk-01-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=desk-01-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=desk-01-down]
/ip firewall mangle reset-counters [find comment=desk-01-up]

:local sysnumber "VLAN-105"

:local rxbytes [/ip firewall mangle { get [find comment=vlan105-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=vlan105-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=vlan105-down]
/ip firewall mangle reset-counters [find comment=vlan105-up]

:local sysnumber "VLAN-103"

:local rxbytes [/ip firewall mangle { get [find comment=vlan103-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=vlan103-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=vlan103-down]
/ip firewall mangle reset-counters [find comment=vlan103-up]

:local sysnumber "VLAN-099"

:local rxbytes [/ip firewall mangle { get [find comment=vlan099-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=vlan099-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=vlan099-down]
/ip firewall mangle reset-counters [find comment=vlan099-up]

:local sysnumber "VNPT-01"

:local rxbytes [/ip firewall mangle { get [find comment=vnpt1-down] bytes }]
:local txbytes [/ip firewall mangle { get [find comment=vnpt1-up] bytes }]
/tool fetch url=("http://192.168.103.246/test/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no

:local sysnumber [/system routerboard get value-name=serial-number]

:local txbytes [/ip firewall mangle { get [find comment=vnpt1-up] bytes }]
:local rxbytes [/ip firewall mangle { get [find comment=vnpt1-down] bytes }]
/tool fetch url=("http://192.168.103.246/mikmonitor/collector.php\?sn=$sysnumber&tx=$txbytes&rx=$rxbytes") mode=http keep-result=no
/ip firewall mangle reset-counters [find comment=vnpt1-down]
/ip firewall mangle reset-counters [find comment=vnpt1-up]