topic Re: Subsearch produced 50000 results, truncating to 50000 - Need help! in Splunk Search

Subsearch produced 50000 results, truncating to 50000 - Need help!

Shashank_87 — Wed, 30 Sep 2020 04:54:00 GMT

Hi, I am dealing with a situation here. Trying to join 2 queries to find out the peak hour volume in last 90 days on a particular page.
The data needs to come from two queries because of the use of referer in the sub-search.

limits.conf can't be modified because there are so many records and due to performance.

So Is there any alternate way or if someone can help me with another alternate query, that will be greatly appreciated.

index=test sourcetype="access_combined_wcookie" req_content="/checkout/yourdetails" status=200
| join uniqueId max=0
[ search index=test sourcetype="access_combined_wcookie" req_content="/reviewbasket" referer="https://www.site.com/content/site/homePage.html*"]
| timechart span=1h count
| sort - count

@manjunathmeti @somesoni2 @to4kawa @woodcock - Will you guys be able to help as you helped me previously?

Thanks very much in advance

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

richgalloway — Fri, 03 Apr 2020 17:13:13 GMT

Try this as a start.

index=test sourcetype="access_combined_wcookie" ((req_content="/checkout/yourdetails" status=200) 
  OR (req_content="/reviewbasket" referer="https://www.site.com/content/site/homePage.html*"))
| stats values(*) as * by _time, uniqueId
| timechart span=1h count
| sort - count

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

Shashank_87 — Fri, 03 Apr 2020 17:37:18 GMT

@richgalloway Thanks for the response but it doesn't give anything. When i run, it says no result found.

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

richgalloway — Fri, 03 Apr 2020 18:35:51 GMT

Do you get results with index=test sourcetype="access_combined_wcookie" ((req_content="/checkout/yourdetails" status=200) OR (req_content="/reviewbasket" referer="https://www.site.com/content/site/homePage.html*"))? If so, do they all have a uniqueId field? If not, that may be the problem.
If you still don't get any results then you may have to revert to using join, but will have to change the subsearch to return fewer results.

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

isoutamo — Sat, 04 Apr 2020 21:14:05 GMT

One way is to 1st run subsearch to lookup and yeh utilize it in a second one. But I prefer using stats as richgalloway already propose.

R. Ismo

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

Shashank_87 — Wed, 30 Sep 2020 04:54:25 GMT

@richgalloway @isoutamo Hi, If i just use Stats like below it is giving the result but then when i use the following timechart it doesn't give anything.

index=test sourcetype="access_combined_wcookie" ((req_content="/checkout/yourdetails" status=200)
OR (req_content="/reviewbasket" referer="https://www.site.com/content/site/homePage.html*"))
| stats values(*) as * by uniqueId

Can you please help.

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

to4kawa — Sun, 05 Apr 2020 16:33:15 GMT

index=test sourcetype="access_combined_wcookie" ((req_content="/checkout/yourdetails" status=200) 
OR (req_content="/reviewbasket" referer="https://www.site.com/content/site/homePage.html*"))
| stats min(_time) as _time dc(req_content) as flag by uniqueId
| where flag > 1
| timechart span=1h count
| sort - count

timechart needs _time and stats with * does not works internal fields.

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

Shashank_87 — Wed, 30 Sep 2020 04:54:39 GMT

@to4kawa Hi, Thank you very much for your response. I am somewhat close to the answer but actually what i need is Peak hour volume on the requested content. For example -
Peak hour volume of "/checkout/yourdetails" and like that there are couple of other scenarios.
What your is doing is counting all of them as one unit using flag so the result won't be accurate. I don't think this will get the total hits on that particular .

Like if i do something like this -
| stats min(_time) as _time list(req_content) as list dc(req_content) as flag by uniqueId
under the list column i could see that page is called 3 times but that will be counted as one with above query

Can you help?

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

to4kawa — Mon, 06 Apr 2020 21:28:22 GMT

my query aims to optimize your query.
actually what i need is Peak hour volume on the requested content.
your query is not for this.
I don't know your log. I can only modify your query.

Re: Subsearch produced 50000 results, truncating to 50000 - Need help!

richgalloway — Tue, 07 Apr 2020 12:23:23 GMT

timechart fails because stats is not passing on the _time field. See my corrected answer.