Splunk Search

Use another index as lookup

arrowecssupport
Communicator

So I'm trying to enrich one search, by pulling fields from another index, they have a matching pair of fields Serialnumber & SERIALNUM.

How would I do this?

Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @Hi @arrowecssupport,
I see that you have the same fields in both the indexes, why do you want to correlate the two indexes?
if you want to take fields from both the indexes you can use the following two approaches.
If you want instead to filter the first index with the results of the second, see the last search.

you can use the join command that works as a database join:

index = email SERIALNUM Subject 
| join SERIALNUM  [ search index=database | rename Serialnumber AS SERIALNUM ]
| table SERIALNUM Subject location ipaddress racknumber 

but it's a very slow command and there's the limit of 50,000 results in the subsearch.

You can also use a different approach:

(index = email SERIALNUM Subject) OR (index=database)
| eval SERIALNUM=coalesce(Serialnumber, SERIALNUM)
| stats values(Subject) AS Subject values(location) AS location values(ipaddress) AS ipaddress values(racknumber) AS racknumber BY SERIALNUM

If at least you want to filter the first index by the second, you can use something like this:

index = email SERIALNUM Subject [ search index=database | rename Serialnumber AS SERIALNUM | fields SERIALNUM ]
| table SERIALNUM Subject location ipaddress racknumber 

In all the searches it isn't clear what's the condition for SERIALNUM Subject that you have in the main search.

Ciao.
Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @Hi @arrowecssupport,
I see that you have the same fields in both the indexes, why do you want to correlate the two indexes?
if you want to take fields from both the indexes you can use the following two approaches.
If you want instead to filter the first index with the results of the second, see the last search.

you can use the join command that works as a database join:

index = email SERIALNUM Subject 
| join SERIALNUM  [ search index=database | rename Serialnumber AS SERIALNUM ]
| table SERIALNUM Subject location ipaddress racknumber 

but it's a very slow command and there's the limit of 50,000 results in the subsearch.

You can also use a different approach:

(index = email SERIALNUM Subject) OR (index=database)
| eval SERIALNUM=coalesce(Serialnumber, SERIALNUM)
| stats values(Subject) AS Subject values(location) AS location values(ipaddress) AS ipaddress values(racknumber) AS racknumber BY SERIALNUM

If at least you want to filter the first index by the second, you can use something like this:

index = email SERIALNUM Subject [ search index=database | rename Serialnumber AS SERIALNUM | fields SERIALNUM ]
| table SERIALNUM Subject location ipaddress racknumber 

In all the searches it isn't clear what's the condition for SERIALNUM Subject that you have in the main search.

Ciao.
Giuseppe

arrowecssupport
Communicator

Only downside with option 1 is that when Serialnumber doesn't exist it doesn't return records.
So i onlt get records when a match is found.

Sometimes there wont be a match.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @arrowecssupport,
sorry but I don't understand what you want to extract:
do you want the events where there's a match between the two indexes or when there isn't any match?
or do you want a label to know if there's a match or not?

Ciao.
Giuseppe

0 Karma

arrowecssupport
Communicator

When there is a match I want the values to be joined to the event.
But when a match isn't found i still want the original event to be found.

At the moment there are 7 events but only 3 matches and it's not returning the other 4.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @arrowecssupport,
try this:

    index = email SERIALNUM Subject 
   | join SERIALNUM  type=left [ search index=database | rename Serialnumber AS SERIALNUM ]
   | table SERIALNUM Subject location ipaddress racknumber 

Ciao.
Giuseppe

arrowecssupport
Communicator

AMAZING THANK YOU SOLVED IT!

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @arrowecssupport,

Good!

Ciao ad Next Time.

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @arrowecssupport,
could you share an example of events to correlate?

Ciao.
Giuseppe

0 Karma

arrowecssupport
Communicator

index = email SERIALNUM Subject | table SERIALNUM Subject location ipaddress racknumber

With a normal lookup, SERIALNUM would be used to match the field Serialnumber to a CSV file and "Lookup output fields" would be defined as location ipaddress racknumber

I have another index called "database" with the fields Serialnumber, location, ipaddress, racknumber

So i want to do the match from the first index email against the database index.

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