Splunk Search

## Calculate average operation time

Path Finder

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
``````
Tags (4)
1 Solution
SplunkTrust

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.

Path Finder

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!

Path Finder

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.

Path Finder

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")
``````
SplunkTrust

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.

Path Finder

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.

SplunkTrust

@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"
``````
Path Finder

And what should I do when you say:

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

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

Path Finder

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
``````
Did you miss .conf21 Virtual?

### Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE! Catch Up Now >>

Get Updates on the Splunk Community!