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
...
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
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
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.
Thank you somesoni2. I'm bit confused here. Whether we have to calculate avg of duration or myTime.
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
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.
Thank you very much MuS. I'm bit confused here. Whether we have to calculate avg of duration or myTime.