topic Re: Calculate average operation time in Splunk Search

Calculate average operation time

pranaynanda — Wed, 09 Aug 2017 14:02:00 GMT

The gut who was doing this job before me made some servicenow reports using excel . He devised a term something that he says "Average process time" and I wish to calculate that. Average process time is said to be

The average process time is calculated by dividing the Total process time in a week by the respective amount of closed tickets in the same week. The total process time for each ticket in one week is calculated by subtracting the date of creating the ticket in tab Incidents column A from the date of resolving the ticket in tab Incidents column T. The amount of closed tickets is calculated by counting all tickets which have a resolved date in tab Incidents column T

I almost have what I need except for the count of tickets in the week.

this is what I have right now:

index=comos sourcetype=comos |where State in ("Closed","Resolved")|convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Created) mktime(Resolved) | eval tt=(Resolved-Created)|stats count(State="Resolved") as res|eval xx=(tt/res)|timechart xx span=1w

Re: Calculate average operation time

DalJeanis — Wed, 09 Aug 2017 16:25:27 GMT

1) Your stats command kills all fields it does not reference.

2) You don't really need to count the resolved records independently, because that will be all the records that have a duration. In fact, you probably already killed all the other records by filtering on the state, unless there is an occasional resolved record with no resolution date... which would be a bug in the source process.

3) If you want to calculate this across a number of weeks, then you need to include the resolve date as _time and bin it, then use it in stats or timechart.

 index=comos sourcetype=comos 
| where State in ("Closed","Resolved")
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Created) mktime(Resolved) 
| eval tt=(Resolved-Created)
| eval _time = relative_time(Resolved,"@w") 
| stats count as "Resolved and Closed Tickets" avg(tt) as "Average Process Time" by _time

...or replace the last two lines with...

| timechart span=1w count as "Resolved and Closed Tickets", avg(tt) as "Average Process Time"

In general, I prefer to bin and calculate things myself, but YMMV.

Re: Calculate average operation time

pranaynanda — Thu, 10 Aug 2017 15:47:45 GMT

I like what it gives me but how can I divide both the values as I get? Basically I want to derive what's there in the definition of the calculation.

Re: Calculate average operation time

DalJeanis — Thu, 10 Aug 2017 16:23:42 GMT

@pranaynanda - you were requested to calculate the average duration. avg(tt) is the average duration. The system has already divided the sum of process time by the number of process items.

You may want to divide it by either 3600 to get hours, or 86400 to get days.

| eval  "Average Process Time (Days)" = round( "Average Process Time" /86400,2)
| fields - "Average Process Time" 

| eval  "Average Process Time (Hours) " = round( "Average Process Time" /3600,2)
| fields - "Average Process Time"

Re: Calculate average operation time

pranaynanda — Fri, 11 Aug 2017 03:12:36 GMT

And what should I do when you say:

| rename COMMENT as "right here you should also eliminate any records closed in prior weeks"

Re: Calculate average operation time

DalJeanis — Fri, 11 Aug 2017 14:27:57 GMT

@pranaynanda - Wow, that's an OLD version, probably the first one that I posted. The current code, that has this line...

| eval _time = relative_time(Resolved,"@w")

... will handle multiple weeks without having to omit any.

Re: Calculate average operation time

pranaynanda — Mon, 14 Aug 2017 11:17:27 GMT

Can you also help with this last query? I have what I need. I only wish to hide the computer field xx from the Visualization.

index=comos sourcetype=comos 
 | where State in ("Closed","Resolved")
 | rename COMMENT as "right here you should also eliminate any records closed in prior weeks" 
 | rename "Resolving Time" as rt
 | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Created) mktime(Resolved) 
 | eval tt=(Resolved-Created)
 | eval _time = relative_time(Resolved,"@w")
 | timechart span=1w count as yy sum(rt) as xx 
 |eval zz= xx/yy

Re: Calculate average operation time

pranaynanda — Mon, 14 Aug 2017 13:15:47 GMT

Sorted. Got it. I did this:

index=comos sourcetype=comos 
 | where State in ("Closed","Resolved")
 | rename "Resolving Time" as rt
 | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Resolved) 
 | eval _time = relative_time(Resolved,"@w")
 |timechart span=1w count as "Number of Tickets" eval(round(avg(rt),2)) as "Average Operational Time"

I wish I could convert it to an answer.

Converted it for you and removed the old comment from the code, since it is no longer true.

Thanks for posting your final working code!

Re: Calculate average operation time

pranaynanda — Wed, 16 Aug 2017 07:29:53 GMT

One little addon that I wanted to do was to have the timechart but display the week numbers instead of dates.

I did this:

index=comos sourcetype=comos 
 | where State in ("Closed","Resolved")
 | rename "Resolving Time" as rt 
 | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Resolved)
 | eval _time=relative_time(Resolved,"@w")
 |eval  weeknumber=strftime(Resolved,"%U %Y")
 |timechart span=1w count as "Number of Tickets" eval(round(avg(rt),2)) as "Average Operational Time"  by weeknumber

but somehow the data is sorted as string and the sorting is all messed up.

Re: Calculate average operation time

pranaynanda — Fri, 18 Aug 2017 10:01:13 GMT

Okay, so I finally got what I wanted but now I want to use a time modifier and I can't wrap my head around those concepts. The date that it selects in the last value is from Thursday to Thursday. Instead I want it Monday to Monday. Please help:

index=comos sourcetype=comos 
 | where State in ("Closed","Resolved")
 | rename "Resolving Time" as rt 
 | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Resolved)
 | eval _time = relative_time(Resolved,"@w")
 |timechart span=1w count as "Number of Tickets" eval(round(avg(rt),2)) as "Average Operational Time" 
 |eval Time=strftime(_time, "%U %Y")