Report: IP last active, source other than dhcp

Question

We have several subnets that use static addresses with "reserved" entries in MS DHCP. When IPAM reports the Last Response date on the Manage Subnets and IP Addresses page, it always shows the current date since it saw the IP in DHCP. However, people forget to remove unused address reservations from DHCP. What I need to know is when was the address last *active*, ie seen in a scan or neighbor discovery. How can I get a report where the Last Response does not include DHCP, just scan and neighbor discovery?

thanks

john

dis

martin.kunc · Accepted Answer

Hi John,

it is possible to fetch these data from IPAM historical records. Please put folowing SQL in a new SQL Advanced Report:

select n.IPAddress, g.Address as 'Subnet', isnull(cast(h.Time as varchar),'No response') from IPAM_Node n

left join IPAM_Group g on g.groupid = n.SubnetId

left join IPAM_IPHistory h on n.IPNodeId = h.IPNodeId

and (h.ModifiedBy & 1 = 1 or h.ModifiedBy & 16 = 16) -- Changed by ICMP or ARP scan

and h.IntoValue = 'Used' and h.Time =

(select MAX(Time) from IPAM_IPHistory hh where

hh.ipnodeid = h.IPNodeID and (hh.ModifiedBy & 1 = 1 or hh.ModifiedBy & 16 = 16)

and hh.IntoValue = 'Used')

where n.Status = 4

order by n.IPAddressN

You should then have a new IPAM report, which you can then show as a resource for example on IPAM Summary page.

Last Reponse from Reserved Nodes.png

Report: IP last active, source other than dhcp

Top Replies