Community discussions

MUM Europe 2020
 
dssmiktik
Forum Veteran
Forum Veteran
Topic Author
Posts: 732
Joined: Fri Aug 17, 2007 8:42 am

IP Pool Statistics

Mon Mar 29, 2010 11:38 pm

I've written a script to collect the used/available addresses for all IP Pools. This could be useful if you have multiple pools, and need to load-balance pool usage. Also, it could be used to detect percent of IP Pool exhaustion and notify via logs, email, or could trigger an action.

It works with a single IP, IP range, or multiple IP ranges set in a pool.
# List stats for IP -> Pool
#
# criticalthreshold = output pool display in red if pool used is above this %
# warnthreshold = output pool display in gold if pool used is above this %

:local criticalthreshold 85
:local warnthreshold 70

# Internal processing below...
# ----------------------------------
/ip pool {
   :local poolname
   :local pooladdresses
   :local poolused
   :local poolpercent
   :local minaddress
   :local maxaddress
   :local findindex
   :local tmpint
   :local maxindex
   :local line

   :put ("IP Pool Statistics")
   :put ("------------------")

# Iterate through IP Pools
   :foreach p in=[find] do={

      :set poolname [get $p name]
      :set pooladdresses 0
      :set poolused 0
      :set line ""

      :set line ("     " . $poolname)

#   Iterate through current pool's IP ranges
      :foreach r in=[:toarray [get $p range]] do={

#      Get min and max addresses
         :set findindex [:find [:tostr $r] "-"]
         :if ([:len $findindex] > 0) do={
            :set minaddress [:pick [:tostr $r] 0 $findindex]
            :set maxaddress [:pick [:tostr $r] ($findindex + 1) [:len [:tostr $r]]]
         } else={
            :set minaddress [:tostr $r]
            :set maxaddress [:tostr $r]
         }

#       Convert to array of octets (replace '.' with ',')
         :for x from=0 to=([:len [:tostr $minaddress]] - 1) do={
            :if ([:pick [:tostr $minaddress] $x ($x + 1)] = ".") do={
               :set minaddress ([:pick [:tostr $minaddress] 0 $x] . "," . \
                                       [:pick [:tostr $minaddress] ($x + 1) [:len [:tostr $minaddress]]]) }
         }
         :for x from=0 to=([:len [:tostr $maxaddress]] - 1) do={
            :if ([:pick [:tostr $maxaddress] $x ($x + 1)] = ".") do={
               :set maxaddress ([:pick [:tostr $maxaddress] 0 $x] . "," . \
                                       [:pick [:tostr $maxaddress] ($x + 1) [:len [:tostr $maxaddress]]]) }
         }

#      Calculate available addresses for current range
         :if ([:len [:toarray $minaddress]] = [:len [:toarray $maxaddress]]) do={
            :set maxindex ([:len [:toarray $minaddress]] - 1)
            :for x from=$maxindex to=0 step=-1 do={
#             Calculate 256^($maxindex - $x)
               :set tmpint 1
               :if (($maxindex - $x) > 0) do={
                  :for y from=1 to=($maxindex - $x) do={ :set tmpint (256 * $tmpint) }
               }
               :set tmpint ($tmpint * ([:tonum [:pick [:toarray $maxaddress] $x]] - \
                                                    [:tonum [:pick [:toarray $minaddress] $x]]) )
               :set pooladdresses ($pooladdresses + $tmpint)
#         for x
            }

#      if len array $minaddress = $maxaddress
         }

#      Add current range to total pool's available addresses
         :set pooladdresses ($pooladdresses + 1)

#   foreach r
      }

#   Now, we have the available address for all ranges in this pool
#   Get the number of used addresses for this pool
      :set poolused [:len [used find pool=[:tostr $poolname]]]
      :set poolpercent (($poolused * 100) / $pooladdresses)

#   Output information
      :set line ([:tostr $line] . "  [" . $poolused . "/" . $pooladdresses . "]")
      :set line ([:tostr $line] . "  " . $poolpercent . " % used")

#   Set colored display for used thresholds
      :if ( [:tonum $poolpercent] > $criticalthreshold ) do={
         :log error ("IP Pool " . $poolname . " is " . $poolpercent . "% full")
         :put ([:terminal style varname] . $line)
      } else={
         :if ( [:tonum $poolpercent] > $warnthreshold ) do={
            :log warning ("IP Pool " . $poolname . " is " . $poolpercent . "% full")
            :put ([:terminal style syntax-meta] . $line)
         } else={
            :put ([:terminal style none] . $line)
         }
      }

# foreach p
   }
# /ip pool
}
 
