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

[will4t]

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!

[martin_mueller]

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.

[will4t]

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

[marcoscala]

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

[will4t]

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

[marcoscala]

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

[lcshared]

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

[MuS · Accepted solution]

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

[will4t]

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

[marcoscala]

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

[will4t]

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.

[MuS]

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

[linu1988]

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

or

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

[Ayn]

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.