topic Re: Join with "eventstats" on a non unique field in Splunk Search

Join with "eventstats" on a non unique field

kcollori — Tue, 29 Sep 2020 16:05:39 GMT

Hello there,

I have 2 indexes [customer_id, datetime] and [customer_id, date_of_creation, motive] with a common field "customer_id". I would like to perform a join of my indexes on this fields knowing that the values in each indexe can be non unique.

As I don't want to use the function Join of Splunk because of its limits, I use Eventstats instead. But the problem is that for the non unique values, I get multivalue fields concerning datetime, date_of_creation and motive.

How could I proceed to get the same result as a join would do (without using Join !) ?

Thanks in advance ! 😄

Re: Join with "eventstats" on a non unique field

richgalloway — Wed, 04 Oct 2017 13:06:49 GMT

Have you tried stats?

index=index1 OR index=index2 customer_id=* | stats values(datetime) as datetime values(date_of_creation) as date_of_creation values(motive) as motive by customer_id | ...

Re: Join with "eventstats" on a non unique field

kcollori — Wed, 04 Oct 2017 13:08:38 GMT

Yes I tried but it still gives me multivalue fields 😕

Re: Join with "eventstats" on a non unique field

DalJeanis — Wed, 04 Oct 2017 13:31:52 GMT

@kcollori - Can you explain how this differs from (or adds requirements to) your use case in this question? https://answers.splunk.com/answers/578302/how-to-join-2-indexes-by-common-field-respective-t.html#answer-579343

When trying to connect something that is non-unique, you have to create uniqueness by a time limit or some other unique characteristic. That generally is going to require streamstats rather than eventstats.

You...

collect all the events that might be relevant,
sort them in order (remember sort 0 so you don't lose any),
copy the information you need from one type of record forward or backward onto the other type of record using streamstats,
get rid of any records that are now redundant
... occasionally eventstats is useful right here to collect remaining information together ...
then calculate and present your information.