Splunk Search
Highlighted

How to edit my search in order to use the result from one calculation in another calculation?

Explorer

Hi there,

I am trying to calculate the percent of failure types by the total number of transactions (including where success=true), however, the way the data gets logged in my case is making this more difficult than it should be.
Here is where I am having the issue. My current query reads:

base search earliest=-1h 
| stats count(eval(success="false")) as Failures count(eval(event="transaction")) as TotalTransactions 
| eval percent_failure=round(Failures*100/TotalTransactions , 1) 
| sort -percent_failure 

Everything is calculating correctly, and my result from the above is the total failure rate BUT I would like this information to be displayed by failuretype. However, when I add "by failuretype" in the query from below, it does not calculate properly because it is not including the transactions that are successful as they do not have a failure_type.

base search earliest=-1h 
| stats count(eval(success="false")) as Failures count(eval(event="transaction")) as TotalTransactions by failure_type
| eval percent_failure=round(Failures*100/TotalTransactions , 1) 
| sort -percent_failure 

It would be great if there was a way I could take a number from one calculation, example: "TotalTransactions"

base search earliest=-1h 
| stats count(eval(event="transaction")) as TotalTransactions 

and use it in another calculation BUT displayed by Failure Type WITHOUT the number for TotalTransactions changing, example:

base search earliest=-1h 
 | stats count(eval(success="false")) as Failures by failure_type
 | eval percent_failure=round(Failures*100/TotalTransactions , 1) 
 | sort -percent_failure 

(Note: my base search actually will remain the same)
I hope this makes sense? Please let me know if I can clarify some more
Thank you

0 Karma
Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Champion

You could use eventstats to put the TotalTransactions value in place on all the needed events. Try this.

    base search... earliest=-7d 
    | fields _time success event failure_type
    | eval marker = if(event="transaction", 1, 0) 
    | eval failure = if(success="false", 1, 0)
    | addinfo 
    | eval time_marker = if(info_max_time - 3600 <= _time, "Last Hour", "Last Week")
    | eventstats sum(marker) as TotalTransactions by time_marker
    | stats sum(failure) as Failures max(TotalTransactions) as TotalTransactions by failure_type time_marker
    | appendpipe [stats max(TotalTransactions) as TotalTransactions by time_marker | stats sum(TotalTransactions) as FullTotal]
    | eventstats max(FullTotal) as FullTotal
    | eventstats sum(Failures) as FullFailures by failure_type
    | eval percent_failure = if(time_marker="Last Hour", round(Failures*100/TotalTransactions, 1), round(FullFailures*100/FullTotal, 1))
    | chart max(percent_failure) over failure_type by time_marker
    | fillnull value="-"
    | rename "Last Week" as "Decline Rate (Last Week)", failure_type as "Error Type", "Last Hour" as "Decline Rate (Last Hour)"

View solution in original post

Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Explorer

This is great, thank you! @rjthibod
I would actually like to display the information on the same table compared to the last 7 days as well. Is there any way to align the results that belong to the same failure type to be on the same row? For example right now it displays "insufficient funds" on two individual rows where one result is in the failure type last hour column and the other is in the failure type last week column. I believe the command "streamstats" should do the trick, is there an easier way? Also, is there a way to have the query be faster? I am only parsing 7 days and my base search shouldn't have that much to parse either. Any insights would be much appreciated.

Below is my query:

base search... earliest=-7d 
| eval marker = if(event="transaction", 1, 0) 
| eventstats sum(marker) as TotalTransactions 
| stats count(eval(success="false")) as Failures max(TotalTransactions) as TotalTransactions by failure_type 
| eval percent_failure=round(Failures*100/TotalTransactions , 1) 
| sort -percent_failure 
| head 10 
| append 
    [ base search... earliest=-1h 
    | eval marker = if(event="transaction", 1, 0) 
    | eventstats sum(marker) as TotalTransactions 
    | stats count(eval(success="false")) as Failures max(TotalTransactions) as TotalTransactions by failure_type 
    | eval percent_failure_lasthour=round(Failures*100/TotalTransactions , 1) 
    | sort -percent_failure_lasthour 
    | head 10 ] 
| sort -percent_failure 
| table failure_type percent_failure_lasthour percent_failure 
| rename percent_failure as "Decline Rate (Last Week)", failure_type as "Error Type", percent_failure_lasthour as "Decline Rate (Last Hour)" 
| fillnull value="-"
0 Karma
Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Champion

Try this. Maybe not the most elegant, but should be faster than what you have since it only searches the data once. You can decide if you want to sort/limit a specific column. For now, all the rows are left in.

 base search... earliest=-7d 
 | fields _time success event failure_type
 | eval marker = if(event="transaction", 1, 0) 
 | eval failure = if(success="false", 1, 0)
 | addinfo 
 | eval time_marker = if(info_max_time - 3600 >= _time, "Last Hour", "Last Week")
 | eventstats sum(marker) as TotalTransactions by time_marker
 | stats sum(failure) as Failures max(TotalTransactions) as TotalTransactions by failure_type time_marker
 | appendpipe [stats max(TotalTransactions) as TotalTransactions sum(Failures) as Failures by time_marker | stats sum(TotalTransactions) as FullTotal sum(Failures) as FullFailures]
 | eval percent_failure = if(time_marker="Last Hour", round(Failures*100/TotalTransactions, 1), round(FullFailures*100/FullTotal, 1))
 | chart max(percent_failure) over time_marker by failure_type
 | fillnull value="-"
 | rename "Last Week" as "Decline Rate (Last Week)", failure_type as "Error Type", "Last Hour" as "Decline Rate (Last Hour)" 
0 Karma
Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Explorer

