IP Pool Statistics

dssmiktik · 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
}

janisk · Wed Mar 31, 2010 8:40 am

thanks for the script, it might come in handy.

m4rk0 · Sat Jan 07, 2012 1:29 pm

AWESOME! This is what I need long time, and found it now in 2 years old post

!!!
THANK YOU!!

huigezi · Sun Jan 08, 2012 10:20 am

thanks

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.

nileshkahar · 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.

aacable · 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

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

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

nichky · 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

Shakib · 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 · 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

Jotne · 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.

Jotne · 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
}

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?

Jotne · 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.

alcohol · Thu Sep 21, 2023 1:33 am

This script was probably written long before subnet math was directly supported in routeros. If you are running 6 or 7 then this should work.

Replace this:

		# 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)

With this:

:set pooladdresses ($pooladdresses + ([:toip $maxaddress] - [:toip $minaddress]))

I thought this would make the script much faster, but it doesn't seem to change things much. If you have lots of DHCP pools this just takes a long time to run. Another suggestion to speed this up would be to move poolused up and add an if check to see if the pool is empty, then skip it if it is. But it's still slow if you have a bunch of pools.

Here is my reformatted code with localized variables in scopes, and the mentioned if poolused > 0 check.

:local criticalthreshold 85
:local warnthreshold 70

/ip pool {
 
   :put ("IP Pool Statistics")
   :put ("------------------")

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

      :local poolname [get $p name]
      :local pooladdresses 0
      :local poolused [:len [used find pool=[:tostr $poolname]]]
      :local minaddress
      :local maxaddress
      
      :if ($poolused > 0) do={
        :local line ("     " . $poolname)

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

          # Get min and max addresses
          :local 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]
          }

          :set pooladdresses ($pooladdresses + ([:toip $maxaddress] - [:toip $minaddress]))
        
        #   foreach r
        }

        :local 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)
          }
        }
      # end if ($poolused > 0)
      }

    # foreach p
    }
# /ip pool
}

IP Pool Statistics

IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Re: IP Pool Statistics

Who is online