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
- :
- Min, max and average in column totals.

- 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

Min, max and average in column totals.

RVDowning

Contributor

05-18-2012
01:12 PM

I have the following search:

```
blah, blah, blah earliest=0
|eval User = UserName." --- ".UserId
| convert mstime(TotalReportRunTime) AS nbrSeconds
| stats count as NbrReports, avg(nbrSeconds) as AvgRunTime
min(nbrSeconds) as MinRunTime max(nbrSeconds) as MaxRunTime by User
| eval AvgRunTime = strftime(AvgRunTime, "%M:%S.%3N")
| eval MinRunTime = strftime(MinRunTime, "%M:%S.%3N")
| eval MaxRunTime = strftime(MaxRunTime, "%M:%S.%3N")
| addtotals fieldname NbrReports row=false col=true
```

What I would like to do is to display on the addtotals line the min value of the MinRuntime column, the max value of the MaxRunTime column and an average of all the run times under the AvgRunTime column (not just the average of displayed values.)

Is this possible?

Highlighted
##

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

Re: Min, max and average in column totals.

Stephen_Sorkin

Splunk Employee

05-18-2012
08:59 PM

Yes. You can do this by using `appendpipe`

instead of `addtotals`

.

Before the evals, add:

```
appendpipe [stats sum(NbrRecords) as NbrRecords avg(AvgRunTime) as AvgRunTime min(MinRunTime) as MinRunTime max(MaxRunTime) as MaxRunTime]
```

EDIT: A comment points out, quite correctly, that it's not valid to take the average of an average.

We can correctly compute the average in one of two different ways.

The first is to have the first stats compute the sufficient statistics for average, say by changing that pair of lines into:

```
| stats count(nbrSeconds) as NbrReports, sum(nbrSeconds) as SumRunTime, min(nbrSeconds) as MinRunTime, max(nbrSeconds) as MaxRunTime by User
| appendpipe [stats sum(NbrRecords) as NbrRecords sum(SumRunTime) as SumRunTime min(MinRunTime) as MinRunTime max(MaxRunTime) as MaxRunTime]
| eval AvgRunTime = SumRunTime/NbrRecords
| fields - SumRunTime
```

We actually could have done this just using the `appendpipe`

above, slightly more opaquely:

```
appendpipe [eval SumRunTime=AvgRunTime*NbrRecords | chart sum(NbrRecords) as NbrRecords eval(sum(SumRunTime)/sum(NbrRecords)) as AvgRunTime min(MinRunTime) as MinRunTime max(MaxRunTime) as MaxRunTime]
```

The other, perhaps better way, is to make use of multivalued fields. First, we augment the User with a grand total line, and then let stats do the rest. In this case, we use the full search:

```
...
| eval User = UserName." --- ".UserId . ";;ALL"
| convert mstime(TotalReportRunTime) AS nbrSeconds
| makemv delim=;; User
| stats count as NbrReports, avg(nbrSeconds) as AvgRunTime, min(nbrSeconds) as MinRunTime, max(nbrSeconds) as MaxRunTime by User
| eval User = if(User == "ALL", null(), User)
| sort - User
| eval AvgRunTime = strftime(AvgRunTime, "%M:%S.%3N")
| eval MinRunTime = strftime(MinRunTime, "%M:%S.%3N")
| eval MaxRunTime = strftime(MaxRunTime, "%M:%S.%3N")
```

Highlighted
##

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

Re: Min, max and average in column totals.

RVDowning

Contributor

05-21-2012
06:25 AM

| convert mstime(TotalReportRunTime) AS nbrSeconds| convert mstime(TotalReportRunTime) AS nbrSeconds2

with

| appendpipe [stats sum(NbrReports) as NbrReports, avg(nbrSeconds2) as AvgRunTime, min(MinRunTime) as MinRunTime, max(MaxRunTime) as MaxRunTime]

but get an empty field for the bottom AvgRunTime

Namely, I was trying to just accumulate the total number of seconds for all reports and then get the average.

Highlighted
##

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

Re: Min, max and average in column totals.

Stephen_Sorkin

Splunk Employee

05-21-2012
07:20 AM

You are absolutely correct. I've amended the answer to handle that.

Highlighted
##

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

Re: Min, max and average in column totals.

RVDowning

Contributor

05-21-2012
12:20 PM

With the second solution the column AvgRunTime did not display and I couldn't get that one to work.

With the third solution I get a syntax error with "AvgRunTime*NbrRecords"

I do get correct results with the final solution. Thanks much!

Highlighted
##

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

Re: Min, max and average in column totals.

Stephen_Sorkin

Splunk Employee

05-21-2012
12:25 PM

I've fixed the third solution.

Highlighted
##

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

Re: Min, max and average in column totals.

RVDowning

Contributor

05-22-2012
07:44 AM

There are still syntax issues with

appendpipe [eval SumRunTime=AvgRunTime*NbrRecords

| stats sum(NbrRecords) as NbrRecords eval(sum(SumRunTime)/sum(NbrRecords)) as AvgRunTime min(MinRunTime) as MinRunTime

max(MaxRunTime) as MaxRunTime]

Error in 'stats' command: The argument 'eval' is invalid

I've played with it a bit, but never could get the syntax right.

Highlighted
##

I've edited that one to use chart, instead of stats, which supports eval as a top level aggregate function.

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

Re: Min, max and average in column totals.

Stephen_Sorkin

Splunk Employee

05-22-2012
03:30 PM