How do I write a filter for each value of a multivalued field?

[Philip_spl]

I have a table like the following:

col1      col2
value1    a
value2    b
value2    c
value1    d
value3    e
value2    f
value3    g

Now I want to reduce the output to this:

col1      col2
value1    a
value2    b
value3    e

So always the last entry of each value of col1.
Is there a way to do this?

Thanks in advance!

[jchampagne_splu]

How are you defining the order of your rows? By time?
From your example, it looks like you want to see the earliest col2 value of each col1 series. To get that, you'd do something like this:

index="myIndex" sourcetype="myData" | stats earliest(col2) as col2 by col1

If you want to see the last col2 value for each col1 series, you'd do something like this:

index="myIndex" sourcetype="myData" | stats latest(col2) as col2 by col1

[renjith_nair · Accepted solution]

From your example you need first entry of each col not the last entry. If its indexed on different time, then try

<your search> |stats last(col2) by col1

This should pick up the first value seen for the field and first(col2) for last value

first() returns the first seen result -> the most recent reference
last() returns the last seen result - > the oldest reference

http://docs.splunk.com/Documentation/Splunk/6.1/SearchReference/Commonstatsfunctions

[Philip_spl]

Thanks! That was what I was searching for 🙂