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!!!
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) ]
This one is not adding the data from the subquery after the join command
Plz check the field mapping it works for me
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.
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
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"