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

...

0 Karma
Highlighted

Re: How to calculate Average duration based on Timestamp?

SplunkTrust
SplunkTrust

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

View solution in original post

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.

0 Karma
Highlighted

Re: How to calculate Average duration based on Timestamp?

SplunkTrust
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
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.

0 Karma
Highlighted

Re: How to calculate Average duration based on Timestamp?

SplunkTrust
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.

0 Karma