User avatar
janisk
MikroTik Support
MikroTik Support
Posts: 6283
Joined: Tue Feb 14, 2006 9:46 am
Location: Riga, Latvia

Re: IP Pool Statistics

Wed Mar 31, 2010 8:40 am

thanks for the script, it might come in handy.
 
User avatar
m4rk0
Member Candidate
Member Candidate
Posts: 195
Joined: Sat Feb 16, 2008 8:30 pm
Location: BA

Re: IP Pool Statistics

Sat Jan 07, 2012 1:29 pm

AWESOME! This is what I need long time, and found it now in 2 years old post :o !!!
THANK YOU!! ;)
http://www.mreza-wisp.ba/
Please, give Karma if I fix your problem
Certified MTCNA
 
User avatar
huigezi
newbie
Posts: 43
Joined: Sat Dec 24, 2011 4:39 am
Location: apple

Re: IP Pool Statistics

Sun Jan 08, 2012 10:20 am

thanks

If you want knowledge, you must toil for it
 
User avatar
nest
Forum Veteran
Forum Veteran
Posts: 817
Joined: Tue Feb 27, 2007 1:52 am
Location: UK
Contact:

Re: IP Pool Statistics

Mon Jan 09, 2012 1:30 am

dssmiktik - did you add this to the wiki? :-)

I will find this script very useful once I've added an ability for it to email us. Thanks. We already had one hotspot that suddenly had a lot of visitors arrive and we quickly ran out of IP addresses (normally we might get 50-100 users during the lease time, but we suddenly had nearly 10 times that many, with a /23 we didn't stand a chance of it surviving). If this had alerted us, we could have taken proactive action instead of reacting to phone calls telling us that customers could not get online. :-(

Once again - thanks for this.
Ron Touw - Mikrotik Certified Trainer
LinITX.com - MultiThread Consultants
Get your MikroTik RBs and Training: http://linitx.com/category/166
Largest Official UK MikroTik Distributor
IRC channel: #routerboard on irc.z.je (IPv4), 6.irc.z.je (IPv6)
 
nileshkahar
just joined
Posts: 3
Joined: Tue Jul 24, 2012 7:41 am

Re: IP Pool Statistics

Sat Aug 02, 2014 12:21 pm

Hi nest,

Can you please share the config for emailing the script output? Your help appreciated.

Thanks,
Nil.
 
User avatar
aacable
Member
Member
Posts: 428
Joined: Wed Sep 17, 2008 11:58 am
Location: ISLAMIC Republic of PAKISTAN
Contact:

Re: IP Pool Statistics

Thu Jun 02, 2016 3:02 pm

Sorry to wakeup this very old thread.

Recently an OP required a customized solution to get alerts and record for the Mikrotik DHCP pool usage stats dynamically by Linux base bash scripting. I used this script and tried to took it to the next level. the bash script on Ubuntu , remotely executes this script and fetch the results in file, store the data in mysql tables, and format the output as desired and sends email if the pool have consumed XX %.

I made the solution, its not very elegant and surely its not professional but I learned a lot from it .
This is just my own idea and sharing it , maybe someone will find it useful for some other project. Just my two cents

https://aacable.wordpress.com/2016/06/0 ... ash-mysql/
3- dhcp billing alert full mail.PNG
1-dhcp-alert-on-bash-screen.PNG
You do not have the required permissions to view the files attached to this post.
_____________
Regard's

Syed Jahanzaib
Web: http://aacable.wordpress.com
Email: aacable [at] hotmail.com
 
plisken
Forum Guru
Forum Guru
Posts: 2438
Joined: Sun May 15, 2011 12:24 am
Location: Belgium
Contact:

Re: IP Pool Statistics

Sat Jun 24, 2017 11:40 am

Can you show the code by way of example
192.168.100.1/24 please.
It is somewhat unclear to me.
Thanks
 
plisken
Forum Guru
Forum Guru
Posts: 2438
Joined: Sun May 15, 2011 12:24 am
Location: Belgium
Contact:

Re: IP Pool Statistics

Fri Mar 16, 2018 10:21 pm

I Repeat my quistion can you show the code by way of example 192.168.100.1/24 please.
It is somewhat unclear to me.
Thanks a lot
 
User avatar
nichky
Long time Member
Long time Member
Posts: 546
Joined: Tue Jun 23, 2015 2:35 pm

Re: IP Pool Statistics

Sat Mar 17, 2018 6:21 am

where can i see the result? for e.g. if i do copy/paste on the terminal i can see the result, but if i put on script, where can i see the result,nothing on log

Thanks
Nikola Suminoski
MikroTik Consultan
MTCRE l MTCWE

!) Safe Mode is your friend;
 
