Splunk Search

Calculate average operation time

pranaynanda
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
0 Karma
1 Solution

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

View solution in original post

0 Karma

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

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

0 Karma

pranaynanda
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")
0 Karma

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

0 Karma

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

0 Karma

DalJeanis
SplunkTrust
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" 

pranaynanda
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" 
0 Karma

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

pranaynanda
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
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...