Re: [Netdisco] subnet usage report

[Ingen S. J. v. (ICTS) <j.v...@ut...>]

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