Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How to edit my search in order to use the result f...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

demkic

Explorer

12-07-2016
04:50 PM

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

1 Solution

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

rjthibod

Champion

12-07-2016
05:08 PM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

demkic

Explorer

12-08-2016
09:17 AM

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="-"
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

rjthibod

Champion

12-08-2016
10:03 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

demkic

Explorer

12-08-2016
03:53 PM

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!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

rjthibod

Champion

12-09-2016
04:32 AM

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

Highlighted
##

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

demkic

Explorer

12-09-2016
09:42 AM

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

rjthibod

Champion

12-09-2016
10:04 AM

`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".

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

demkic

Explorer

12-09-2016
09:52 AM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

rjthibod

Champion

12-09-2016
10:02 AM

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