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

[Marinus]

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.

[kristian_kolb]

Aaah ok. didn't read between the lines.

[Marinus]

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

[kristian_kolb]

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

[Marinus]

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.

[kristian_kolb]

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'.

[Marinus]

Or more specifically all users who failed

| where action=fail | table user action data

[kristian_kolb · Accepted solution]

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

[Marinus]

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

[Ayn]

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...