topic Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result? in Splunk Search

If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

brutecat — Mon, 01 Jun 2015 03:49:24 GMT

Hi there,

I was wondering if someone could assist with the following.

I have a table built up as daily averages of 'duration' for events. The 'duration' here refers to the time it takes for the event to complete. I would like to display the standard deviation of these averages at the bottom of the table as a consolidated result. Is this possible in Splunk?

Thanks,

Stan

Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

acharlieh — Mon, 01 Jun 2015 04:49:23 GMT

This is one of the cases where appendpipe is awesome and sounds like exactly what you're looking for. What happens is, this command takes the results you have before it, feeds it as input to a subsearch, and appends the results to your search.

Now you didn't state it explicitly, but I'm going to assume you have some EXISTING SEARCH that's producing a table of two fields (columns), day stating the day, and avgduration being the average duration for the given day.

Now if we want to add the standard deviation of the averages as a new row we just modify your search like so:

EXISTING SEARCH | appendpipe [stats stdev(avgduration) as stddev | eval day="ALL"]

Obviously you're able to rename the fields and use eval to tweak other values as you see fit within and outside of the search. Hope this helps!

Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

brutecat — Mon, 01 Jun 2015 05:04:35 GMT

Hey acharlieh, thanks - perfect

Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

jackson1990 — Mon, 28 Sep 2020 20:06:29 GMT

Assuming your table has Duration_avg as fields,
Use the below command to find standardDeviation of a Field
| stats stddev() AS STDDEV
In your case,for ex:
| stats stddev(Duration_avg ) AS STDDEV

Hope it helps for you...

References:
http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/CommonStatsFunctions

Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

ramdaspr — Mon, 01 Jun 2015 05:16:17 GMT

Sure you can.
Since there isnt a data sample, I am assuming you have 2 columns, Event, Average

index=myindex | table Event,Average | append [search index=myindex | stats stdev(Average) as Average]

The idea is to calculate the std deviation separately and then append the results to your table. The renaming in the subsearch is so that the results are appended to the column Average.

Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

acharlieh — Mon, 01 Jun 2015 07:04:29 GMT

You're welcome!

Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

acharlieh — Mon, 01 Jun 2015 07:07:24 GMT

Simply piping to stats would destroy the table, which since the OP asked to display the average of the standard deviations at the bottom of the table I'm assuming this was not the intention.

Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

acharlieh — Mon, 01 Jun 2015 07:10:36 GMT

While your solution would get you to the correct answer, in this case since the field and data that you're wanting to take the standard deviation of is already in the results of the table, you should prefer appendpipe as I had stated which lets you simply build from results you have already retrieved, instead of incurring a performance penalty by having to go all the way back to the indexes on disk (and possibly remote indexers) which is what would happen by doing an append search as you have suggested.

Re: If I have a table with daily averages, how do I display the standard deviation of these averages at the bottom of the table as a consolidated result?

ramdaspr — Mon, 01 Jun 2015 07:15:35 GMT

Thanks 🙂 I wasnt aware of the appendpipe function