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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...