Splunk Search

Only display earliest hit for each day

oleg106
Explorer

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.

Tags (2)
0 Karma
1 Solution

lguinn2
Legend

No problem.

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

Assuming that these field names work, of course!

View solution in original post

lguinn2
Legend

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

0 Karma

oleg106
Explorer

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.

0 Karma

oleg106
Explorer

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.

0 Karma

somesoni2
Revered Legend

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

lguinn2
Legend

No problem.

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

Assuming that these field names work, of course!

View solution in original post

oleg106
Explorer

This works, thanks!

0 Karma

lguinn2
Legend

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.

0 Karma

lguinn2
Legend

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.

0 Karma

oleg106
Explorer

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.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!