topic Re: What to do when appendcols command can't handle larger counts? in Splunk Search

What to do when appendcols command can't handle larger counts?

f5x6kb8 — Thu, 09 Mar 2017 17:57:30 GMT

We need to determine a 30 day average based on the count of two events, a request and a response. The issue is that each generate upward of 30K each...hourly. The search below works great for short durations, but once the duration increases, the count data from the appendcols is all over the map.
Any ideas would be greatly appreciated!!

index=blah blah blah 
| search field="A Response*"  
| timechart span=1h count as response  
| appendcols [ search field="A Request*"   
| timechart span=1h count as request ]  
| eval reciprocal=round(response/request,2)*100

Re: What to do when appendcols command can't handle larger counts?

niketn — Thu, 09 Mar 2017 21:32:26 GMT

You need to summarize data per hour (which implies you will reduce daily events from 30K*24 to 24). Then you will be able to run subsearches like appendcol without dropping data.

Refer to sitimechart and collect commands on Splunk documentation for your reference.
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Sitimechart
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Collect

Re: What to do when appendcols command can't handle larger counts?

somesoni2 — Thu, 09 Mar 2017 21:53:30 GMT

and/or it may be possible to avoid the appendcols altogether. We can have a look if you can share full search of yours.

Re: What to do when appendcols command can't handle larger counts?

DalJeanis — Thu, 09 Mar 2017 23:40:30 GMT

Eliminate appendcols by just processing the data once for both types.

index=blah blah blah 
 | search field="A Response*" OR field="A Request*"
 | bin _time span=1h
 | eval request=if(like(field,"A Request%"),1,0)
 | eval response=if(like(field,"A Response%"),1,0)
 | timechart span=1h sum(request) as request, sum(response) as response  
 | eval reciprocal=round(response/request,2)*100

The search might also be written like this. not sure which is more efficient, and not sure whether the .* on the end is needed.

  | regex field="^A Re(sponse|quest).*"

Re: What to do when appendcols command can't handle larger counts?

f5x6kb8 — Fri, 10 Mar 2017 13:25:56 GMT

Worked like a charm! Thank you very much for taking the time. Have a Great Weekend!