#1 Add report on subnet sizes & usage

fixed
2014-01-01
2011-04-07
No

It would be useful to have a report listing each subnet discovered, size of the subnet and the number of distinct IP addresses that have been seen within each subnet the last XX days. This may help when reorganizing IP space, eg finding subnets with less than 50% utilization.

Discussion

  • Jeroen van Ingen

    The following SQL should do it ("last week" data, sorted by largest subnet first, least active addresses next):

    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 (
    select distinct
    net,
    ip
    from
    subnets,
    node_ip
    where
    ip <<= net
    and
    time_last > now() - interval '7 days'
    and
    last_discover > now() - interval '7 days'
    union
    select distinct
    net,
    alias as ip
    from
    subnets s,
    device_ip di
    join device d using (ip)
    where
    alias <<= net
    and
    s.last_discover > now() - interval '7 days'
    and
    d.last_discover > now() - interval '7 days'
    ) as joined
    group by
    net
    order by
    2 desc,
    3 asc
    ;

     
  • Jeroen van Ingen

    formatting broken by SF tracker :(

     
  • Oliver Gorwits

    Oliver Gorwits - 2013-10-06

    Ticket moved from /p/netdisco/feature-requests/109/

    Can't be converted:

    • _milestone:
    • _priority: 5
     
  • Oliver Gorwits

    Oliver Gorwits - 2013-10-07
    • labels: --> Feature, Web
    • Milestone: -->
     
  • Oliver Gorwits

    Oliver Gorwits - 2013-10-07
    • assigned_to: Jeroen van Ingen --> nobody
     
  • Oliver Gorwits

    Oliver Gorwits - 2013-10-09
    • Status: open --> new
     
  • Oliver Gorwits

    Oliver Gorwits - 2014-01-01
    • status: new --> taken
    • assigned_to: Oliver Gorwits
     
  • Oliver Gorwits

    Oliver Gorwits - 2014-01-01
    • status: taken --> fixed