topic Re: How to join two searches? in Splunk Search

How to join two searches?

Shashank_87 — Thu, 28 Jun 2018 11:35:13 GMT

Hi, I am trying to join two of my searches in splunk using a common field uniqueID but I am getting a error in Splunk Job inspector - SubSearch produced more than 50k results, truncating to max out 50k.
I can't change limits.conf and I have to use the query to get the desired result.

Really appreciate if someone can help on this? My query is something like this -

index="A" sourcetype="test*" requested_content="/index" 
| join uniqueId
    [ search [search B] ] 
| timechart span=1h count

Re: How to join two searches?

Sukisen1981 — Thu, 28 Jun 2018 11:48:49 GMT

Hi, there is always a limitation set on the number of rows you can run your join command with,
Typically your solunk admin would have set a limit of 50k (we have 25 k) as a threshold.
You need to tell us your use case and let us see if we can do this without a join / join with some trimming.
Needless to say, running such a big join will sooner or later give you severe performance issues.

Re: How to join two searches?

Shashank_87 — Thu, 28 Jun 2018 12:12:31 GMT

so I am looking to get the response time for a particular page. Now that pages comes in both logged in and logged out versions and I am only looking for logged in customers.
My search 1 gives the page load time (response_time) of the requested content but it doesn't tell you if it was logged out page or logged in page. My 2nd search gives me the events which will only come in case of Logged in customer.
So I need to join two searches on the basis of a common field called uniqueID.

Re: How to join two searches?

renjith_nair — Thu, 28 Jun 2018 12:46:19 GMT

Hi @Shashank_87,

Try adding format to the subsearch

index="A" sourcetype="test*" requested_content="/index" 
| join uniqueId
[ search [search B|format] ] 
| timechart span=1h count

Also if you have a common unique field, try

index="A" OR index="B"  requested_content="/index"  //Add your common search terms from both searches
|eventstats count(uniqueId)  as dups
|where dups >1
|timechart span=1h count

If there is only one uniqueId in each event, above result will have duplicates only if its present in both searches and thus we can filter the results which have entries in both searches
If the above doesnt work, provide us some sample from both searches

Re: How to join two searches?

woodcock — Sat, 30 Jun 2018 15:21:50 GMT

As others have mentioned, this is exactly why join should be avoided. Try this:

(index="A" sourcetype="test*" requested_content="/index") OR (search B with uniqueId)
| bin _time span=1h
| stats dc(sourcetype) AS num_sourcetypes values(sourcetype) BY uniqueId _time
| where num_sourcetypes==2 OR Maybe Different Logic HERE
| timechart span=1h count