Filter DHCP lease history with Extensible attributes [ Edited ]

[#1]

Hello,

A customer asked me today to export dhcp lease history from Turkey only.

Here are the steps to filter DHCP lease history based on Extensible attributes:

1) Export Networks from IPAM

2) Import in Reporting lookup, change permissions and test it:

3) Update the DHCP lease history search

sourcetype=ib:dhcp:lease_history index=ib_dhcp_lease_history dhcpd OR dhcpdv6 r-l-e | eval Network=FP_NW+"/"+FP_CIDR | lookup network-with-ea.csv Network as Network | eval Protocol=if(PROTO=="dhcpdv6","IPV6","IPV4") | noop | eval LEASE_START=strftime(START_EPOCH, "%Y-%m-%d %H:%M:%S") | eval LEASE_END=strftime(END_EPOCH, "%Y-%m-%d %H:%M:%S") | eval dummy_epoch="" | eval __COMMENT="The lease_time.latest and lease_time.latest are the date format in epoch number. For example if timestamp is 01-01-1971 01.01.01, the epoch number is 8 digit number. So taken lease_time length with >=8." | eval min_lengh_epoch=8 | eval earliest=if(len("0") <= min_lengh_epoch, dummy_epoch , "0") | eval latest=if(len("") <= min_lengh_epoch, dummy_epoch , "") | eval earliest=if(len("0") == 0, START_EPOCH,"0") | eval latest=if(len("") == 0, END_EPOCH,"") | where ((earliest <= START_EPOCH) AND (START_EPOCH <= latest)) OR ((earliest <= END_EPOCH) AND (END_EPOCH <= latest)) | eval FINGER_PRINT=if(isnull(OS_NUMBER) OR OS_NUMBER==0,FP,SFP) | msservers MS_SERVER | eval resolved_names_or_ips=coalesce(ms_resolved_names,ms_resolved_ips) | eval resolved_names_or_ips=if(isnull(resolved_names_or_ips),MS_SERVER,resolved_names_or_ips) | noop | noop | eval host = if (isnull(MS_SERVER),host,NULL) | eval MEMBER_IP = if (isnull(MS_SERVER),MEMBER_IP,NULL) | noop | eval DEVICE_CLASS=if(isnull(DEVICE_CLASS), "Modified or Deleted", DEVICE_CLASS) | rename host as "Member", ACTION as "Action", LEASE_IP as "Lease IP", MAC_DUID as "MAC/DUID", MEMBER_IP as "Member IP", OPTION12HOST as "Host Name", LEASE_START as "Lease Start", LEASE_END as "Lease End", FINGER_PRINT as "Fingerprint", MS_SERVER as "Microsoft Server IP", ms_resolved_names as "Microsoft Server" | convert ctime(_time) as Time | table Time, Member, "Member IP", Protocol, Action, "Lease IP", "MAC/DUID", "Host Name", "Lease Start", "Lease End", "Fingerprint", "Microsoft Server", "Microsoft Server IP"

4) And voila:

It is also possible to modify the DHCP lease history dashboard to include drop down menu for country, region and site if you use this data often.

Feel free to share your feedback

[#2]

Hi Jeanselme,

How can we add the comment field in the DHCP lease history report. The comment needs to be the Network scope comment.

Thanks