Timechart with avg from calculated values

[zscgeek]

I am trying to build a timechart that includes the avg rate we pay our carrier per min over time. The issue is for me to calculate this I really need to have

My data includes the number of minutes billed(min) and the amount(fee). I need to get the avg by dividing fee over min.

My source data looks like this:

date,carrier,location,calls,min,fee
2010-10-31 GMT+12:00/+0000,AcmeTel,AB,2,0,0.0
2010-10-31 GMT+11:00/+0000,AcmeTel,AB,13,4,612340.02499961853027
2010-10-31 GMT+10:00/+0000,AcmeTel,AB,1121,66,57317.4404942989349
2010-10-31 GMT+09:00/+0000,AcmeTel,AB,6951,317,1237.1152408123016
2010-10-31 GMT+08:00/+0000,AcmeTel,AB,13548,379,21623.901252269745
2010-10-31 GMT+07:00/+0000,AcmeTel,AB,9162,362,14353.2052426338196
2010-10-31 GMT+06:00/+0000,AcmeTel,AB,3190,181,7077.094498872757
2010-10-31 GMT+05:00/+0000,AcmeTel,AB,1000,63,2765.5147514343262
2010-10-31 GMT+04:00/+0000,AcmeTel,AB,1155,103,4368.79524874687195
2010-10-31 GMT+03:00/+0000,AcmeTel,AB,535,55,2628.31174778938293

Ideally my query would look something like this:

sourcetype="cdrreports" NOT location | timechart sum(fee), sum(min) by carrier | rename sum(fee) as fee | rename sum(min) as min | eval AvgPerMin=fee/min

this will not work however because timechart can only have a single value calculated.

The simple sounding solution is to do something like this:

sourcetype="cdrreports" NOT location | eval AvgPerMin=fee/min | timechart avg(AvgPerMin) by carrier

That won't work however since you are then creating averages of averages.

Any idea how to create something like this?

[Genti · Accepted solution]

So i created some more data in order to make this work a bit better for me. Here is the data i am using:

gzaimi@bigmac ~/Testing/logs cat rj.log 
#date,carrier,location,calls,min,fee
2010-10-31 GMT+12:00/+0000,AcmeTel,AB,2,1,1.0
2010-10-31 GMT+12:00/+0000,Voda,AB,2,2,3.0
2010-10-31 GMT+11:00/+0000,AcmeTel,AB,13,5,61340.02499961853027
2010-10-31 GMT+11:00/+0000,Voda,AB,13,4,61230.02499961853027
2010-10-31 GMT+10:00/+0000,AcmeTel,AB,111,66,5317.4404942989349
2010-10-31 GMT+10:00/+0000,Voda,AB,1121,66,57317.4404942989349
2010-10-31 GMT+09:00/+0000,AcmeTel,AB,695,317,237.1152408123016
2010-10-31 GMT+09:00/+0000,Voda,AB,6951,317,1237.1152408123016
2010-10-31 GMT+08:00/+0000,AcmeTel,AB,1358,379,2623.901252269745
2010-10-31 GMT+08:00/+0000,Voda,AB,1358,379,1623.901252269745
2010-10-31 GMT+07:00/+0000,AcmeTel,AB,9162,362,14353.2052426338196
2010-10-31 GMT+07:00/+0000,Voda,AB,912,362,1433.2052426338196
2010-10-31 GMT+06:00/+0000,AcmeTel,AB,3190,181,7077.094498872757
2010-10-31 GMT+06:00/+0000,Voda,AB,319,11,7077.094498872757
2010-10-31 GMT+05:00/+0000,AcmeTel,AB,1000,63,2765.5147514343262
2010-10-31 GMT+05:00/+0000,Voda,AB,100,63,265.5147514343262
2010-10-31 GMT+04:00/+0000,AcmeTel,AB,155,103,4368.79524874687195
2010-10-31 GMT+04:00/+0000,Voda,AB,1155,103,4368.79524874687195
2010-10-31 GMT+03:00/+0000,AcmeTel,AB,535,55,2628.31174778938293
2010-10-31 GMT+03:00/+0000,Voda,AB,535,55,2628.31174778938293

here is the search i am running:

sourcetype="rjlog" carrier!="carrier" | eval feemin=fee/min| table _time min fee feemin

Here is the graph i am getting using advanced charting view, changing y-axes to use log (instead of linear) using connected etc. (check the attributes on the attached images).

Here, Here and Here

EDIT:
Taking in consideration your comment then what you want you can do by:

<your source/sourcetype> | timechart eval(sum(fee)/sum(min)) as myavg

This will give you the 0.0109 value that you were looking for.

Best,
.gz

[Genti]

There's actually a whole page on eval. http://www.splunk.com/base/Documentation/4.1.5/SearchReference/Eval however you are right that this page doesnt mention "eval() as" but only "eval xyz=...". There are however a couple of examples of it here: http://www.splunk.com/base/Documentation/4.1.5/Knowledge/Searchfortransactions

[zscgeek]

btw - the version of the query "timechart eval(sum(fee)/sum(min)) as myavg" is exactly what I needed. Thanks.

[zscgeek]

btw - eval() is not in the function list for timechart:

http://www.splunk.com/base/Documentation/4.1.5/SearchReference/CommonStatsFunctions

[Genti]

Edited my answer above to comply with your comment.

[zscgeek]

Genti,

Thats the easy looking answer (and was my first solution) but has one fatal flaw, this is averaging an average, so the final value is totally skewed.

To give you an example.

Lets assume I have record with 10 minutes and a total fee of $10. Avg would work out of being $1. Lets then add a second record with 10,000 minutes and fee of $100. Avg fee would be $0.01. If you then call avg() on the fee you would get a result of $0.51. The correct result here would be $0.010989010989011 (10,010 minutes / $110)

That make any sense? Any ideas on how to work around this?

[Simeon]

In your query, it looks like you were really trying to do the following with a chart.

sourcetype="cdrreports" NOT location | chart sum(fee) as fee sum(min) as min | eval AvgPerMin=fee/min | timechart median(AvgPerMin) 

However, after more evaluation the problem seems much simpler:

sourcetype="cdrreports" NOT location | timechart span=1m avg(fee) by carrier 

It's possible I am completely misunderstanding your intent, but the above would find the average fee every minute.

[zscgeek]

So the key metric I am trying to chart is the avg price per min. To get that you need to take the price we paid (fee) from each input line and divide it by the number of minutes (min) in that line as well.

Ideally I would like a timechart of this value by each carrier so I can see how they have changed over time.