Splunk Search

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

demkic
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 failure_type. However, when I add "by failure_type" 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
1 Solution

rjthibod
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

rjthibod
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)"

demkic
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

rjthibod
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

demkic
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

rjthibod
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

demkic
Explorer

Actually, if you have some time, could you also please explain this line of command to me?

| eval time_marker = if(info_max_time - 3600 <= _time, "Last Hour", "Last Week")

What is info_max_time? And why 3600? Many thanks!

0 Karma

rjthibod
Champion

The field "info_max_time" is added to each row by the addinfo SPL command. It represents the epoch time value of the latest search time value for the query. So, this search segment allows you to differentiate the events that represent the last hour (where the _time is greater than or equal to or greater than info_max_time minus 3600 seconds, one hour) from those events later than the last hour, "Last Week". This command is why we have to add back the values from "Last Hour" later in the search (the appendpipe part).

0 Karma

demkic
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 time_marker = if(info_max_time - (24*3600) <= _time, "Last Hour", "Last Week")

I'm sorry for the hassle.

0 Karma

rjthibod
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

demkic
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

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...