topic Re: Appendcols not lining up Total Volume by SLA Volume in Splunk Search

Appendcols not lining up Total Volume by SLA Volume

fisuser1 — Tue, 10 Dec 2019 14:23:34 GMT

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 %"

Re: Appendcols not lining up Total Volume by SLA Volume

Richfez — Tue, 10 Dec 2019 15:01:36 GMT

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.

Re: Appendcols not lining up Total Volume by SLA Volume

adonio — Tue, 10 Dec 2019 20:32:17 GMT

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

Re: Appendcols not lining up Total Volume by SLA Volume

fisuser1 — Wed, 11 Dec 2019 13:01:52 GMT

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

Re: Appendcols not lining up Total Volume by SLA Volume

adonio — Wed, 11 Dec 2019 14:47:14 GMT

@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

Re: Appendcols not lining up Total Volume by SLA Volume

DavidHourani — Wed, 11 Dec 2019 14:56:10 GMT

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

Re: Appendcols not lining up Total Volume by SLA Volume

DavidHourani — Wed, 11 Dec 2019 14:56:58 GMT

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

Re: Appendcols not lining up Total Volume by SLA Volume

fisuser1 — Wed, 11 Dec 2019 20:08:24 GMT

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

Re: Appendcols not lining up Total Volume by SLA Volume

DavidHourani — Wed, 11 Dec 2019 20:14:39 GMT

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

Re: Appendcols not lining up Total Volume by SLA Volume

sideview — Fri, 13 Dec 2019 00:05:19 GMT

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)