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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...