Getting Data In

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

Revered Legend

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

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.

Explorer

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

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

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.

Explorer

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

Did you miss .conf21 Virtual?

### Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE! Catch Up Now >>

Get Updates on the Splunk Community!