topic Re: Merge two tables from two different sources in Splunk Search

Merge two tables from two different sources

krrish0930 — Tue, 26 Sep 2017 16:20:01 GMT

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!!!

Re: Merge two tables from two different sources

DalJeanis — Tue, 26 Sep 2017 20:05:18 GMT

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.

Re: Merge two tables from two different sources

krrish0930 — Tue, 26 Sep 2017 20:55:03 GMT

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

Re: Merge two tables from two different sources

Sukisen1981 — Wed, 27 Sep 2017 08:30:45 GMT

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

Re: Merge two tables from two different sources

krrish0930 — Wed, 27 Sep 2017 13:47:42 GMT

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

Re: Merge two tables from two different sources

Sukisen1981 — Wed, 27 Sep 2017 14:02:00 GMT

Plz check the field mapping it works for me

Re: Merge two tables from two different sources

DalJeanis — Wed, 27 Sep 2017 14:53:50 GMT

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"