topic Re: how to generate a third table with "join" command? in Splunk Search

how to generate a third table with "join" command?

will4t — Sat, 29 Mar 2014 05:32:35 GMT

suppose there are indexes A(x,y) and B(a,b,c). Is it possible to generate a new index C (a,b,c,y) based on that the x field in A (x,y) matches b field in B(a,b,c)? Thanks for your help!

Re: how to generate a third table with "join" command?

MuS — Sat, 29 Mar 2014 07:15:56 GMT

hi will4t,

if I get you correct and you want to match two fields from two indexes and display some other fields as result, try something like this:

 index=A OR index=B | where x=b | table a, b, c ,y

cheers, MuS

Re: how to generate a third table with "join" command?

Ayn — Sat, 29 Mar 2014 08:20:51 GMT

But I guess events won't have BOTH x and b. Rather you'll have events with x and y in one index, and other events with a, b and c in another. So "where x=b" will never match.

Re: how to generate a third table with "join" command?

linu1988 — Sat, 29 Mar 2014 08:46:47 GMT

Even if x value matches b then won't exist in the same event or position to match. So the join should go like

index A|join x[|search index B|rename b as x]|table a,b,c,y

index A|rename x as b|join b[|search index B]|table a,b,c,y

Re: how to generate a third table with "join" command?

MuS — Sat, 29 Mar 2014 12:33:09 GMT

Ayn is right, still I would avoid to use join when ever it is possible. Maybe streamstats could be of help here .....

Re: how to generate a third table with "join" command?

marcoscala — Sat, 29 Mar 2014 12:54:52 GMT

Correct answer should be linu1988s.

index=A x=* | rename x as b | join b [ search index=B b=*] | table a, b, c, y

I added x=* and b=* to be sure to extract events with fields x and b with some value.

Marco

Re: how to generate a third table with "join" command?

lcshared — Mon, 31 Mar 2014 05:53:48 GMT

Sorry Marco, but this answer is neither correct. If you add x=* and b=* to your base searches, you imply that a,c and y are always present in either of the x or b events. If they are not, you get wrong or missing results. So linu1988's answer is still the best...

Re: how to generate a third table with "join" command?

marcoscala — Mon, 28 Sep 2020 16:16:38 GMT

Look, this is an example I just did friday for a Customer:
sourcetype="sap_fea" IVN=* id_elab =* | join type=outer id_elab,IVN [search sourcetype=sap_err ] | fillnull value=OK message | table _time, id_elab, IVN, success, message

where I had to merge data from sap_fea sourcetype contaning id_elab, IVN and success fields, with the "sap_err" sourcetype where, if there's and error in sap_fea, in the "message" fields there's the error description. If there's no error, I have no corresponding event in sourcetype "sap_err" and that's why I used outer join and "fillnull".

And it works! 🙂
Marco

Re: how to generate a third table with "join" command?

martin_mueller — Mon, 31 Mar 2014 12:39:15 GMT

Here's a thought, assuming field values for b and x are unique:

index=A OR index=B | rename x as b | stats values(a) as a values(c) as c values(y) as y by b

You'll get a table roughly like this:

b    a    c    y
b1   a1   c1   y1
b2   ...

If you have cases where a value for b/x only exists in one index and you want to get rid of those values you can add a dc(index) to your stats and where out those with less than two distinct indexes.

Re: how to generate a third table with "join" command?

will4t — Mon, 07 Apr 2014 01:53:57 GMT

Sorry for my delaying reply. I had problem with commenting. I the idea from linu. What I try to do is have a log index A and a small csv file B of signature or a mixture of IP address and subnets. When the contents from B appeared in index A. The match and the event was appended into index C dynamically. Maybe join is not a good way to do the job. Should use commends like inputlookup.

Re: how to generate a third table with "join" command?

will4t — Mon, 07 Apr 2014 01:56:35 GMT

This answer can be valid. But it needs to be tested to be sure.

Re: how to generate a third table with "join" command?

will4t — Mon, 07 Apr 2014 02:17:18 GMT

Marco's answer could be right. sorry that I am late in commenting. I am more concern with process time and power.

Re: how to generate a third table with "join" command?

marcoscala — Mon, 07 Apr 2014 08:35:50 GMT

Will4t, From your description, I thing you just need a regular lookup, nit a Join...

What is still not clear to me, is why you need to create a new index C: do you need (and why) to store the result of the lookup somewhere?

Marco

Re: how to generate a third table with "join" command?

will4t — Sat, 12 Apr 2014 09:26:38 GMT

Yes. I noticed that I can avoid using join. Creating a new index just is for sharing the information with the user group.