topic Re: What are possible search strategies to find most recent values of one or more fields? in Splunk Search

What are possible search strategies to find most recent values of one or more fields?

Marinus — Tue, 16 Oct 2012 12:56:34 GMT

Hi All

I'm looking at the possible approaches to obtain events that contain the most recent values for one or more fields.

consider the following events

user=a, action='pass', data=a
user=b, action='fail', data=b
user=c, action='pass', data=c
user=c, action='fail', data=d

I'd like to filter on the most recent value of let's say action.
Ideally you'd like to keep the result as events so that you can
report on additional fields like data.

Re: What are possible search strategies to find most recent values of one or more fields?

Ayn — Tue, 16 Oct 2012 13:01:04 GMT

Could you elaborate more on what filtering behaviour you want? Is the "last" action simply the single last event, or do you mean that this should be split by user, by data, by action type etc etc...

Re: What are possible search strategies to find most recent values of one or more fields?

Marinus — Tue, 16 Oct 2012 13:07:01 GMT

Ideally you'd like to end up with the last events.
So I'd expect to see.

user=a, action='pass', data=a
user=b, action='fail', data=b
user=c, action='pass', data=c

you can then report with for example a table

| table user action data

Re: What are possible search strategies to find most recent values of one or more fields?

kristian_kolb — Tue, 16 Oct 2012 13:12:30 GMT

Have you looked at dedup?

Considering your example above, you could try

your_search | dedup action

This will ultimately come up with two events - number 1 and 2 - since those are the events with the most recent unique values for 'action'.

your_search | dedup data

will return the first three events, since they have unique values for 'data'

your_search | dedup action user

will return all events since they each have a unique combination of 'user' and 'action'

Hope this helps,

Kristian

Re: What are possible search strategies to find most recent values of one or more fields?

Marinus — Tue, 16 Oct 2012 13:13:14 GMT

Or more specifically all users who failed

| where action=fail | table user action data

Re: What are possible search strategies to find most recent values of one or more fields?

kristian_kolb — Tue, 16 Oct 2012 14:27:31 GMT

Put the action=fail before the first pipe.

sourcetype=xxx action=fail | dedup user | table user data

would give you:
user data b b c d

No need bring in the action, since we know it's 'fail'.

Re: What are possible search strategies to find most recent values of one or more fields?

Marinus — Tue, 16 Oct 2012 14:31:46 GMT

The dedup strategy is a simple one. Another one is where you use stats i.e. | stats latest(action) by user. The only problem is that you don't end up with a subset and additional useful fields.

Re: What are possible search strategies to find most recent values of one or more fields?

kristian_kolb — Tue, 16 Oct 2012 14:34:38 GMT

With stats you don't, with dedup you do.

Re: What are possible search strategies to find most recent values of one or more fields?

Marinus — Tue, 16 Oct 2012 14:44:11 GMT

Adding a action=fail into the search could result in a case where a user now passes and no longer fails being missed.

Re: What are possible search strategies to find most recent values of one or more fields?

kristian_kolb — Tue, 16 Oct 2012 14:45:37 GMT

Aaah ok. didn't read between the lines.