Hi guys, I am needing to graph or have a monthly report of the amount of hosts connected to my mikrotik, basically graphing the DHCP lease could anyone give me an idea how to do it?
Ty
How I would tackle this in broad lines:
SNMP access to your router
Get DHCP leases (there should be an OID for that, if I’m not mistaking)
Dump data to other environment
Use your graphing tool of choice to display data
OR …
Use Jotne’s SPLUNK solution to get a lot more out of it.
http://forum.mikrotik.com/t/tool-using-splunk-to-analyse-mikrotik-logs-4-0-graphing-everything/153043/1
PLEASE CORRECT THE TITLE
Based on another I just finish:
http://forum.mikrotik.com/t/help-with-this-script/157065/1
{
:global arrMonths {jan="01";feb="02";mar="03";apr="04";may="05";jun="06";jul="07";aug="08";sep="09";oct="10";nov="11";dec="12"}
:local dhcpName "dhcp-laboratorio"
:local filename "leasehistory.txt"
:local ndate [/system clock get date]; :local ntime [/system clock get time]
:local now "$[:pick $ndate 7 11]-$($arrMonths->[:pick $ndate 0 3])-$[:pick $ndate 4 6] $[/system clock get time]"
/file
:if ([:len [find where name="flash" and type="disk"]] = 1) do={:set filename "flash/$filename"}
:if ([:len [find where name=$filename]] = 0) do={print file="$filename"; :delay 5s; set $filename contents=""}
:local currentLease [:len [/ip dhcp-server lease find where server=$dhcpName]]
:delay 4s
:local filecontent [get $filename contents]
:local newfilecontent "$currentLease\t$now\r\n$filecontent"
set $filename contents=$newfilecontent
}
The update time is based on scheduling interval,
keep an eye on file, when reach ~64K the excedence are deleted (is why is writed “last first”)
if scheduled every 30 min:
~64K are near 2700 record, sufficient for more than 1 month with one value for each half hour on each day
you can copy & paste the content of the txt file inside excel, and you obtain a table with two colums
the date are stored on ISO-like format, can be sorted
27 2022/04/05 15:19:30
42 2022/04/05 18:26:10
Even better !!
31 days x 24 hours x 2 times per hour = 1488 records for a month with 31 days.
Looking at your file output I see 22 characters, take 25 adding newline and 2 spare digits (more then 1000 leases ? No …) ?
That’s 37200 bytes or 36.3K.
So that should give a bit more then 1.5 months given the limit on a txt file is 64K (1.76 to be exact, for a 31-day month).
My count is really approximative…
Yes, are 25 characters, from lease number 0 to 999, obviously 26 from 0 to 9999:
1234567890123456789012345
999t2022/04/05s15:19:30rn
t = tab
s = space
r = carriage return
n = line feed
But can be reduced to 15 if the year and month go on filename, like:
leasecount_2022-04.txt
123456789012345
999t05s15:19:30rn
Unique file created each month:
{
:global arrMonths {jan="01";feb="02";mar="03";apr="04";may="05";jun="06";jul="07";aug="08";sep="09";oct="10";nov="11";dec="12"}
:local dhcpName "dhcp-laboratorio"
# omit .txt !!!
:local filename "leasehistory"
:local ndate [/system clock get date]; :local ntime [/system clock get time]
:local now "$[:pick $ndate 4 6] $[/system clock get time]"
:local filename "$filename_$[:pick $ndate 7 11]-$($arrMonths->[:pick $ndate 0 3]).txt"
/file
:if ([:len [find where name="flash" and type="disk"]] = 1) do={:set filename "flash/$filename"}
:if ([:len [find where name=$filename]] = 0) do={print file="$filename"; :delay 5s; set $filename contents=""}
:local currentLease [:len [/ip dhcp-server lease find where server=$dhcpName]]
:delay 4s
:local filecontent [get $filename contents]
:local newfilecontent "$currentLease\t$now\r\n$filecontent"
set $filename contents=$newfilecontent
}
Really beautiful !
I might want to take up coding again 
16 Bytes x 31d x 24h x 5 times = 59520
last script can be used for DHCP pool up to 9999 IP,
5 time each hour, each 12 min, for each day in the mounth,
and generate single files for each mounth
and if you do not have noticed, on both script (unique file or separate files for each mounth)
you can specify the monitored dhcp-server name
the “ideal” script (just a foreach cycle…) can create separate files for each dhcp server inside, but rely on correct DHCP Server name for not generate invalid filename…
Ok, I do not resist, this is the script.
New file each month, but one file for each DHCP Server
Prerequisites: DHCP Server must have a valid name for be saved as filename!!!
:global arrMonths {jan="01";feb="02";mar="03";apr="04";may="05";jun="06";jul="07";aug="08";sep="09";oct="10";nov="11";dec="12"}
/ip dhcp-server
:foreach dhcpServer in=[find] do={
:local dhcpName [get $dhcpServer name]
:local ndate [/system clock get date]; :local ntime [/system clock get time]
:local now "$[:pick $ndate 4 6] $[/system clock get time]"
:local filename "lease_history_$dhcpName_$[:pick $ndate 7 11]-$($arrMonths->[:pick $ndate 0 3]).txt"
/file
:if ([:len [find where name="flash" and type="disk"]] = 1) do={:set filename "flash/$filename"}
:if ([:len [find where name=$filename]] = 0) do={print file="$filename"; :delay 5s; set $filename contents=""}
:local currentLease [:len [/ip dhcp-server lease find where server=$dhcpName]]
:delay 4s
:local filecontent [get $filename contents]
:local newfilecontent "$currentLease\t$now\r\n$filecontent"
set $filename contents=$newfilecontent
}
Returns a 2 columns file (lease / day time) if copied & pasted inside excel (or similar)
max run time: one time each 12 min, not less
Variant: New file each month, BUT same file for multiple DHCP Servers
Prerequisites: DHCP Server must have a valid name for be saved as filename!!!
:global arrMonths {jan="01";feb="02";mar="03";apr="04";may="05";jun="06";jul="07";aug="08";sep="09";oct="10";nov="11";dec="12"}
/ip dhcp-server
:foreach dhcpServer in=[find] do={
:local dhcpName [get $dhcpServer name]
:local ndate [/system clock get date]; :local ntime [/system clock get time]
:local now "$[:pick $ndate 4 6] $[/system clock get time]"
:local filename "lease_history_$[:pick $ndate 7 11]-$($arrMonths->[:pick $ndate 0 3]).txt"
/file
:if ([:len [find where name="flash" and type="disk"]] = 1) do={:set filename "flash/$filename"}
:if ([:len [find where name=$filename]] = 0) do={print file="$filename"; :delay 5s; set $filename contents=""}
:local currentLease [:len [/ip dhcp-server lease find where server=$dhcpName]]
:delay 4s
:local filecontent [get $filename contents]
:local newfilecontent "$dhcpName\t$currentLease\t$now\r\n$filecontent"
set $filename contents=$newfilecontent
}
Returns a 3 columns file (server / lease / day time) if copied & pasted inside excel (or similar)
max run time: depend how much is long the dhcp server name, approx. 1 time each 30 min can be safe
thanks for the scripts, I am trying them out and they are great. at least I can plot it faster.
Yes, please do. This has nothing to do with High-bandwidth Digital Content Protection HDCP
And so other users in the future wanting to do something similar will have a better chance of finding it, please edit your original post, and change the title to something meaningful like
“How can I graph DHCP leases?”
It is easy to do. Here’s how:
