Output to file with mac-address and hostname

RouterOS Scripting
1

Hello everyone,
I made this script with some code found on this forum.

How it works:

From terminal I added simple queue for my dhcp:

:for x from 100 to 254 do={/queue simple add name="queue-$x" max-limit=1M/10M target="192.168.0.$x"}

Now this script checks how many bytes each device has downloaded+uploaded, if exceded it limit the bandwith of the device.
It works but I want to print the output to a file (stats-YYYY-MM-DD.txt) to collect the stats and add also the macaddress and the hostname (macaddress,hostname,last-number-of-dchp,down,upl,total). Reset all counters and then upload like this:

/tool fetch address=my-server src-path=stats-YYYY-MM-DD.txt \
user=user mode=ftp password=pass dst-path=stats-YYYY-MM-DD.txt upload=yes

The output to the file might be something like this:

macaddress,Ipad,100,100,100,200
macaddress,PC,101,200,100,300
macaddress,android-fd45t,102,500,100,600
[...]




:local traf
:local tottraf
:local kbtot
:local mbtot
:local kbdl
:local mbdl
:local kbup
:local mbup
:local dntraf
:local uptraf
:local s
:local a
:for i from=100 to=253 do={
  :set s ("192.168.0.".$i."/32")
  :set a [/queue simple find where target=$s]
  :if ([:len $a] > 0) do={
    :set a [:pick $a 0 1]
    :set traf [/queue simple get $a bytes]
    :set uptraf [:pick $traf 0 [:find $traf "/"]]
    :set dntraf [:pick $traf ([:find $traf "/"]+1) [:len $traf]]
    :set tottraf ($uptraf+$dntraf)
    :set kbtot ($tottraf/1024)
    :set mbtot ($kbtot/1024)
    :set kbdl ($dntraf/1024)
    :set mbdl ($kbdl/1024)
    :set kbup ($uptraf/1024)
    :set mbup ($kbup/1024)
    :if (($uptraf+$dntraf) > 1073741824) do={
       /queue simple set $a max-limit="1M/4M"
    :log warning ("IP: ".$i." DL: ".$mbdl." UP: ".$mbup." TOT: ".$mbtot)
    } else={
    :log info ("IP: ".$i." DL: ".$mbdl." UP: ".$mbup." TOT: ".$mbtot)
    }
  }
}

Thank you

2

After hours of googling I have changed the file’s name.. Now the file is generated with the date so schedule the script at midnight you will have stats daily. Remain to add the macadress and hostname to identify better each user. If someone is interesting I’ll post some php code to see in a web page the graph.


Here the code:


# filename prefix
:local prefix "stats"

# months array
:local months ("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec");

# get Date
:local ds [/system clock get date]
# convert name of month to number
:local month [ :pick $ds 0 3 ];
:local mm ([ :find $months $month -1 ] + 1);
:if ($mm < 10) do={ :set mm ("0" . $mm); }
# set $ds to format YYYY-MM-DD
:set ds ([:pick $ds 7 11] . $mm . [:pick $ds 4 6])

:local fileContents "";
:local fileName ($prefix."-".$ds)
:local traf
:local tottraf
:local kbtot
:local mbtot
:local kbdl
:local mbdl
:local kbup
:local mbup
:local dntraf
:local uptraf
:local s
:local a
:for i from=100 to=253 do={
  :set s ("192.168.0.".$i."/32")
  :set a [/queue simple find where target=$s]
  :if ([:len $a] > 0) do={
    :set a [:pick $a 0 1]
    :set traf [/queue simple get $a bytes]
    :set uptraf [:pick $traf 0 [:find $traf "/"]]
    :set dntraf [:pick $traf ([:find $traf "/"]+1) [:len $traf]]
    :set tottraf ($uptraf+$dntraf)
    :set kbtot ($tottraf/1024)
    :set mbtot ($kbtot/1024)
    :set kbdl ($dntraf/1024)
    :set mbdl ($kbdl/1024)
    :set kbup ($uptraf/1024)
    :set mbup ($kbup/1024)
    :if ($mbtot > 0) do={
    :set fileContents ($fileContents.$i.",".$mbdl.",".$mbup.",".$mbtot."\n")
    }
    :if ($mbtot > 1000) do={
       /queue simple set $a max-limit="1M/7M"
    :log warning ("IP: ".$i." DL: ".$mbdl." UP: ".$mbup." TOT: ".$mbtot)
    } else={
    :log info ("IP: ".$i." DL: ".$mbdl." UP: ".$mbup." TOT: ".$mbtot)
    }
  }
}
/file print file=$fileName;
:delay 2s;
/file set $fileName contents="$fileContents";
3

Hi,
I wrote this but if the ip expired it doesn’t work saying “no such item” :frowning: Anyone could help?

  :set ip ("192.168.0.".$i)
  #if some device have the ip
:if ([:len [/ip dhcp-server lease find address="$ip"]] = 1) do={
  :set hostname [/ip dhcp-server lease get [find address="$ip"] host-name]
  :log info $hostname

Full code:

  

# filename prefix
:local prefix "stats"

# months array
:local months ("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec");

# get Date
:local ds [/system clock get date]
# convert name of month to number
:local month [ :pick $ds 0 3 ];
:local mm ([ :find $months $month -1 ] + 1);
:if ($mm < 10) do={ :set mm ("0" . $mm); }
# set $ds to format YYYY-MM-DD
:set ds ([:pick $ds 7 11] . $mm . [:pick $ds 4 6])

:local fileContents "";
:local fileName ($prefix."-".$ds)
:local traf
:local tottraf
:local kbtot
:local mbtot
:local kbdl
:local mbdl
:local kbup
:local mbup
:local dntraf
:local uptraf
:local s
:local a
:local hostname
:local ip
:for i from=100 to=253 do={
  :set s ("192.168.0.".$i."/32")
  :set a [/queue simple find where target=$s]
  :if ([:len $a] > 0) do={
    :set a [:pick $a 0 1]
    :set traf [/queue simple get $a bytes]
    :set uptraf [:pick $traf 0 [:find $traf "/"]]
    :set dntraf [:pick $traf ([:find $traf "/"]+1) [:len $traf]]
    :set tottraf ($uptraf+$dntraf)
    :set kbtot ($tottraf/1024)
    :set mbtot ($kbtot/1024)
    :set kbdl ($dntraf/1024)
    :set mbdl ($kbdl/1024)
    :set kbup ($uptraf/1024)
    :set mbup ($kbup/1024)
  :set ip ("192.168.0.".$i)
:if ([:len [/ip dhcp-server lease find address="$ip"]] = 1) do={
  :set hostname [/ip dhcp-server lease get [find address="$ip"] host-name]
  :log info $hostname
    :set fileContents ($fileContents.$i.",".$mbdl.",".$mbup.",".$mbtot."\n")
    }
    :if ($mbtot > 1000) do={
       /queue simple set $a max-limit="1M/7M"
    :log warning ("IP: ".$i." DL: ".$mbdl." UP: ".$mbup." TOT: ".$mbtot)
    } else={
    :log info ("IP: ".$i." DL: ".$mbdl." UP: ".$mbup." TOT: ".$mbtot)
    }
  }
}
/file print file=$fileName;
:delay 2s;
/file set $fileName contents="$fileContents";