How to calculate Average duration based on Timestamp?

[thiagarajan]

My log looks something similar to this. I will have at least 100 different durations per hour. (Duration is the time which is taken to complete one transaction). My requirement is to create a table/chart with the average duration per hour.

Expected

06/09/2014 | 12:00:00 AM - 12:59:59 AM | 15 ms | i.e (10+20)/2

06/09/2014 | 01:00:00 AM - 01:59:59 AM | 20 ms | i.e (20+20+20)/3

.

.

I should get only 24 results always. The time mentioned in log is not the time indexed by Splunk. It comes from the application logs. I tried lot of things but nothing is working. can someone help me in solving the problem.

Log format

MYTIME,DURATION

06/09/2014 12:01:16 AM 10ms

06/09/2014 12:05:51 AM 20ms

..

06/09/2014 01:01:16 AM 20ms

06/09/2014 01:05:51 AM 20ms

06/09/2014 01:05:51 AM 20ms

..

06/09/2014 02:01:11 AM 70ms

06/09/2014 02:03:11 AM 20ms

...

06/09/2014 03:01:14 PM 74ms

06/09/2014 03:01:16 PM 87ms

...

[somesoni2]

Try this (@Mus solution with output formatting)

Your base search  
| eval DURATION=ltrim(DURATION, "ms")
|timechart span=1h avg(DURATION) as Duration
| eval Date=strftime(_time,"%m/%d/%Y") | eval Period=strftime(_time,"%H:%M:%S %p")." - ".strftime(relative_time(_time,"+1h")-1,"%H:%M:%S %p") | table Date, Period,Duration

[somesoni2]

I have overwritten the DURATION field with value without ms (see |eval DURATION=...., @Mus has created new field myTime) so average is calculated based on that.
The search is creating resultset which can be displayed as chart/table. You would have to select appropriate option to display the data in the format you want.

[thiagarajan]

Thank you somesoni2. I'm bit confused here. Whether we have to calculate avg of duration or myTime.

[MuS · Accepted solution]

Hi thiagarajan,

how about this:

your base search here 
| eval myTime=ltrim(DURATION, "ms")
| timechart span=1h avg(myTime) AS Duration

first you need to trim your duration time to get only numbers, then you can chart it by hour.

hope this helps ...

cheers, MuS

[MuS]

There is no difference if you use

your base search here 
| eval myTime=ltrim(DURATION, "ms")
| timechart span=1h avg(myTime) AS Duration

or

your base search here 
| eval DURATION=ltrim(DURATION, "ms")
| timechart span=1h avg(DURATION) AS Duration

The first uses a newly created field and the second always uses DURATION as field name.

[thiagarajan]

Thank you very much MuS. I'm bit confused here. Whether we have to calculate avg of duration or myTime.