topic Re: Only display earliest hit for each day in Splunk Search

Only display earliest hit for each day

oleg106 — Wed, 05 Mar 2014 15:55:50 GMT

Hi,

We have building access logs in Splunk and I have to generate an attendance report. I can filter based on specific events, like when someone swiped at the turnstile downstairs, but it shows multiple results since people leave/come back to the building several times a day. I need to only show the first result per each day. Example output:

03/05/2014 08:23:16.257432 - Card Access - Access granted - NY - 10th St - Turnstile 1 - Joe Smith

03/04/2014 15:54:53.062885 - Card Access - Access granted - NY - 10th St - Turnstile 2 - Joe Smith

03/04/2014 15:22:12.837323 - Card Access - Access granted - NY - 10th St - Turnstile 1 - Joe Smith

03/04/2014 08:46:14.337548 - Card Access - Access granted - NY - 10th St - Turnstile 3 - Joe Smith

03/03/2014 09:10:01.092098 - Card Access - Access granted - NY - 10th St - Turnstile 1 - Joe Smith

02/28/2014 09:32:04.303305 - Card Access - Access granted - NY - 10th St - Turnstile 2 - Joe Smith

02/27/2014 09:57:45.852013 - Card Access - Access granted - NY - 10th St - Turnstile 1 - Joe Smith
...

I want to filter it out so that it looks like ..

03/05/2014 08:23:16.257432 - Card Access - Access granted - NY - 10th St - Turnstile 1 - Joe Smith

03/04/2014 08:46:14.337548 - Card Access - Access granted - NY - 10th St - Turnstile 3 - Joe Smith

03/03/2014 09:10:01.092098 - Card Access - Access granted - NY - 10th St - Turnstile 1 - Joe Smith

02/28/2014 09:32:04.303305 - Card Access - Access granted - NY - 10th St - Turnstile 2 - Joe Smith

02/27/2014 09:57:45.852013 - Card Access - Access granted - NY - 10th St - Turnstile 1 - Joe Smith
...

Basically only display a single event for each day that appears first.

Re: Only display earliest hit for each day

lguinn2 — Wed, 05 Mar 2014 16:05:53 GMT

No problem.

yoursearchhere
| eval date=strftime(_time,"%x")
| sort _time
| dedup username date

Assuming that these field names work, of course!

Re: Only display earliest hit for each day

somesoni2 — Wed, 05 Mar 2014 16:49:30 GMT

Try this

Your base search | eval Date=strftime(_time, "%x") | stats first(_time) as swipedTime, first(_raw) as FullRecord by Date | eval swiptedTime=strftime(swiptedTime,"%H:%M:%S.%3Q")

Re: Only display earliest hit for each day

oleg106 — Wed, 05 Mar 2014 18:31:27 GMT

Sorry, I forgot to add an important detail. The app spits out all sorts of log types, and since they are all formatted differently, they are not broken up in fields. So I would need to be able to rely just on the timestamp the log came in, the rest can be ignored.

Re: Only display earliest hit for each day

oleg106 — Wed, 05 Mar 2014 18:31:42 GMT

Re: Only display earliest hit for each day

oleg106 — Wed, 05 Mar 2014 18:32:08 GMT

Re: Only display earliest hit for each day

lguinn2 — Wed, 05 Mar 2014 21:01:54 GMT

That's going to be problematic if you want to see the first event per user - we must have some way of identifying the user! If the field isn't currently extracted, that's okay, but there still must be some mechanism that we can describe to Splunk.

The search above requires only a username. If you want just the first event for each day, just leave off the username field, but I don't think that is what you want.

Re: Only display earliest hit for each day

lguinn2 — Wed, 05 Mar 2014 21:03:26 GMT

Or you can substitute _raw for username, which is what @somesoni2 did. The only problem is that dedup-ing _raw can be a performance issue.

Re: Only display earliest hit for each day

lguinn2 — Thu, 06 Mar 2014 16:22:47 GMT

BTW, you can still have fields even when your log contains a variety of different record formats.

Re: Only display earliest hit for each day

oleg106 — Thu, 06 Mar 2014 21:53:54 GMT

This works, thanks!