Getting Data In

How to calculate Average duration based on Timestamp?

thiagarajan
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
1 Solution

MuS
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

somesoni2
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

somesoni2
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

thiagarajan
Explorer

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

0 Karma

MuS
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

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

thiagarajan
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...