how to compare and replace fields value on an indexA from a different fields on indexB

[skylabsit]

Hi,
I have an an indexA which have logs which includes data in each raw like:
User=11111,Language=English,Usage=btn_section1_1,Experience=btn_section2_4,Problems=btn_section4_8
Fields are extracted well here.

now I have imported to splunk using DB Connect (inputs) from sql server, a dictionary of key-value of "btns"
and it stored on indexB, with 2 fields for each event, looks like:
Key=btn_section1_1, Value="Often"
Key=btn_section2_4, Value="All OK"
... and so on...

Now I need to achieve in a query to join both indexes 
and then to have a table for each event, like below where I got the value from the IndexB which translate the actual "btn":
User   Language  Usage       Experience
1111  English      "Often"     "All OK"

I appreciate for help

Thanks!

[gcusello · Accepted solution]

Hi @skylabsit ,

why didn't you used a lookup instead of index?

it's easier to manage and quicker!

Anyway, you could use join command or stats command:

using join (I don like join because it's very slow, but probably having few events in IndexB it could be sufficiently performant):

index=indexA
| join Experience [ search index=indexB | rename Key AS Experience Value AS Experience_Value | fields Experience Experience_Value ]
| table User Language Usage Experience_Value
| rename Experience_Value AS Experience

If you want also Problems, you could add another join similar to the first.

Using stats command, you could run something like this:

index=indexA OR index=IndexB
| rename Key AS Experience Value AS Experience_Value
| stats values(Experience_Value) AS Experience_Value BY User Language Usage Experience 
| table User Language Usage Experience_Value
| fields - Experience
| rename Experience_Value AS Experience

Ciao.

Giuseppe

[skylabsit]

Hi @gcusello 
thank you for your replies.
I test your suggestion, and the Join actually made the job. I got the required values .
an repeated the join for 3 more fields, and it works.

[skylabsit]

@gcusello 
I wondered, I have do to same joining..but it with a dbxquery. and I cannot get it working..

I have a big DB table, that cannot imported in splunk, so what I am trying to make a lookup via dbxquery
but I need to pass param of account number into it from up query? is it possible?

| dbxlookup  connection="db-dev" query="SELECT AccountNumber  FROM DB.Customers  WHERE AccountNumber = "$AccountNumber$"