Timechart avg values looking odd.

[theouhuios]

I see values like this when I do |table _time Value .

2014-05-26 16:30:28     48.438430017856341
2014-05-26 16:30:28     53.126020019584374
2014-05-26 16:30:28     43.750840016128315
2014-05-26 21:23:16     100
2014-05-26 21:23:16     100
2014-05-26 21:23:16     100
2014-05-26 23:57:54     100
2014-05-26 23:57:54     100
2014-05-26 23:57:54     100
2014-05-26 23:57:54     100
2014-05-26 23:57:54     100
2014-05-26 23:57:54     100
2014-05-26 23:57:54     100
2014-05-26 23:57:54     100

When I do a timechart avg(Value) as avgV I get the average result as

2014-05-26 16:30:00     48.438430
2014-05-26 17:00:00      
2014-05-26 17:30:00      
2014-05-26 18:00:00      
2014-05-26 18:30:00      
2014-05-26 19:00:00      
2014-05-26 19:30:00      
2014-05-26 20:00:00      
2014-05-26 20:30:00      
2014-05-26 21:00:00     100.000000
2014-05-26 21:30:00      
2014-05-26 22:00:00      
2014-05-26 22:30:00      
2014-05-26 23:00:00      
2014-05-26 23:30:00     100.000000 

Please ignore the timestamp as the first table is a large dataset. When I see the averages I am lost on how it calculated the average like this. The reason why I am trying all this is to figure out this same issue on sparkline trending which is kinda confusing with 0,100,48.4 values.

Any explanation on how it calculates the avg()?

[somesoni2 · Accepted solution]

The timechart command will perform the aggregate function (whatever is specified) over the time slots defined by its attribute span, if available, or by the search time range and available events.

Example: This will calculate the average of the field 'Value' for the time slot 30 min. (your search time range (e.g. Today) will be devided into 30 min bucket and Avg will be calculated for each bucket for the events falls into that bucket.)

....| timechart span=30m avg(Value) as avgV

In your example your can see that for 30 min period of 2014-05-26 16:30:00 to 2014-05-26 17:00:00, there are three events (first 3), so that Avg calculated is for these 3 entries, hence 48.438430.
Similarly for 30 min period 2014-05-26 23:30:00 to 2014-05-27 00:00:00, there are 8 entries and Avg will be calculated for these 8 entries, which is 100.

More details can be found in documentation here.
http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Timechart

[theouhuios]

Thanks for the explanation. Thats what I was trying to understand.

[theouhuios]

I am not looking for a single value actually. Just trying to figure out on how is timechart doing the average. Like for the first value of 48 what all values did it consider to create ?

Search which I amd using is below and the timerange is set to Today.

eventtype="windows_performance" (Host="") Host="ABC-123-456" object="Processor" counter="% Idle Time" instance="" |dedup _raw|timechart avg(Value) as avgV

[somesoni2]

If you're looking for a single value avg of all your values, then timechart is not the command you should be using. just replace 'timechart' with 'stats' (or 'chart') and you're good to go. Timechart summarize data on the basis of time bucket (span which you can define). See documentations for more details.

[theouhuios]

@somesoni2 . Shouldn't the average be a single value? when we do an avg here it gives three values. How does timechart do the averages?

[jkat54]

How about the full search strings you are using? Can you post those instead of the abbreviated versions?

[somesoni2]

What wrong do you find with the timechart output? Could you explain little more?