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!

Detecting Remote Code Executions With the Splunk Threat Research Team

REGISTER NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If ...

Observability | Use Synthetic Monitoring for Website Metadata Verification

If you are on Splunk Observability Cloud, you may already have Synthetic Monitoringin your observability ...

More Ways To Control Your Costs With Archived Metrics | Register for Tech Talk

Tuesday, May 14, 2024  |  11AM PT / 2PM ET Register to Attend Join us for this Tech Talk and learn how to ...