Shakib
just joined
Posts: 3
Joined: Tue Dec 18, 2018 5:28 pm

Re: IP Pool Statistics

Wed Dec 19, 2018 11:01 am

Dear guyz,
I need a script for shifting VLANs one port to another while my one link will go down, the VLANs will be shift automatically.
like: if my primay link ether1 goes down the created V LANs of ether1 will be shifted to ether2 .
I wanna do this for auto-redundency link.

Thank you,
Shakib
 
Shakib
just joined
Posts: 3
Joined: Tue Dec 18, 2018 5:28 pm

Re: IP Pool Statistics

Wed Dec 19, 2018 11:05 am

Dear guyz,
I need a script for shifting VLANs one port to another while my one link will go down, the VLANs will be shift automatically.
like: if my primay link ether1 goes down the created V LANs of ether1 will be shifted to ether2 .
I wanna do this for auto-redundency link.

Thank you,
Shakib
 
User avatar
Jotne
Forum Guru
Forum Guru
Posts: 1316
Joined: Sat Dec 24, 2016 11:17 am
Location: jo.overland at gmail.com

Re: IP Pool Statistics

Fri Jun 21, 2019 1:26 am

Love this script, but did find a big error with it.
It does not count DHCP IP that are converted to static IP.
/ip dhcp-server lease print
do show all IP in all pool, static or dynamic
/ip pool used print
that is used in the script only show bound IP. For me that would be a fraction of all IP, and scoop show much less used than its really is.

If I got time I will have a look at it and how to get the correct numbers.
 
How to use Splunk to monitor your MikroTik Router

MikroTik->Splunk
 
 
User avatar
Jotne
Forum Guru
Forum Guru
Posts: 1316
Joined: Sat Dec 24, 2016 11:17 am
Location: jo.overland at gmail.com

Re: IP Pool Statistics

Fri Jun 21, 2019 9:27 pm

