topic How to join large tables with more than 50,000 rows in Splunk? in Splunk Search

How to join large tables with more than 50,000 rows in Splunk?

0range — Tue, 22 Jul 2014 08:04:19 GMT

How do you join large tables?

It is impossible to join tables with more than 50k rows in splunk, so I'm using some tricks, and these tricks are extremely annoying.

Is there any "normal way"?

So, to summarize:
there is no "beautiful" way to join in splunk. We need to use stats, eventstats and brainfuck.

Re: How to join large tables with more than 50,000 rows in Splunk?

martin_mueller — Tue, 22 Jul 2014 08:26:04 GMT

The Splunk way to join data is not to use join. What's your use case?

Re: How to join large tables with more than 50,000 rows in Splunk?

0range — Wed, 23 Jul 2014 07:43:43 GMT

And what to use instead of join?

My case is joining some data 🙂 from different applications and hence different sources/sourcetypes/indexes.
Several million of rows, at least.

Re: How to join large tables with more than 50,000 rows in Splunk?

HiroshiSatoh — Mon, 28 Sep 2020 17:08:56 GMT

Is it possible to use STATS?

(Example)
index=index_A
ID,NAME
1,AAA
2,BBB
3,CCC
4,DDD

index=index_B
ID,COUNT
1,100
2,200
3,300
4,400

index=index_A OR index=index_B|stats first(NAME) as NAME,first(COUNT) as COUNT by ID

ID,NAME,COUNT
1,AAA,100
2,BBB,200
3,CCC,300
4,DDD,400

Re: How to join large tables with more than 50,000 rows in Splunk?

martin_mueller — Wed, 23 Jul 2014 09:53:12 GMT

Here are a few options: http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches

Re: How to join large tables with more than 50,000 rows in Splunk?

0range — Thu, 28 Aug 2014 11:37:05 GMT

ye, it's possible, but it is a little bit strange way.

Re: How to join large tables with more than 50,000 rows in Splunk?

0range — Thu, 28 Aug 2014 11:38:11 GMT

first problem: more than 2 indexes/tables
second problem: different variables for different joins
third problem: different names for the same variable

when I haveto join three indexes A, B, C; and join A with B by id1 and B with C by id2 - it becomes MUCH more complicated. especially when the join-by-fields have different names in different indexes. 20 rows of and awful-formatting search request for such a simple thing... =(

Re: How to join large tables with more than 50,000 rows in Splunk?

wpreston — Thu, 28 Aug 2014 12:37:16 GMT

I would encourage you not to use the join command. Join is RDBMS thinking, but Splunk works with data differently than an RDBMS does and most of the time join is not needed, nor is it the best way to relate data. If you see this excellent post by MuS, he offers some much more efficient ways of searching across multiple tables (or sourctypes, or whatever it is that differentiates your data) without using join. To the problems that you mentioned:

first problem: more than 2 indexes/tables: This is no problem in Splunk. Searching across multiple indexes/sourctypes is very easy, with no need to join for this operation. I routinely search across multiple sourcetypes without needing to use join.

Second problem: different variables for different joins: We can address this once the details of the different variables for different joins are explained.

Third problem: different names for the same variable: Use eval's coalesce function to make it so that you only have to deal with a single variable name.

If (like @martin_mueller asked) you could share more details of your use case or could share your search, we can help you write a better search.

Re: How to join large tables with more than 50,000 rows in Splunk?

0range — Thu, 28 Aug 2014 16:01:46 GMT

Ok, I'll try to show what do I really need now.
I suppose not-inner join's become a little problem now.

A. What I've got
1) sourcetype = a | table _time, id
2) sourcetype = b | table bid, cid
3) sourcetype = c | table cid

B. What I need
earliest=-1d@d latest=@d a
left join
earliest=-1d@d b on a.id = b.bid
left join
earliest=-1d@d c on b.cid = c.cid

and gt a table: a._time, a.id, b.bid, c.cid from this join

...to be continued...

Re: How to join large tables with more than 50,000 rows in Splunk?

0range — Thu, 28 Aug 2014 16:15:11 GMT

// and eval b - unknown... have no time now 😞

It all looks like a tricky and time wasting thing 🙂 I think it's possible to improve

Re: How to join large tables with more than 50,000 rows in Splunk?

somesoni2 — Thu, 28 Aug 2014 18:34:21 GMT

Try this

(sourcetype=a OR sourcetype=b OR sourcetype=c)
| table sourcetype id bid cid | eventstats first(bid) as bid by cid 
| eval id=coalesce(id,"").coalesce(bid,"") 
| stats values(id) as id values(bid) as bid values(cid) as cid values(sourcetype) as sourcetype by id

Re: How to join large tables with more than 50,000 rows in Splunk?

0range — Fri, 29 Aug 2014 07:33:51 GMT

what does the point mean?
| eval id=coalesce(id,"").coalesce(bid,"")

Re: How to join large tables with more than 50,000 rows in Splunk?

somesoni2 — Fri, 29 Aug 2014 12:06:55 GMT

It should have been just |eval id=coalesce(id,bid) but my data for testing this was not having NULL but blank so used that. It basically takes first non-null value from id or bid and assign it to id.

Re: How to join large tables with more than 50,000 rows in Splunk?

0range — Fri, 29 Aug 2014 14:19:57 GMT

the description sounds like coalesce(id, bid, "")

where can I read about the "." operator?

Re: How to join large tables with more than 50,000 rows in Splunk?

martin_mueller — Fri, 29 Aug 2014 15:02:36 GMT

The dot operator concatenates two strings.

Read "more" here: http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Eval#Operators

Re: How to join large tables with more than 50,000 rows in Splunk?

sideview — Wed, 30 Mar 2016 19:01:32 GMT

This is bringing back a long-quiet post, but reading this today I see nobody ever gave you a good stats search. try this one. I believe it addresses all the followup issues you identified in other answers.

sourcetype=a OR sourcetype=b OR sourcetype=c
| eval id=if(sourcetype="b",bid,id)
| eval a_time=if(sourcetype="a",_time,null())
| stats values(cid) as cid values(a_time) as a_time by id

Re: How to join large tables with more than 50,000 rows in Splunk?

srujan9292 — Wed, 02 Jan 2019 15:11:36 GMT

I have a similar issue.
What if my data is something like below:

index=a
left join filter (index=a join Q
(index=xyz|table Q)
table filter
)
table *