/ip dhcp-server lease pri det

RouterOS Scripting
1

I have a bunch of MT devices that servce DHCP leases.

I would like to be able to always have an updated list/spreadsheet/database/etc. that associates:

IP ADDRESS
MAC ADDRESS
HOSTNAME
COMMENT
MT-IDENTITY

I use a script now that utilizes:

:local stringexec   "/system iden print; :put \"\\r\\n\"; /ip cloud pri; :put \"\\r\\n\";  /ip dhcp-server lease pri det; :put \"\\r\\n\"; "

It’s great, but it outputs a text file like this:

name: 355hEX



          ddns-enabled: yes
  ddns-update-interval: none
           update-time: yes
        public-address: 69.202.xxx.xxx
              dns-name: aaaaa.sn.mynetname.net
                status: updated
               warning: Router is behind a NAT. Remote connection might not 
                        work.



Flags: X - disabled, R - radius, D - dynamic, B - blocked 
 0   address=192.168.0.176 mac-address=10:3D:0A:5E:4F:C2 address-lists="" 
     server=defconf dhcp-option="" status=bound expires-after=16h48m11s 
     last-seen=7h11m49s active-address=192.168.0.176 
     active-mac-address=10:3D:0A:5E:4F:C2 active-server=defconf 
     host-name="PearceLR" 

 1   address=192.168.0.174 mac-address=C4:3D:1A:D6:81:D9 
     client-id="1:c4:3d:1a:d6:81:d9" address-lists="" server=defconf 
     dhcp-option="" status=bound expires-after=15h9m45s last-seen=8h50m15s 
     active-address=192.168.0.174 active-mac-address=C4:3D:1A:D6:81:D9 
     active-client-id="1:c4:3d:1a:d6:81:d9" active-server=defconf 
     host-name="Beelink-Mini-S" 

 2   address=192.168.0.152 mac-address=94:E7:0B:29:30:E7 
     client-id="1:52:41:53:20:94:e7:b:29:30:e7:0:0:0:0:0:0" address-lists="" 
     server=defconf dhcp-option="" status=waiting last-seen=2w3h41m31s 
     host-name="Laptop-JRS-AN515-55" 

 3   address=192.168.0.151 mac-address=84:F3:EB:A1:39:FC address-lists="" 
     server=defconf dhcp-option="" status=bound expires-after=19h43m38s 
     last-seen=4h16m22s active-address=192.168.0.151 
     active-mac-address=84:F3:EB:A1:39:FC active-server=defconf 
     host-name="tankutility-84f3eba139fc" 

 4   address=192.168.0.193 mac-address=1C:1E:E3:8C:87:71 address-lists="" 
     server=defconf dhcp-option="" status=bound expires-after=23h31m33s 
     last-seen=28m27s active-address=192.168.0.193 
     active-mac-address=1C:1E:E3:8C:87:71 active-server=defconf 
     host-name="355BlueMBR" 

 5   address=192.168.0.125 mac-address=34:F1:50:6F:33:D0 address-lists="" 
     server=defconf dhcp-option="" status=bound expires-after=22h15m11s 
     last-seen=1h44m49s active-address=192.168.0.125 
     active-mac-address=34:F1:50:6F:33:D0 active-server=defconf 
     host-name="355BlueLR" 

 6   address=192.168.0.124 mac-address=E8:DB:84:9D:1C:7B address-lists="" 
     server=defconf dhcp-option="" status=waiting last-seen=7w5d12h14m5s 
     host-name="ESP_9D1C7B" 

 7   address=192.168.0.123 mac-address=98:F4:AB:21:19:F8 
     client-id="1:98:f4:ab:21:19:f8" address-lists="" server=defconf 
     dhcp-option="" status=bound expires-after=12h1m26s last-seen=11h58m34s 
     active-address=192.168.0.123 active-mac-address=98:F4:AB:21:19:F8 
     active-client-id="1:98:f4:ab:21:19:f8" active-server=defconf 
     host-name="Emporia" 

 8   address=192.168.0.122 mac-address=44:61:32:EB:34:04 
     client-id="ff:32:eb:34:4:0:3:0:1:44:61:32:eb:34:4" address-lists="" 
     server=defconf dhcp-option="" status=bound expires-after=16h52m19s 
     last-seen=7h7m41s active-address=192.168.0.122 
     active-mac-address=44:61:32:EB:34:04 
     active-client-id="ff:32:eb:34:4:0:3:0:1:44:61:32:eb:34:4" 
     active-server=defconf host-name="Shop"

What I need is a way to extract this data from the MT devices, FTP to a server, combine about a dozen of these together, and import them into Excel or a db, or some other single system.

The end goal is to be able to access this combined data and find (for example):

  1. All devices with a mac address that starts with 3c, or
  2. All devices with TH in the comment, or
  3. All devices with an IP starting with 192.168.0

Anyone have any suggestions?

Thank you.

2

Maybe my basic/initial approach needs to be changed.

What about using something like Splunk to manage all the DHCP info across all devices? I know if can handle log entries, but can we get DHCP info into Splunk?

I turned on DHCP logging:

/system logging add action=memory disabled=no prefix="" topics=dhcp

Lots of info, but I don’t see 1 piece of data that is important to: Comment

3

I am considering going on fiverr to hire a programmer to write a program to import about 15 of these text files into a database.

I suspect there is a more elegant way to get all the data that just using:

“/ip dhcp-server lease pri det”

and then a python program to parse the oddly formatted text files FTP’d to my PC and import the data.

4

Try to use commands like this

:local SomeVar [/ip/dhcp-server/lease/print terse as-value]

to output each desired parameter to an array. You could also just loop through everything you want and output that to an array too.

I would then combine those arrays into a larger array with appropriate labels for each type of data. You will probably also need the time of the query and a unique identifier somewhere in there so whatever you end up using to process this data can sort the data by device and use the most recent data to a specified time and date.

Then I would use the new :serialize command to convert it to JSON and transport that JSON somehow to a centralized location (you could use a push or pull model depending on the approach). In theory this whole thing could be done via the API or you could POST the data to somewhere.

I imagine that some sort of metrics aggregation system like Prometheus would be the best way to sort this data, but I’m not familiar with them. I’d hope it’s easy to get JSON into them.

I wonder if SNMP be used for this? I’m not familiar with it, but I imagine others on this forum are.