Splunk Search

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

Explorer

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!

Tags (1)
1 Solution
SplunkTrust

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

SplunkTrust

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.

Explorer

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

Builder

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

Explorer

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

Builder

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

Explorer

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

SplunkTrust

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

Explorer

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

Builder

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

Explorer

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.

SplunkTrust

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

Champion

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`

Legend

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.

Get Updates on the Splunk Community!