Splunk Search

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

Explorer

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

0 Karma

Ultra Champion
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.

0 Karma

Explorer

@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?

0 Karma

Ultra Champion

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.

0 Karma

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Champion

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

0 Karma

Explorer

@richgalloway @soutamo 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.

0 Karma

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma