Splunk Search

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

will4t
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)
0 Karma
1 Solution

MuS
SplunkTrust
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

View solution in original post

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

will4t
Explorer

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

0 Karma

marcoscala
Builder

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
Explorer

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

0 Karma

marcoscala
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

0 Karma

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

0 Karma

MuS
SplunkTrust
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

will4t
Explorer

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

0 Karma

marcoscala
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

0 Karma

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

linu1988
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

Ayn
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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...