Hi @rjthibod
Ideally, I was looking to have three columns where;
field1: Error Type
field2: Decline Rate (Last Hour)
field 3: Decline Rate (Last Week)

The query you suggested seems to only calculate "Last Hour" (leaving out Last Week for some reason) where I have 11 fields (columns) with
field 1: time_marker
field 2: error type #1
field 3: error type #2
field 4: error type #3
...
...
field 11: error type #10

I guess essentially I wanted the "transpose" of your solution.

I managed to get what I was looking for by doing the following search:

base search earliest=-7d 
| eval marker = if(event="transaction", 1, 0) 
| eventstats sum(marker) as TotalTransactions 
| stats count(eval(success="false")) as Failures max(TotalTransactions) as TotalTransactions by failure_type 
| eval percent_failure=round(Failures*100/TotalTransactions , 1) 
| sort -percent_failure 
| head 10 
| streamstats count as WeekRank 
| append 
    [ base search earliest=-1h 
    | eval marker = if(event="transaction", 1, 0) 
    | eventstats sum(marker) as TotalTransactions 
    | stats count(eval(success="false")) as Failures max(TotalTransactions) as TotalTransactions by failure_type 
    | eval percent_failure_lasthour=round(Failures*100/TotalTransactions , 1) 
    | sort -percent_failure_lasthour 
    | head 10 
    | streamstats count as HourRank ] 
| stats first(WeekRank) as WeekRank first(HourRank) as HourRank values(Failures) as Failures values(percent_failure_lasthour) as percent_failure_lasthour values(percent_failure) as percent_failure by failure_type 
| sort -percent_failure 
| table failure_type percent_failure_lasthour percent_failure 
| rename percent_failure as "Decline Rate (Last Week)", failure_type as "Error Type", percent_failure_lasthour as "Decline Rate (Last Hour)" 
| fillnull value="-"

I am still very interested in learning how I can use YOUR query to get the same results. Is there a way to simply transpose the table and have it calculate "Last Week" since it is not displaying currently.

I also need to brush up on the documentation since I am not too clear with the command "appendpipe" and "addinfo"

thanks so much for your help!

0 Karma
Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Champion

I am so sorry for getting it wrong. I should have spent more time emulating your data in order to test it. Here is what I think is a working version that fixes the logic errors I had.

  base search... earliest=-7d 
  | fields _time success event failure_type
  | eval marker = if(event="transaction", 1, 0) 
  | eval failure = if(success="false", 1, 0)
  | addinfo 
  | eval time_marker = if(info_max_time - (24*3600) <= _time, "Last Hour", "Last Week")
  | eventstats sum(marker) as TotalTransactions by time_marker
  | stats sum(failure) as Failures max(TotalTransactions) as TotalTransactions by failure_type time_marker
  | appendpipe [stats max(TotalTransactions) as TotalTransactions by time_marker | stats sum(TotalTransactions) as FullTotal]
  | eventstats max(FullTotal) as FullTotal
  | eventstats sum(Failures) as FullFailures by failure_type
  | eval percent_failure = if(time_marker="Last Hour", round(Failures*100/TotalTransactions, 1), round(FullFailures*100/FullTotal, 1))
  | chart max(percent_failure) over failure_type by time_marker
  | fillnull value="-"
  | rename "Last Week" as "Decline Rate (Last Week)", failure_type as "Error Type", "Last Hour" as "Decline Rate (Last Hour)"
0 Karma
Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Explorer

It's totally fine, thank you so much for your help. It works perfect now! If it's not too much trouble to ask - could you explain what the "appendpipe" command is doing here in our case?

0 Karma
Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Champion

The appendpipe is needed because of the way I split the data for the "time_marker" field. Before that segment runs, the field TotalTransactions for the "Last Week" time period is not quite correct, i.e., it is missing the TotalTransactions from the "Last Hour". The appendpipde segment just aggregates all of the TotalTransactions values for both and puts them in the proper total value "FullTotal".

0 Karma
Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Explorer

One tiny comment - I noticed when I click on "Last Hour" to view only those results the time stamp says:

(12/2/16 9:45:09.000 AM to 12/9/16 9:45:09.949 AM)

(I double checked the data for last hour individually and it does seem like something is not being calculated correctly for that time frame.)

Should I tweak this line of command somehow?
| eval timemarker = if(infomax_time - (24*3600) <= _time, "Last Hour", "Last Week")

I'm sorry for the hassle.

0 Karma
Highlighted

Re: How to edit my search in order to use the result from one calculation in another calculation?

Champion

No, I am so sorry for not catching that I left the 24*3600 in there. Should just be 3600.

   base search... earliest=-7d 
   | fields _time success event failure_type
   | eval marker = if(event="transaction", 1, 0) 
   | eval failure = if(success="false", 1, 0)
   | addinfo 
   | eval time_marker = if(info_max_time - 3600 <= _time, "Last Hour", "Last Week")
   | eventstats sum(marker) as TotalTransactions by time_marker
   | stats sum(failure) as Failures max(TotalTransactions) as TotalTransactions by failure_type time_marker
   | appendpipe [stats max(TotalTransactions) as TotalTransactions by time_marker | stats sum(TotalTransactions) as FullTotal]
   | eventstats max(FullTotal) as FullTotal
   | eventstats sum(Failures) as FullFailures by failure_type
   | eval percent_failure = if(time_marker="Last Hour", round(Failures*100/TotalTransactions, 1), round(FullFailures*100/FullTotal, 1))
   | chart max(percent_failure) over failure_type by time_marker
   | fillnull value="-"
   | rename "Last Week" as "Decline Rate (Last Week)", failure_type as "Error Type", "Last Hour" as "Decline Rate (Last Hour)"
0 Karma