Splunk Search

Selfjoin - Keeping All Results

gvmorley
Contributor

This one may be easy, but it's eluding me.

I've got a results table from an .csv file (using | inputlookup) that looks a bit like this:

alt text

What I want to do is combine the results that have the same 'Key', but I want to retain all of the 'Fields', ideally as a multi-value field.

So ending up with:

alt text

If I use a selfjoin I've only got the option to keep one or other of the associated fields (using the overwrite option with either true or false).

The results table that I'm working from is >20,000 lines so I may also need to be conscious of limits, etc.

Any thoughts?

0 Karma
1 Solution

esix_splunk
Splunk Employee
Splunk Employee

You can try something like this...

..| inputlookup mylookup | stats list(field1) list(field2) by key

That should give you the values of all fields, pivoting on the key value.

View solution in original post

gvmorley
Contributor

I also found another solution via someone else's Splunk Answers question.

I was originally trying to simply use 'transaction' on the Key value. I'd discounted this, as it always came back with zero results.

It appears that 'transaction' needs the _time field, which wasn't in my inputlookup table.

By adding a

| eval _time=now()

I could then do:

...| inputlookup mylookup | eval _time=now() | transaction Key

I found this here, so can't take any credit for it:

http://answers.splunk.com/answers/207087/inputlookup-followed-by-transaction-no-results-fou.html

Both solutions work for me, which is great.

0 Karma

esix_splunk
Splunk Employee
Splunk Employee

You can try something like this...

..| inputlookup mylookup | stats list(field1) list(field2) by key

That should give you the values of all fields, pivoting on the key value.

gvmorley
Contributor

Hi.

Many thanks for this. I haven't used the list() function before, so that not only solves my problem, but gives me something new to play with!

Graham.

Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...