Calculate avg of time values

[neerajs_81]

Hello All,  Below is my dataset from a base query. How can i calculate the average value of the column ?

Incident	avg_time	days	hrs	minutes
P1	1 hour, 29 minutes	0.06181041204508532	1.4834498890820478	29.00699334492286
P2	1 hour, 18 minutes	0.05428940107829018	1.3029456258789642	18.176737552737862

I need to display the average of the avg_time values. Since there is date/time involved, merely doing the below function is not working

stats avg(avg_time) as average_ttc_overall

[PickleRick]

1. As @ITWhisperer noticed - you might be reinventing the wheel since probably the value comes from some earlier time-based data so there could be no need for rendering and parsing this value back and forth

2. Are you sure (you might be, just asking) that you want to calculate average of the averages? If the overall average is what you're seeking, an average of averages will not give you that.

[ITWhisperer]

It looks like all these times are based on a common (other unshown) field. Perhaps it would be helpful it you shared more information about how these current fields are calculated as there may be a different calculation you can do to get you nearer to your desired result?

[PrewinThomas]

@neerajs_81 

When it's formatted as strings like "1 hour, 29 minutes", you'll need to convert those time strings into a numeric format first(typically total minutes) before applying stats avg()

Eg:

| your_base_search
| eval time_parts=split(avg_time, ", ")
| eval hours=tonumber(replace(mvindex(time_parts, 0), " hour[s]?", ""))
| eval minutes=tonumber(replace(mvindex(time_parts, 1), " minute[s]?", ""))
| eval total_minutes=(hours * 60) + minutes
| stats avg(total_minutes) as average_min

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[neerajs_81]

Thanks a lot. I got the average_min as per your query but now how do i convert / represent it back to hours minutes format ?

| stats avg(total_minutes) as average_min

  For example your query gave me the average_min= 112.9  . How do i convert this back to show as 1 hour, 8 minutes 

[PrewinThomas · Accepted solution]

@neerajs_81 

You can use below for avg_mins and avg_hours.

| eval time_parts=split(avg_time, ", ")
| eval hours=tonumber(replace(mvindex(time_parts, 0), " hour[s]?", ""))
| eval minutes=tonumber(replace(mvindex(time_parts, 1), " minute[s]?", ""))
| eval total_minutes=(hours * 60) + minutes
| stats avg(total_minutes) as average_ttc_overall
| eval avg_hours=floor(average_ttc_overall / 60)
| eval avg_minutes=round(average_ttc_overall % 60)
| eval hour_avg=avg_hours . " hr " . avg_minutes . " mins" | table average_ttc_overall hour_avg

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[ITWhisperer]

Try something like this

| eval average_min=tostring(average_min,"duration")

[neerajs_81]

Thanks @ITWhisperer   Tried that as i found the same on your other posts. But this is what is displays the result as .  How to make it  show as 1h, 45 mins ?  More easily readable format in days, hours ,minutes.

[ITWhisperer]

Try something like this

| rex field=field_in_hhmmss "((?<days>\d+)\+)?((?<hours>\d+):)?((?<minutes>\d+):)?(?<seconds>[\d\.]+)"
| eval formatted=if(days > 0,days." days, ","").if(days > 0 OR hours > 0,hours." hours, ","").if(days > 0 OR hours > 0 OR minutes > 0,minutes." mins, ","").if(seconds > 0,seconds." secs","")

[neerajs_81]

Thank you. Giving your karma points