Splunk Search

Group time duration by minute

pranaynanda
Path Finder

I have a set of data where I run this query:

 base search| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*)| eval total_time=((time_complete-time_initial)/60)| timechart avg(total_time) AS "Average Time to Complete" by Module

where I would like to group the values of field total_time in groups of 0-2 / 3-5 / 6-10 / 11-20 / > 20 and show the count in a timechart.

Please help.

0 Karma

cmerriman
Super Champion

i think your best bet is to use an eval:

base search| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*)| eval total_time=((time_complete-time_initial)/60)| timechart avg(total_time) AS total_time_avg by Module|eval "Average Time to Complete"=case(total_time_avg<=120,"0-2",total_time_avg<=300,"3-5",total_time_avg<=600,"6-10",total_time_avg<=1200,"11-20",1=1,">20")|fields - total_time_avg 

just understand that 3-5 is anything over 2 minutes up through 5 minutes, 6-10 is anything over 5 minutes up through 10 minutes, etc. though it can be adjusted accordingly.

0 Karma

pranaynanda
Path Finder

It does not solve. This is how it looks:

2017-08-27 >20 1.3433333333333333
2017-08-28 >20 0.9136054421768706
2017-08-29 >20 1.1056265984654732
2017-08-30 >20 1.1308823529411764

Every value out there is ">20"

0 Karma

cmerriman
Super Champion

sorry that was an oversight on my part. with using timechart, total_time_avg wouldn't be a column header, your Module values would be.

try this:

base search| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*)| eval total_time=((time_complete-time_initial)/60)| timechart avg(total_time) AS total_time_avg by Module|foreach * [eval <<FIELD>>=case('<<FIELD>>'<=120,"0-2",'<<FIELD>>'<=300,"3-5",'<<FIELD>>'<=600,"6-10",'<<FIELD>>'<=1200,"11-20",1=1,">20")]
0 Karma

pranaynanda
Path Finder

Sorry from my end too but there was a gap in description of the problem. I want to know the count of values that landed in these groups in a time frame. So if there's a trendline visualization, there should be 5 trendlines for each of these groups showing how many of these time averages landed in each group in that time frame.

0 Karma

cmerriman
Super Champion

alright, so that makes it more complicated. maybe something like this:

base search| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*) |bucket _time span=1d| eval total_time=((time_complete-time_initial)/60)|eventstats avg(total_time) as total_time_avg by _time Module|eval total_time_avg=case(total_time_avg<=120,"0-2",total_time_avg<=300,"3-5",total_time_avg<=600,"6-10",total_time_avg<=1200,"11-20",1=1,">20")| stats count by _time Module total_time_avg|eval {Module}=count|fields - Module count|stats values(*) as * by _time total_time_avg
0 Karma

pranaynanda
Path Finder

Thank you for your help. This solved my problem:

base search|convert timeformat="%Y-%m-%d %H:%M:%S" mktime(time*)|eval total_time=((time_complete-time_initial))|rename total_time as total_time_avg|eval "Average Time to Complete"=case(total_time_avg<=120,"0-2",total_time_avg<=300,"3-5",total_time_avg<=600,"6-10",total_time_avg<=1200,"11-20",1=1,">20")|fields - total_time_avg| timechart count by "Average Time to Complete"

I would convert this to an answer but I don't have the permissions.

0 Karma

pranaynanda
Path Finder

Someone please convert this comment to an answer!!!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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