Getting Data In
Highlighted

## How to calculate Average duration based on Timestamp?

Explorer

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

...

Tags (3)
1 Solution
Highlighted

## Re: How to calculate Average duration based on Timestamp?

SplunkTrust

Hi thiagarajan,

``````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

Highlighted

## Re: How to calculate Average duration based on Timestamp?

Explorer

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

Highlighted

## Re: How to calculate Average duration based on Timestamp?

SplunkTrust

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.

Highlighted

## Re: How to calculate Average duration based on Timestamp?

SplunkTrust

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
``````
Highlighted

## Re: How to calculate Average duration based on Timestamp?

Explorer

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

Highlighted

## Re: How to calculate Average duration based on Timestamp?

SplunkTrust

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.