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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...