Splunk Search

Merge two tables from two different sources

krrish0930
New Member

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

0 Karma

Sukisen1981
Champion

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) ]

0 Karma

krrish0930
New Member

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

0 Karma

Sukisen1981
Champion

Plz check the field mapping it works for me

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

krrish0930
New Member

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...