IP Allocation FAILED : Best practice for inserting new IPs into mysql

[Sophie Loewenthal]

Hi Rod,

     Yes this really did help me. Thanks for the idea for checking and 
alerting via cron ( maybe put into Zabbix )

Interestingly this has not run out of space. So I don't know why I see 
messages like "[sqlippool] pool appears to be full"

+-------------------+--------+-----------+
| pool_name         | total  | allocated |
+-------------------+--------+-----------+
| proximus-carli    | 130047 |      2767 |
| proximus-mechelen | 130047 |         1 |
| vodafone          |  32510 |     23192 |
| vodafone-m2m      | 130048 |      NULL |
+-------------------+--------+-----------+


Kind regards,
Sophie

-- 
Sophie Loewenthal
System Engineer ITOPS / Trimble Transport & Logistics
GSM:+32.471.900703



On 06-Nov-15 12:16 PM, Rod Elias wrote:
> Hi Sophie,
>
> Please see the following link: https://gist.github.com/chapeupreto/109abbdd7b20c977dd58
> It has an SQL query that shows the number of total and allocated IP addresses for each pool that exist in radippool table.
>
> With that SQL query in mind, we defined a threshold value considering the total number of IP minus the number of allocated IP. This way, we have a script running in crontab that checks the result of that query and the threshold value and then triggers an e-mail and/or SMS warning about the running out of IP in the pools.
>
> As for your second question, yes you can add more IP addresses into radippool without the need to stop the server.
>
> Hope this helps you!
>
> Best regards,
>
>
> ----- Mensagem original -----
> De: "Sophie Loewenthal" <sophie.loewenthal at trimbletl.com>
> Para: Freeradius-Users at lists.freeradius.org
> Enviadas: Sexta-feira, 6 de Novembro de 2015 8:41:09
> Assunto: IP Allocation FAILED : Best practice for inserting new IPs into mysql
>
> Hi,
>
> Please find  attached a debug of 62 lines.
>
> My ippool seems full:
> [sqlippool] pool appears to be full
> [sqlippool]     expand: IP Allocation FAILED from %{control:Pool-Name}
> (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port}
> user %{User-Name}) -> IP Allocation FAILED from proximus-carli   (did
> proximus.trimbletl.com cli 32770XXXX port 29449 user 45486XXXXX)
>
> How could I check if the ippool had run out of IPs or was close to?
>
> I tried with these but clearly this was not good enough . Is there a
> better method of currently allocated addresses?
> mysql> select count(*) from radippool where  username = '';
> +----------+
> | count(*) |
> +----------+
> |   383727 |
> +----------+
>
> and with,
> mysql> select count(*) from radippool where  expiry_time is NULL ;
> +----------+
> | count(*) |
> +----------+
> |   396466 |
> +----------+
>
> I am surprised this filled up, because we don't have enough devices to
> fill up our IP space.
> What could happen if NAS did not send back accounting stop packets?
>
> Can I add more IP addresses into radippol with FreeRADIUS running, or
> should I shut down FreeRADIUS during this insert?
>
>
> Kind regards,
> Sophie
>
>
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>