Splunk Search

Appendcols not lining up Total Volume by SLA Volume

fisuser1
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

DavidHourani
Super Champion

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

sideview
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

DavidHourani
Super Champion

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

fisuser1
Contributor

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

0 Karma

DavidHourani
Super Champion

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

0 Karma

fisuser1
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

adonio
Ultra Champion

@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

adonio
Ultra Champion

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

DavidHourani
Super Champion

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

0 Karma

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...