topic Join between 2 source type with a lot of data in Splunk Search

Join between 2 source type with a lot of data

jbechchar — Wed, 04 Nov 2015 11:29:19 GMT

I everybody.

I have a problem on splunk.

I have a sourcetype with my orders and a sourcetype with my customers.

I have a customer technical key in my customers table and in my orders table.

It is possible to simulate left join ? I have a lot of customers (more than 10 millions...) ... so it is not possible to use the join command.

Thanks in advance for your answers.

Re: Join between 2 source type with a lot of data

ltrand — Wed, 04 Nov 2015 19:37:52 GMT

I would look at populating a lookup or kvstore with your customers and from that you can either do an automatic lookup, or use the data manually. Then you'll want to construct a search to regularly keep the lookup up-to-date. You could also potentially use the KVStore for this as well.

http://docs.splunk.com/Documentation/Splunk/6.0/Knowledge/Usefieldlookupstoaddinformationtoyourevents
http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/Lookup
http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/Outputlookup
http://docs.splunk.com/Documentation/Splunk/6.3.0/Knowledge/ConfigureKVstorelookups

https://answers.splunk.com/answers/86564/updating-lookup-table-data-externally-auto-magically.html
https://answers.splunk.com/answers/236903/how-to-update-a-lookup-table-using-a-scheduled-sea.html

Re: Join between 2 source type with a lot of data

MuS — Wed, 04 Nov 2015 20:03:00 GMT

KV store is a good approach, but if you cannot do it there is always stats.
If your orders and costumers source types have a common field like id you can to something like this:

sourcetype=orders OR sourcetype=costumers | stats values(orders) AS orders values(costumers) AS costumers by id

This is un-test since I don't have your data available, but you can read more about this topic here: https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html

cheers, MuS

Re: Join between 2 source type with a lot of data

curryRick — Wed, 04 Nov 2015 20:05:05 GMT

I concur with creating a lookup table from your customer data using a regularly scheduled search to keep the table current. Then configure the table for automatic lookup and your customer info will be added to each order event as it is processed.

Re: Join between 2 source type with a lot of data

jbechchar — Fri, 06 Nov 2015 11:50:36 GMT

Thanks all for your answers.

It is possible to put a value in the _key field ? for Example my technical key...

I cannot see an exemple anywhere.

Thanks in advance.