Splunk Search
Highlighted

Min, max and average in column totals.

Contributor

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?

0 Karma
Highlighted

Re: Min, max and average in column totals.

Splunk Employee
Splunk Employee

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

Re: Min, max and average in column totals.

Contributor

Ah, great answer but it doesn't quite work. avg(AvgRunTime) is an average of averages which isn't arithmetically correct. I tried:
| 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.

0 Karma
Highlighted

Re: Min, max and average in column totals.

Splunk Employee
Splunk Employee

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

0 Karma
Highlighted

Re: Min, max and average in column totals.

Contributor

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!

0 Karma
Highlighted

Re: Min, max and average in column totals.

Splunk Employee
Splunk Employee

I've fixed the third solution.

0 Karma
Highlighted

Re: Min, max and average in column totals.

Contributor

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.

0 Karma
Highlighted

Re: Min, max and average in column totals.

Splunk Employee
Splunk Employee

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

0 Karma