I need to do a search between two Index and extract a value in a new column

[flck]

Hi everyone,
I need to do a search similar to an Excel vlookup.
I have two Indexes, IndexA and IndexB.

The IndexA has a column called "account" along with other columns not important at the moment.
The IndexB has the same column called "account" which corresponds to a numeric ID, and a second column called "AccountName" which corresponds to a string.

I need to add a new column to the IndexA in which the ID of the "account" column is searched and bring the string of the "AccountName" column of the IndexB.

I appreciate any help.

Greetings.

[kamlesh_vaghela · Accepted solution]

@flck

I'll not suggest to use join as it has it's own limitation like degrading performance and limited data from subsearch. I'm suggesting below search for your requirement Instead of that.

You have to just replace your interesting fields with OtherFields1 and so on.. Please try search for same.

(index="IndexA" OR index = "IndexB")
| stats values(AccountName) as AccountName values(OtherFields1) as OtherFields1 values(OtherFields2) as OtherFields2 by account

[flck]

@kamlesh_vaghela

Thank you very much for your help.

It worked perfect, I really appreciate your help.

Thank you my friend.

[richgalloway]

The goal is a bit vague, but perhaps this will help. The stats command can combine the two indexes.

index=indexA OR index=indexB | stats values(*) as * by account
| table account AccountName other columns

[flck]

Hello, thanks for the answer.

The data found within the Indexes are the following:

IndexA:

account detail.check-item-detail.14-Day Average CPU Utilization
0000001 6.50%
0000002 1.80%

IndexB:

AccountName account
Name1 0000001
Name2 0000002

I have tried the solution given by richgalloway and it works partially, it replaces the AccountName and account columns in the IndexA but does not match.

In the following way it worked for me:

index = "IndexA" | table *
| join type = inner account [search index = "IndexB"]
| fields, "account", "AccountName"

I want to do some more tests before being sure that the query is 100% reliable.

Thank you.

[richgalloway]

This method has been successful in the past, but my answer was incomplete. Please try my revised answer.

[mydog8it]

Can you provide a sample set of the data?