Merge two tables from two different sources

[krrish0930]

i have a requirement to merge two tables

**table 1**
appname      |  source 
app1         |  src1 
app2         |  src 2 
app3         |  src 3

**table 2**
appname    | userinfo
app1       | usr1
app3       | usr 3

merge two tables depending on the appname and the result should be like

appname | source | userinfo
app1 | src1 | usr1
app2 | src2 |
app3 | src3 | usr3
I have tried something like this

index=appdata | spath path=result{} output=x|mvexpand x | stats latest(src) by appname
| join type=left appname [| search index=usrdata | spath path=result{} output=x | mvexpand x | table appname userinfo]

this query is populating data from only the first search before the join command.

Any help is much appreciated. Thanks!!!

[Sukisen1981]

Hi - I did with 2 CSV indexes
mapping - app=appname , source=sc , userinfo=us

index="app1"
| stats values(app),values(sc) by app
| mvexpand values(app)
| mvexpand values(sc)
| join type=left app [search index="app2"
|stats values(app),values(us) by app
| mvexpand values(app)
| mvexpand values(us) ]

[krrish0930]

This one is not adding the data from the subquery after the join command

[Sukisen1981]

Plz check the field mapping it works for me

[DalJeanis]

The pipe before search in your subsearch is probably one error. There may be others.

I'd use this sort of format to do that (assuming there aren't more than about 10K relevant events in usrdata) ...

index=appdata 
| spath path=result{} output=x
| mvexpand x 
| stats latest(src) as src by appname
| append [search index=usrdata 
     | spath path=result{} output=y 
     | mvexpand y 
     | table appname userinfo]
| stats values(src) as src values(userinfo) as userinfo by appname

The other thing I'd check is whether src needs to be x.src and appname and userinfo need to be y.appname and y.userinfo. run each part of the seaerch independently with |head 5 to get ssample output to verify that you are getting good results.

The mvexpand verb is assuming that there will be a multivalue field called x (or y in the subsearch). If x (or y) is not an mv field, then it won't hurt, but it is redundant.

[krrish0930]

Thanks for the help DalJeanis. It helped big time but the only thing is when i have tried searching using the query which you gave. It displayed duplicate values like below.

Appname | source | userinfo
app1 | src 1 |
app1 | | usr1
app2 | src 2 |
app3 |src3 |
app3 | | usr3

can you suggest me a way to remove duplicates and give everything in a single line like
app1 | src1 | usr1

[DalJeanis]

Hmmm. That can't happen from that search language, because the stats command will roll them together.

Please verify that you are running with the stats command.

If this is happening with the stats command, that would indicate that there are trailing spaces or other unprintable characters at the end of the appname field in the usrdata index. If so, then add this immediately before the stats command:

| rex mode=sed field=appname "s/^(\S+)\s+$/\1/g"