Archive

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

Path Finder

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.

Tags (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

@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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

@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

View solution in original post

0 Karma

Path Finder

@kamlesh_vaghela

Thank you very much for your help.

It worked perfect, I really appreciate your help.

Thank you my friend.

0 Karma

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Path Finder

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.

0 Karma

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Builder

Can you provide a sample set of the data?

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!