topic Re: How to get an average value in timechart with time format? in Splunk Search

How to get an average value in timechart with time format?

SapthagiriAavik — Thu, 25 Jan 2018 18:36:19 GMT

I want an average answering duration of each HR persons in hh:mm format
rep_duration is the time taken to answer and
search query is this

index=bora | timechart span=5d avg(rep_duration) as  AHT_avg  by hr_answerd
|eval hr=floor(AHT_avg/60)
|eval min=round(AHT_avg%60)
|strcat hr ":" min  AHT_avg
|fields - hr,min

and expected output is this
hr_answered date1 date2 date3 .....
name hh:mm hh:mm hh:mm ...

ex;
hr_answerd 22/01/2018 23/01/2018

umesh 22:05 12:05
john 36:55 35:12
umesh 33:51 25:12
john 72:55 15:12

Re: How to get an average value in timechart with time format?

somesoni2 — Thu, 25 Jan 2018 18:52:07 GMT

Try like this

index=bora | bucket span=5d _time | stats avg(rep_duration) as AHT_avg by _time hr_answered
| eval AHT_avg=tostring(AHT_avg, "duration")

Re: How to get an average value in timechart with time format?

SapthagiriAavik — Thu, 25 Jan 2018 20:07:42 GMT

Thank you for suggesstion, how to get only as hh:mm duration is showing dd:hh:mm:ss

Re: How to get an average value in timechart with time format?

SapthagiriAavik — Thu, 25 Jan 2018 20:16:41 GMT

the format im looking for is

hr_answerd 22/01/2018 23/01/2018
umesh 22:05 12:05
john 36:55 35:12
umesh 33:51 25:12
john 72:55 15:12

Re: How to get an average value in timechart with time format?

somesoni2 — Thu, 25 Jan 2018 20:36:48 GMT

Change last eval to | eval AHT_avg=strptime(strftime(AHT_avg, "%s"),"%H:%M")

Re: How to get an average value in timechart with time format?

mayurr98 — Fri, 26 Jan 2018 06:44:08 GMT

hey I think you want something like this

try this

index=bora | bin span=1d _time | stats avg(rep_duration) as AHT_avg by _time hr_answered | eval AHT_avg=strftime(AHT_avg, "%s") | eval mins=floor((AHT_avg/60)%60) | eval hrs=floor((AHT_avg/3600)%60)
 | eval f_hrs=if(len(hrs)=1,"0".tostring(hrs), tostring(hrs))
 | eval f_mins=if(len(mins)=1,"0".tostring(mins), tostring(mins))  | eval result=f_hrs.":".f_mins | eval time=strftime(_time,"%d/%m/%Y") | chart values(result) over hr_answered by time

adjust | bin span=1d _time span according to your need.
let me know if this helps!

Re: How to get an average value in timechart with time format?

mayurr98 — Fri, 26 Jan 2018 06:58:16 GMT

| eval AHT_avg=strptime(strftime(AHT_avg, "%s"),"%H:%M") will give you no results as you are converting AHT_avg to seconds and then to epoch time using strptime giving the format of %s to %H:%M which is not possible. and even if you write strftime it will give you false results.
try this run anywhere search

index=_internal total_k_processed=*  | bucket span=1d _time | stats avg(total_k_processed) as AHT_avg by _time name | eval z=strftime(strftime(AHT_avg, "%s"),"%H:%M")

I hope you understand what I am trying to say.

Re: How to get an average value in timechart with time format?

somesoni2 — Tue, 29 Sep 2020 17:51:03 GMT

Thanks @mayurr98. Used wrong order of functions. @SapthagiriAavikov, please use ´| eval AHT_avg=strftime(strptime(AHT_avg, "%s"),"%H:%M")´

Re: How to get an average value in timechart with time format?

mayurr98 — Fri, 26 Jan 2018 09:35:47 GMT

you are welcome 🙂 , now strftime will give you results but it will give you wrong results as it solely for time function so %H can not be greater 24 and in his case it could be anything like 30:50,35:40,36:55..so the only way to do this use eval function and convert it manually.

Re: How to get an average value in timechart with time format?

SapthagiriAavik — Mon, 29 Jan 2018 13:07:34 GMT

Thanks @mayurr98 , its working