Splunk Search

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

SapthagiriAavik
Explorer

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
0 Karma
1 Solution

mayurr98
Super Champion

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!

View solution in original post

mayurr98
Super Champion

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!

SapthagiriAavik
Explorer

Thanks @mayurr98 , its working

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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")
0 Karma

SapthagiriAavik
Explorer

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

0 Karma

SapthagiriAavik
Explorer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

mayurr98
Super Champion

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

mayurr98
Super Champion

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.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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