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!
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
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.
This answer can be valid. But it needs to be tested to be sure.
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
Marco's answer could be right. sorry that I am late in commenting. I am more concern with process time and power.
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
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...
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
Yes. I noticed that I can avoid using join. Creating a new index just is for sharing the information with the user group.
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
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.
Ayn is right, still I would avoid to use join
when ever it is possible. Maybe streamstats
could be of help here .....
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
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.