create or replace view non_dhcp_hosts as
  (select hwaddr, ipaddress, hostname from arp_dat
   where last_seen > &2 and not exists
     (select * from dhcp_leases
       where arp_dat.hwaddr    = dhcp_leases.hwaddr
         and arp_dat.ipaddress = dhcp_leases.ipaddress));

create or replace view unregistered_hosts as
  (select hwaddr, ipaddress, hostname from non_dhcp_hosts nd
   where not exists
     (select * from hostdb.machtab m, hostdb.hoststab h
       where m.assetno = h.assetno and h.protocol in ('IP', 'OLD', 'NA')
         and nd.hwaddr = m.hwaddr  and nd.ipaddress = h.ipaddress));

create or replace view ipaddr_age as 
  (select ipaddress, max(last_seen) as last_seen,
     floor((&1-max(last_seen))/86400) as age from
    (select ipaddress, last_seen from arp_dat
      union
      select ipaddress, last_seen as last_seen from dhcp_leases
      union
      select ipaddress, last_seen from nmap_dat
      union
      select ipaddress, last_seen from lastseen
    )
    group by ipaddress);

exit
