Splunk Search

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

Shashank_87
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

to4kawa
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

Shashank_87
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

to4kawa
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

richgalloway
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

Shashank_87
Explorer

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

0 Karma

richgalloway
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

isoutamo
SplunkTrust
SplunkTrust

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

Shashank_87
Explorer

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

0 Karma

richgalloway
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...