Only display earliest hit for each day

[oleg106]

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.

[lguinn2]

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

[oleg106]

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.

[oleg106]

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.

[somesoni2]

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")

[lguinn2 · Accepted solution]

No problem.

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

Assuming that these field names work, of course!

[oleg106]

This works, thanks!

[lguinn2]

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.

[lguinn2]

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.

[oleg106]

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.