Splunk Search

Appendcols not lining up Total Volume by SLA Volume

Contributor

Trying to do a correlation search for total volume vs sla volume. This search works if I edit the time span to an hour for the past day. Once I pull that span back to the past 7 days or last week, sla volume is not lining up as expected.

Any thoughts of what I am missing here in the appendcols?

eventtype=client_rest_volume earliest=-7d@w0 latest=@w0 
| bucket _time span=1d 
| stats count as tot_trans by _time 
| appendcols     
    [ search eventtype=client_rest_volume earliest=-7d@w0 latest=@w0 
        | bucket _time span=1d 
        | eval rsptime = rsptime/1000 
        | where rsptime < 2000 
        | stats count as sla_trans by _time] 
| eval successrate=((sla_trans/tot_trans)*100)."%"
| rename tot_trans as "Total Transactions"
| rename sla_trans as "Transactions within SLA"
| rename successrate as "SLA Success %"
| table "Total Transactions" "Transactions within SLA" "SLA Success %"
1 Solution

SplunkTrust
SplunkTrust

Hi @fisuser1,

Based off the answer from @adonio you can try this, more compact, faster and guaranteed to make you happy :

eventtype=client_rest_volume earliest=-7d@w0 latest=@w0
 | bucket _time span=1d
 | eval rsptime = rsptime/1000
 | eval is_rsptime = if(rsptime<2000,"1","0")
 | stats sum(is_rsptime) as sla_trans, count as tot_trans by _time
 | eval successrate=((sla_trans/tot_trans)*100)."%"
 | rename tot_trans AS "Total Transactions" sla_trans AS "Transactions within SLA" successrate AS "SLA Success %"

Let me know if that helps and don't forget to upvote if you love the answer.

Cheers,
David

View solution in original post

SplunkTrust
SplunkTrust

it's not a good practice to use append or appendcols for this search. Instead you can use "conditional eval" to create what you need, and then have a single reporting command (timechart / stats / chart / etc) do all the work. this allows the reporting commands to do the work out at the indexer nodes. This should speed up the search by a somewhat large factor and you'll also avoid any truncation limits and finalization limits around append/appendcols.

try this:

eventtype=client_rest_volume earliest=-7d@w0 latest=@w0 
 | eval rsptime = rsptime/1000 
 | eval is_sla_trans = if(rsptime<2000,1,0)
 | timechart span=1d count as tot_trans sum(is_sla_trans) as sla_trans
 | eval successrate=((sla_trans/tot_trans)*100)."%"
 | rename tot_trans as "Total Transactions"
 | rename sla_trans as "Transactions within SLA"
 | rename successrate as "SLA Success %"

By the way I gave a talk at Conf on this sort of thing, and you can see the slides here - https://conf.splunk.com/watch/conf-online.html?search=FNC2751#/

(and possibly by the time you read this, you can also get the recording)

0 Karma

SplunkTrust
SplunkTrust

Hi @fisuser1,

Based off the answer from @adonio you can try this, more compact, faster and guaranteed to make you happy :

eventtype=client_rest_volume earliest=-7d@w0 latest=@w0
 | bucket _time span=1d
 | eval rsptime = rsptime/1000
 | eval is_rsptime = if(rsptime<2000,"1","0")
 | stats sum(is_rsptime) as sla_trans, count as tot_trans by _time
 | eval successrate=((sla_trans/tot_trans)*100)."%"
 | rename tot_trans AS "Total Transactions" sla_trans AS "Transactions within SLA" successrate AS "SLA Success %"

Let me know if that helps and don't forget to upvote if you love the answer.

Cheers,
David

View solution in original post

Contributor

13.71 seconds vs 41.065 seconds search time, you get the lollipop for the day! thank you @DavidHourani!!!!

0 Karma

SplunkTrust
SplunkTrust

Awesome @fisuser1 that's what I'm talking about !

0 Karma

Contributor

I resolved this on my own. Problem was not the search, but the subsearch timing out due to the massive amount of data we were pulling back in the query. I increased the timeout slightly in the limits.conf and am able to see expected results. Thank you for the suggestions though @adonio and @rich7177

0 Karma

SplunkTrust
SplunkTrust

@fisuser1, as this solution works, i would recommend against it. there is no reason for a sub-search and no reason to increase limits. try my search below and check the difference in performance in the job inspector.
your solution is a bad practice

0 Karma

SplunkTrust
SplunkTrust

not sure why appending, you are using the same data ...
try the following code, although there are also other ways to accomplish:

eventtype=client_rest_volume earliest=-7d@w0 latest=@w0
| bucket _time span=1d
| eventstats count as tot_trans by _time
| eval rsptime = rsptime/1000
| eval is_rsptime = if(rsptime<2000,"1","0")
| eventstats sum(is_rsptime) as sla_trans by _time
| rename tot_trans as "Total Transactions"
| rename sla_trans as "Transactions within SLA"
| rename successrate as "SLA Success %"
| table "Total Transactions" "Transactions within SLA" "SLA Success %"

hope it helps

0 Karma

SplunkTrust
SplunkTrust

@adonio you're missing : | eval successrate=((sla_trans/tot_trans)*100)."%" somewhere in your search 🙂

0 Karma

SplunkTrust
SplunkTrust

I'd examine your output when they're not lining up - specifically, your base search piles 'em together by _time and takes all of them, but the appendcols search only takes the one where rsptime is under 2000, then counts what's left. What if all of the rsptimes for a particular timeslot were > 2000?

If I were you, I'd get rid of the appendcols and move that logic into the main search. It'll be faster and it'll get around this problem entirely.

Could you provide a bit of data that you are using, and also what it is (in English) that you are after? That would probably be useful to us.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!