Use another index as lookup

[arrowecssupport]

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?

[gcusello · Accepted solution]

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]

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.

[gcusello]

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

[arrowecssupport]

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.

[gcusello]

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]

AMAZING THANK YOU SOLVED IT!

[gcusello]

Hi @arrowecssupport,

Good!

Ciao ad Next Time.

Giuseppe

P.S.: Karma Points are appreciated 😉

[gcusello]

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

Ciao.
Giuseppe

[arrowecssupport]

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.