Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Calculate average operation time

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

pranaynanda

Path Finder

08-09-2017
07:02 AM

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

1 Solution

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

08-09-2017
09:25 AM

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.

Highlighted
##

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Calculate average operation time

pranaynanda

Path Finder

08-10-2017
08:47 AM

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Calculate average operation time

DalJeanis

SplunkTrust

08-10-2017
09:23 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Calculate average operation time

pranaynanda

Path Finder

08-10-2017
08:12 PM

And what should I do when you say:

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Calculate average operation time

DalJeanis

SplunkTrust

08-11-2017
07:27 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Calculate average operation time

pranaynanda

Path Finder

08-14-2017
04:17 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Calculate average operation time

pranaynanda

Path Finder

08-14-2017
06:15 AM

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!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Calculate average operation time

pranaynanda

Path Finder

08-16-2017
12:29 AM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Calculate average operation time

pranaynanda

Path Finder

08-18-2017
03:01 AM

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