topic Re: Use another index as lookup in Splunk Search

Use another index as lookup

arrowecssupport — Wed, 26 Feb 2020 11:32:48 GMT

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?

Re: Use another index as lookup

gcusello — Wed, 26 Feb 2020 11:34:24 GMT

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

Ciao.
Giuseppe

Re: Use another index as lookup

arrowecssupport — Wed, 26 Feb 2020 11:46:31 GMT

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.

Re: Use another index as lookup

gcusello — Wed, 26 Feb 2020 12:04:47 GMT

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

Re: Use another index as lookup

arrowecssupport — Wed, 26 Feb 2020 13:51:08 GMT

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.

Re: Use another index as lookup

gcusello — Wed, 26 Feb 2020 13:55:48 GMT

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

Re: Use another index as lookup

arrowecssupport — Wed, 26 Feb 2020 14:06:11 GMT

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.

Re: Use another index as lookup

gcusello — Wed, 26 Feb 2020 14:15:56 GMT

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

Re: Use another index as lookup

arrowecssupport — Wed, 26 Feb 2020 14:19:30 GMT

AMAZING THANK YOU SOLVED IT!

Re: Use another index as lookup

gcusello — Tue, 28 Jul 2020 10:43:35 GMT

Hi @arrowecssupport,

Good!

Ciao ad Next Time.

Giuseppe

P.S.: Karma Points are appreciated 😉