This NQE uses the L3 interface info and ARP tables to find the subnets in use on your network. It lists the associated vlan, used addresses, devices, and usage statistics.

An optional parameter allows you to search for an IP address, and whether the IP is in use or not, it will display the subnet it belongs to.

import "@fwd/L3/Interface Utilities";

numHostsInSubnetV4(maskLength) =
  if 2 ^ (32 - maskLength) >= 4
  then 2 ^ (32 - maskLength) - 2
  else 2 ^ (32 - maskLength);

numHostsInSubnetV6(maskLength) = 2 ^ (128 - maskLength);

@query
query(IP_Addresses: List<IpAddress>) =
  foreach device in network.devices
  foreach interface in getL3Interfaces(device)
  let addresses = (foreach address in interface.ipv4.addresses
                   select { ip: address.ip, mask: address.prefixLength }) +
                  (foreach address in interface.ipv6.addresses
                   select { ip: address.ip, mask: address.prefixLength })
  let neighbors = (foreach address in interface.ipv4.neighbors
                   select address.ip) +
                  (foreach address in interface.ipv6.neighbors
                   select address.ip)
  foreach address in addresses
  let subnet = ipSubnet(networkAddress(ipSubnet(address.ip, address.mask)), address.mask)
  let ips = (foreach address in addresses
             select address.ip) +
            neighbors
  foreach ip in ips
  group { ip, vlan: interface.vlan, device: device.name } as address
    by subnet as subnets
  where if length(IP_Addresses) > 0
        then max(foreach ip in IP_Addresses
                 select ip in subnets)
        else true
  let vlan = distinct(foreach item in address
                      where isPresent(item.vlan)
                      select toString(item.vlan))
  let ips = distinct(foreach item in address
                     where item.ip in subnets
                     select item.ip)
  let devices = distinct(foreach item in address
                         select item.device)
  let total = if isIPv4(subnets)
              then numHostsInSubnetV4(length(subnets))
              else numHostsInSubnetV6(length(subnets))
  let used = length(ips)
  let unused = total - used
  let percent = 100 * used / total
  select {
    subnet: subnets,
    vlan,
    "Used IPs": ips,
    Devices: devices,
    "Number Used": used,
    "Number Available": unused,
    "% Used": percent
  };