How to dedup a field for each day over 30 days?

[pboynton63]

I have this search that I run looking back at the last 30 days

index = ib_dhcp_lease_history dhcpd OR dhcpdv6 r - l - e ACTION = Issued LEASE_IP = 10.* jdoe*

Which tells me how many times jdoe got an IP address from my DHCP server. In this case, the DHCP server is an Infoblox box. The results are fine, except some days jdoe gets the same IP issued several times. The goal is to see just one event per day and what IP jdoe got.

I tried dedup LEASE_IP, but that doesn't give me the real picture because there are days when he gets the same IP and it is as though he didn't get one from the results.

I have also tried | sort date_mday and then the dedup and the same result.

Then I tried to do | bucket span and that was no help either.

I also tried | sort +_time | dedup LEASE_IP

But that only gives me the latest results and leaves out days when he got an IP, but it was the same as other days.

This is my first post and I really did look around first, but I am pretty green with SPLUNK.

All help is much appreciated.

P.

[mtranchita · Accepted solution]

I don't see your image but you could try throwing a dedup before your stats. Something like:

index = ib_dhcp_lease_history dhcpd OR dhcpdv6 r - l - e ACTION = Issued LEASE_IP = 10. jdoe | dedup LEASE_IP date_mday |convert ctime(_time) as Time | stats dc(LEASE_IP) by Time, LEASE_IP, OPTION12HOST, date_mday | table Time, LEASE_IP, OPTION12HOST |rename LEASE_IP as "Leased IP", OPTION12HOST as "Host Name"

that will return the last instance so maybe something like:

index = ib_dhcp_lease_history dhcpd OR dhcpdv6 r - l - e ACTION = Issued LEASE_IP = 10. jdoe | reverse | dedup LEASE_IP date_mday   | table Time, LEASE_IP, OPTION12HOST |rename LEASE_IP as "Leased IP", OPTION12HOST as "Host Name"

probably a better way to do it, but hope that helps

[pboynton63]

This works!!

index = ib_dhcp_lease_history dhcpd OR dhcpdv6 r - l - e ACTION = Issued LEASE_IP = 10.* jdoe* | dedup LEASE_IP date_mday |convert ctime(_time) as Time | stats dc(LEASE_IP) by Time, LEASE_IP, OPTION12HOST, date_mday | table Time, LEASE_IP, OPTION12HOST |rename LEASE_IP as "Leased IP", OPTION12HOST as "Host Name"

I tried several versions of this, but this is the right one. What I learned is order here was important.

Mnay Many thanks!!

[ppablo]

Hi @pboynton63

Glad you found a solution through @mtranchita. Please don't forget to resolve the post by clicking "Accept" directly below their answer. Also, be sure to upvote the answer and any comments by mtranchita that you found helpful.

Cheers

Patrick

[pboynton63]

I don't see an accept button below mtranchita's answer. Only one below renjith.nair's answer which while very nice was not the one I was looking for.

Thanks,

P.

[ppablo]

Ah apologies! I didn't realize the there were two users trying to help you out in the thread. I see it ended up working out 🙂 Thanks!

[renjith_nair]

Try this,

your base search | timechart span=1d count by LEASE_IP

or

|bucket span=1d _time|chart count over LEASE_IP by _time

Please refer to http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/CommonStatsFunctions and adjust the searches according to your requirement

[pboynton63]

Thank you Renjith,

I tried some of your ideas but I was unable to get the results I am looking for. I had a look at the documentation you said could help and it did have one function that seems to give me what I want, but not in the format I require. I used | stats dc(date_mday) and that gave me a number that was right on. So in my example, over a 30 day period I can see JDoe was issued at least 24 IPs. One for every day he came to the office. Before I was seeing 60 IPs. So, this is an improvement. I apologize for not mentioning the fact that I need this in a table format.

Unfortunately, I need the output to include the date, hostname and leased_ip in a table. Stats dc(date_mday) does not do that.

Here is the whole string so you can see it how it is in SPLUNK:

index = ib_dhcp_lease_history dhcpd OR dhcpdv6 r - l - e ACTION = Issued LEASE_IP = 10.* jdoe* |rename LEASE_IP as "Leased IP", OPTION12HOST as "Host Name", |convert ctime(_time) as Time |table Time, "Leased IP", "Host Name"

I will keep trying and get back soon.

[mtranchita]

if you try a distinct count of the IP by the other fields I think that you will get what you are looking for. That should also show if someone gets a new IP during the day.

index = ib_dhcp_lease_history dhcpd OR dhcpdv6 r - l - e ACTION = Issued LEASE_IP = 10.* jdoe* |convert ctime(_time) as Time | stats dc(LEASE_IP) by Time, LEASE_IP, OPTION12HOST, date_mday | table Time, LEASE_IP, OPTION12HOST |rename LEASE_IP as "Leased IP", OPTION12HOST as "Host Name"

[pboynton63]

OK I tried what you sent me, and here are the results:

link text

As you can see I am still seeing duplicates for the 21st which ideally should go away