Reply
Accepted Solution

DHCP Top Lease Clients

GAgyapong
Techie
Posts: 11
11057     0

Hi,

 

I have no Splunk training, and I am in need of a custom report. The Infoblox Reporting Server comes with a canned report to get the "DHCP Top Lease Clients" by MAC address.  I would like to be able to get this same report by IP address instead.  Any help will be appreciated.

Re: DHCP Top Lease Clients

[ Edited ]
Adviser
Posts: 118
11058     0

Hello,

 

Assuming you are using the reporting in version 7.3 or later, the following search should generate what your are looking for.

  

 

sourcetype=ib:dhcp:lease_history index=ib_dhcp_lease_history             (host="*")             *             *             *             *             *             *             dhcpd OR dhcpdv6 r-l-e      
| where (ACTION="Issued" or ACTION="Renewed" or ACTION="Freed")                
| stats count(eval(if(ACTION="Issued",COUNT,0))) as Issued,
  count(eval(if(ACTION="Renewed",COUNT,0))) as Renewed,
  count(eval(if(ACTION="Freed",COUNT,0))) as Freed by LEASE_IP 
|rename LEASE_IP as IP

 

Untitled.tiff

Re: DHCP Top Lease Clients

GAgyapong
Techie
Posts: 11
11058     0

Thanks for your response RBarlow!

 

Unfortunately, I am not able to test this search string, as our evaluation reporting server have exceeded the license limit.  

 

We are working on purchasing the reporting server soon, so I'll test the search and respond back on here.

 

Thanks again!

Re: DHCP Top Lease Clients

CDecker_1
Techie
Posts: 3
11058     0

How do I write a report to exclude certain computer names (ie begin with DC*) and only find leases given to non-standard company computer names?

Re: DHCP Top Lease Clients

Adviser
Posts: 118
11058     0

You can just use the standard DHCP lease history report enter DC* in the hostname field. This will show only the hostnames starting with "DC". Hover over the resulting table, then click on the search glass in the lower left to open in search.

 

Then change the part that says (OPTION12HOST="DC*") to (OPTION12HOST!="DC*")  


@CDecker_1 wrote:

How do I write a report to exclude certain computer names (ie begin with DC*) and only find leases given to non-standard company computer names?


 

Re: DHCP Top Lease Clients

CDecker_1
Techie
Posts: 3
11058     0

I do not see hostname other than OPTION12HOST as "Host Name" and if i change "Host Name" it just changes the header of the column? 

 

sourcetype=ib:dhcp:lease_history index=ib_dhcp_lease_history dhcpd OR dhcpdv6 r-l-e | eval Protocol=if(PROTO=="dhcpdv6","IPV6","IPV4") | eval LEASE_START=strftime(START_EPOCH, "%Y-%m-%d %H:%M:%S") | eval LEASE_END=strftime(END_EPOCH, "%Y-%m-%d %H:%M:%S") | lookup os_number_fingerprint_lookup OS_NUMBER output SFP | eval FINGER_PRINT=if(isnull(OS_NUMBER) OR OS_NUMBER==0,FP,SFP) | lookup nios_member_ip_lookup host output MEMBER_IP | lookup fingerprint_device_class_lookup FINGER_PRINT output DEVICE_CLASS | 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" | convert ctime(_time) as Time | table Time, Member, "Member IP", Protocol, Action, "Lease IP", "MAC/DUID", "Host Name", "Lease Start", "Lease End", "Fingerprint"

Re: DHCP Top Lease Clients

[ Edited ]
Adviser
Posts: 118
11058     0

I think you may be starting with the report and you need to start with the dashboard. You should end up with something like the below. Then you would modify the bold section as described in my previous post to exclude matching hosts instead of only showing matches.

 

sourcetype=ib:dhcp:lease_history index=ib_dhcp_lease_history             (host="*")             *             *             *             (OPTION12HOST="DC*")             *             *             dhcpd OR dhcpdv6 r-l-e             | eval Protocol=if(PROTO=="dhcpdv6","IPV6","IPV4")             | noop             | eval LEASE_START=strftime(START_EPOCH, "%Y-%m-%d %H:%M:%S")             | noop             | eval LEASE_END=strftime(END_EPOCH, "%Y-%m-%d %H:%M:%S")             | noop             | eval FINGER_PRINT=if(isnull(OS_NUMBER) OR OS_NUMBER==0,FP,SFP)             | 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" | convert ctime(_time) as Time | table Time, Member, "Member IP", Protocol, Action, "Lease IP", "MAC/DUID", "Host Name", "Lease Start", "Lease End", "Fingerprint"

Re: DHCP Top Lease Clients

GAgyapong
Techie
Posts: 11
11058     0

I am now able to comfirm that, the script worked as expected.  Thanks for the response!


@GAgyapong wrote:

Thanks for your response RBarlow!

 

Unfortunately, I am not able to test this search string, as our evaluation reporting server have exceeded the license limit.  

 

We are working on purchasing the reporting server soon, so I'll test the search and respond back on here.

 

Thanks again!


 

Showing results for 
Search instead for 
Do you mean 

Recommended for You