topic Re: Calculate avg of time values in Splunk Search

Calculate avg of time values

neerajs_81 — Thu, 31 Jul 2025 08:39:25 GMT

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

Re: Calculate avg of time values

PrewinThomas — Thu, 31 Jul 2025 09:02:46 GMT

@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!

Re: Calculate avg of time values

ITWhisperer — Thu, 31 Jul 2025 09:27:31 GMT

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?

Re: Calculate avg of time values

neerajs_81 — Thu, 31 Jul 2025 12:51:37 GMT

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

Re: Calculate avg of time values

ITWhisperer — Thu, 31 Jul 2025 12:58:23 GMT

Try something like this

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

Re: Calculate avg of time values

neerajs_81 — Thu, 31 Jul 2025 13:07:20 GMT

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.

Re: Calculate avg of time values

PickleRick — Thu, 31 Jul 2025 13:29:39 GMT

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.

Re: Calculate avg of time values

ITWhisperer — Thu, 31 Jul 2025 14:09:50 GMT

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","")

Re: Calculate avg of time values

PrewinThomas — Fri, 01 Aug 2025 04:10:55 GMT

@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!

Re: Calculate avg of time values

neerajs_81 — Fri, 01 Aug 2025 05:32:15 GMT

Thank you. Giving your karma points