Community discussions

MikroTik App
 
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: 6263
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: 196
Joined: Sat Feb 16, 2008 8:30 pm
Location: BA
Contact:

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!! ;)
 
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
 
User avatar
nest
Forum Veteran
Forum Veteran
Posts: 822
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.
 
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: 435
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.
 
plisken
Forum Guru
Forum Guru
Posts: 2509
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: 2509
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
Forum Guru
Forum Guru
Posts: 1275
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
 
Shakib
just joined
Posts: 4
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: 4
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: 3279
Joined: Sat Dec 24, 2016 11:17 am
Location: Magrathean

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.
 
User avatar
Jotne
Forum Guru
Forum Guru
Posts: 3279
Joined: Sat Dec 24, 2016 11:17 am
Location: Magrathean

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
}
 
plisken
Forum Guru
Forum Guru
Posts: 2509
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: 3279
Joined: Sat Dec 24, 2016 11:17 am
Location: Magrathean

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.
 
alcohol
just joined
Posts: 11
Joined: Thu Oct 31, 2013 8:23 pm

Re: IP Pool Statistics

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
}

Who is online

Users browsing this forum: No registered users and 20 guests