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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...