Getting Data In

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

Philip_spl
Engager

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!

0 Karma
1 Solution

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

jchampagne_splu
Splunk Employee
Splunk Employee

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

renjith_nair
Legend

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

---
What goes around comes around. If it helps, hit it with Karma 🙂

Philip_spl
Engager

Thanks! That was what I was searching for 🙂

0 Karma
Get Updates on the Splunk Community!

September Community Champions: A Shoutout to Our Contributors!

As we close the books on another fantastic month, we want to take a moment to celebrate the people who are the ...

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...

What’s New in Splunk Observability – September 2025

What's NewWe are excited to announce the latest enhancements to Splunk Observability, designed to help ITOps ...