Re: [Netdisco] subnet usage report

[Ivan B. <iva...@gm...>]

works great!

Thank you

I will work it out.



Ivan

On Thu, Apr 7, 2011 at 2:58 PM, Ingen Schenau, Jeroen van (ICTS)
<j.v...@ut...> wrote:
> Hi Ivan, all,
>
> On Thu, 2011-04-07 at 14:24 +0200, Ivan Brunello wrote:
>> You pointed out a good possible flaw in my assumptions.
>>
>> I would not bother to an high level of detail.
>> AFAIK, DHCP allocation (on NOT expired leases) on our DHCP servers is
>> done one a "lowest free ip address first".
>> Not checked documentation (they are mostly Microsoft),  but this is
>> what seems to happen.
>
> OK. I gave it a try with our data and the numbers match fairly well with
> what I'd expect (including subnets for wireless).
>
>> This would give me a quite good (good enough for me) snapshot of used address.
>> And our wifi relies heavily on DHCP and REALLY SHORT LEASES :-)
>>
>> I would check a 7 days arp count: long enough to keep track to weekly
>> peaks, and short enough to not contain too many allocated but not
>> currently used addresses.
>>
>>
>> My need is to check e.g. whether a /24 always contains no more than 40
>> address, and this would lead to a leaner /26.
>> We're consolidating our ip addressing to avoid overlap w/ other
>> departments, and we need to make a lot of capacity planning.
>
> Well, let's try it in SQL first before implementing this in a report.
> Data is sorted by subnet size first, then by percentage used (so you can
> easily see where you can reclaim the most space).
>
> select
>  net as "Subnet",
>  power(2, (32-masklen(net))) as "Subnet size",
>  count(distinct ip) as "Count active last week",
>  round(100 * count(distinct ip) / power(2, (32-masklen(net)))) as "Pct
> used"
> from
>  subnets,
>  node_ip
> where
>  ip <<= net
>  and
>  active
>  and
>  time_last > now() - interval '7 days'
>  and
>  last_discover > now() - interval '7 days'
> group by
>  net
> order by
>  2 desc,
>  4,
>  net;
>
>
> Let me know how this works out for you; if this is what you're looking
> for, I think I can implement it in a report in the near future.
>
>
> Regards,
>
> Jeroen van Ingen
> ICT Service Centre
> University of Twente, P.O.Box 217, 7500 AE Enschede, The Netherlands
>
>
>