Updated script to count leases show in ip dhcp-server lease and not what are used in the pool to reflect all static IP reserved.
# Collect DHCP Pool information
# ----------------------------------
/ip pool {
	:local poolname
	:local pooladdresses
	:local poolused
	:local minaddress
	:local maxaddress
	:local findindex
	:local tmpint
	:local maxindex

	# Iterate through IP Pools
	:foreach pool in=[find] do={
		:set poolname [get $pool name]
		:set pooladdresses 0
		:set poolused 0

		# Iterate through current pool's IP ranges
		:foreach range in=[:toarray [get $pool range]] do={

			# Get min and max addresses
			:set findindex [:find [:tostr $range] "-"]
			:if ([:len $findindex] > 0) do={
				:set minaddress [:pick [:tostr $range] 0 $findindex]
				:set maxaddress [:pick [:tostr $range] ($findindex + 1) [:len [:tostr $range]]]
			} else={
				:set minaddress [:tostr $range]
				:set maxaddress [:tostr $range]
			}

			# Convert to array of octets (replace '.' with ',')
			:for x from=0 to=([:len [:tostr $minaddress]] - 1) do={
				:if ([:pick [:tostr $minaddress] $x ($x + 1)] = ".") do={
					:set minaddress ([:pick [:tostr $minaddress] 0 $x] . "," . \
					[:pick [:tostr $minaddress] ($x + 1) [:len [:tostr $minaddress]]])
				}
			}

			:for x from=0 to=([:len [:tostr $maxaddress]] - 1) do={
				:if ([:pick [:tostr $maxaddress] $x ($x + 1)] = ".") do={
					:set maxaddress ([:pick [:tostr $maxaddress] 0 $x] . "," . \
					[:pick [:tostr $maxaddress] ($x + 1) [:len [:tostr $maxaddress]]])
				}
			}

			# Calculate available addresses for current range
			:if ([:len [:toarray $minaddress]] = [:len [:toarray $maxaddress]]) do={
				:set maxindex ([:len [:toarray $minaddress]] - 1)
				:for x from=$maxindex to=0 step=-1 do={
					# Calculate 256^($maxindex - $x)
					:set tmpint 1
					:if (($maxindex - $x) > 0) do={
						:for y from=1 to=($maxindex - $x) do={ :set tmpint (256 * $tmpint) }
					}
					:set tmpint ($tmpint * ([:tonum [:pick [:toarray $maxaddress] $x]] - \
					[:tonum [:pick [:toarray $minaddress] $x]]) )
					:set pooladdresses ($pooladdresses + $tmpint)
				}
			}

			# Add current range to total pool's available addresses
			:set pooladdresses ($pooladdresses + 1)

		# /foreach range
		}

		# Test if pools is used in DHCP or VPN and show leases used
		:local dname [/ip dhcp-server find where address-pool=$poolname]
		:if ([:len $dname] = 0) do={
			:set poolused [:len [used find pool=[:tostr $poolname]]]
		} else={
			:local dname [/ip dhcp-server get [find where address-pool=$poolname] name]
			:set poolused [:len [/ip dhcp-server lease find where server=$dname]]}

		# Send data
		:log info message=("script=pool pool=$poolname used=$poolused total=$pooladdresses")

	# /foreach pool
	}
# /ip pool
}
 
How to use Splunk to monitor your MikroTik Router

MikroTik->Splunk
 
 
plisken
Forum Guru
Forum Guru
Posts: 2438
Joined: Sun May 15, 2011 12:24 am
Location: Belgium
Contact:

Re: IP Pool Statistics

Fri Jun 21, 2019 10:12 pm

Very useful script. Can you have this dhcp info mailed to you? Can you equip the script with that?
 
User avatar
Jotne
Forum Guru
Forum Guru
Posts: 1316
Joined: Sat Dec 24, 2016 11:17 am
Location: jo.overland at gmail.com

Re: IP Pool Statistics

Fri Jun 21, 2019 10:37 pm

As long as you have setup your email system like this:
https://wiki.mikrotik.com/wiki/Manual:Tools/email
Then change:
# Send data
:log info message=("script=pool pool=$poolname used=$poolused total=$pooladdresses")
to:
# Send data
/tool e-mail send to=your.mail@gmail.com subject="test" body="script=pool pool=$poolname used=$poolused total=$pooladdresses" start-tls=yes
Tested and work like a charm.
But you got one email for every pool. Can be fixed.
And you do not get the nice graphs that Splunk gives you :)
See my signature.

EDIT
To test it in command line (cli)

Change to
# Send data
:put ("script=pool pool=$poolname used=$poolused total=$pooladdresses")
And add [ before first line and ] after last line. Cut and past to cli for test.
 
How to use Splunk to monitor your MikroTik Router

MikroTik->Splunk
 

Who is online

Users browsing this forum: No registered users and 22 guests