topic Re: How do I edit my search to calculate Ticket Resolution Time? in Splunk Search

How do I edit my search to calculate Ticket Resolution Time?

jhoang — Thu, 28 Jan 2016 22:59:06 GMT

Hi,

So currently I am pulling a report with all tickets that have been created this year. For the Ticket Resolution Time, I am trying to obtain how long it takes for a ticket to go from an open state to a "Request Status = Resolved or Closed" state. I want to break down all of these resolved & closed tickets based on their complexity to give me an average resolution time based on the 4 complexities (Intensive, intermediate, moderate, and minimal.) This can be 4 separate single value displays.

Currently I am stuck with the following search:

index=sdp Department="*" "Request Status"=Closed OR "Request Status"=Resolved Level="*" | rename Level as Complexity | eval _time=strptime('Start Time',"%Y-%m-%dT%H:%M:%S.%3N-%z") | timechart span=1d count by "Complexity"

Re: How do I edit my search to calculate Ticket Resolution Time?

masonmorales — Thu, 28 Jan 2016 23:28:43 GMT

Probably something like:

index=sdp Department="*" "Request Status"="*" Level="*" | rename Level as Complexity |transaction TicketNumber startswith="'Request Status'=Open*" endswith="('Request Status'=Resolved OR 'Request Status'=Closed)" | stats sum(duration) as TimeOpen by Complexity

You may have to tweak the single quotes/double quotes to get it working. I personally prefer field names with underscores in lieu of spaces, and then I just do a rename with spaces at the end. 😉

Re: How do I edit my search to calculate Ticket Resolution Time?

pgreer_splunk — Thu, 28 Jan 2016 23:30:46 GMT

I'm assuming you wish to determine the time between a ticket was first opened (or some other specific status from which it was in an initial or specific state until it reached the 'Resolved' or 'Closed' state) - correct?

Try combining the events for the ticket into a transaction that spans a beginning state to the end state of 'Resolved' or 'Closed'.

Here is an example of a answers question where the person looking for a solution that was resolved by creating a transaction between a start/end state within the data they were reporting on:

https://answers.splunk.com/answers/46503/calculate-time-difference-between-2-events.html

Re: How do I edit my search to calculate Ticket Resolution Time?

Murali2888 — Thu, 28 Jan 2016 23:37:49 GMT

Hi jhoang,

I assume Start Time as the time when the ticket was created and _time as the time when the ticket was closed or resolved.
Can you try the below query?

index=sdp Department="*" "Request Status"=Closed OR "Request Status"=Resolved Level="*" | rename Level as Complexity | eval ticketCreated=strptime('Start Time',"%Y-%m-%dT%H:%M:%S.%3N-%z") | eval ticketClosed=strptime('_time',"%Y-%m-%dT%H:%M:%S.%3N-%z") | eval ResolutionTime = ticketClosed - ticketCreated | stats avg(ResoultionTime) by Complexity

Re: How do I edit my search to calculate Ticket Resolution Time?

jhoang — Thu, 28 Jan 2016 23:40:12 GMT

Hey,

I tried the above query but it does not return any results. I am comfortable with the suggestion you provided in regards to the field names with underscores, how would the new query look instead?

What is the difference between the single and double quotes?

Re: How do I edit my search to calculate Ticket Resolution Time?

jhoang — Thu, 28 Jan 2016 23:42:36 GMT

Hi Murali,

so the query returned the results, however the avg(Resolution Time) is blank, and there is also a complexity field with the name not assigned.

When I try to pull the single value chart, I only receive the word "intensive"

Re: How do I edit my search to calculate Ticket Resolution Time?

Murali2888 — Thu, 28 Jan 2016 23:45:28 GMT

the rename command should be rename Level AS Complexity.
Can you try that?

Re: How do I edit my search to calculate Ticket Resolution Time?

jhoang — Thu, 28 Jan 2016 23:47:59 GMT

same results

Re: How do I edit my search to calculate Ticket Resolution Time?

jhoang — Thu, 28 Jan 2016 23:51:12 GMT

should I be replacing ticketClosed - ticketCreated with:

Resolved Time - Created Time and

Re: How do I edit my search to calculate Ticket Resolution Time?

Murali2888 — Thu, 28 Jan 2016 23:54:28 GMT

if you would like to have four different single value chart for each Complexity, then you would need to have four search queries. I think you cannot produce four single value charts with one query.

In this case, the single value chart is taking the first value.

Re: How do I edit my search to calculate Ticket Resolution Time?

jhoang — Fri, 29 Jan 2016 00:00:32 GMT

Okay that I under, so I would update the query

with the following?

by Complexity="Intensive"

The query you provided initially provided the correct breakdown, however there was just no information/ data for avg(ResolutionTime) for either of the complexity. That field was blank

ex.

Re: How do I edit my search to calculate Ticket Resolution Time?

masonmorales — Fri, 29 Jan 2016 00:52:59 GMT

Single quotes can be used to quote field names with spaces. I use them during an eval. e.g.
| eval MyField = 'some other field' will treat 'some other field' as an actual field name. Whereas, | eval MyField = "some other field" will set MyField to the literal string of "some other field"

I made up the TicketNumber field. I assume there is one in your events, or some other ID that you can key off of? Change it to that field name.

If you redo your field extractions with underscores, it would look something like this:

index=sdp Department="*" "Request Status"="*" Level="*" | rename Level as Complexity |transaction TicketNumber startswith=(Request Status=Open*) endswith=(Request_Status=Resolved OR Request_Status=Closed) | stats sum(duration) as TimeOpen by Complexity

Re: How do I edit my search to calculate Ticket Resolution Time?

Murali2888 — Fri, 29 Jan 2016 01:03:51 GMT

This would probably due to eval expression.

Two things you need to check.
1. The timeformat for both Start Time and _time are "%Y-%m-%dT%H:%M:%S.%3N-%z". If not please modify the format accordingly, so that the strptime can convert it into correct epoch time.
2. Remove single quotes in the eval expression as below.
| eval ticketCreated=strptime(StartTime,"%Y-%m-%dT%H:%M:%S.%3N-%z") | eval ticketClosed=strptime(_time,"%Y-%m-%dT%H:%M:%S.%3N-%z")

The time variable in the strptime must not be quoted. Apologies for multiple changes.

Re: How do I edit my search to calculate Ticket Resolution Time?

jhoang — Mon, 01 Feb 2016 23:12:15 GMT

Hi Murali,

So I was able to capture the resolution time based on Complexity. However when I run my current search command, it displays the value's in a weird method.

Is there a way to display the value in an easier format such as 2Days 8 Hours and 30 Minutes?

Search below:

index=sdp Department="*" "Request Status"=Closed OR "Request Status"=Resolved Level="Intensive" | rename Level as Complexity | eval ticketCreated=_time | eval ticketClosed=strptime('Resolved Time',"%b %d,%Y %I:%M %p") | eval Averagetime1=ticketClosed-ticketCreated | stats avg(Averagetime1) as timeVariable by Complexity | fieldformat timeVariable = tostring(timeVariable,"duration")

Value display is:
@+08:30:30.000000

Re: How do I edit my search to calculate Ticket Resolution Time?

Murali2888 — Tue, 02 Feb 2016 00:04:55 GMT

Hi Jhoang,

You can use the below

  | eval timeVariable=tostring(totalVariable,"duration") | eval TimeTaken = replace(timeVariable,"(\d*)\+(\d*)\:(\d+)\:(\d+)\.(\d+)","\1Days \2Hours \3Minutes \4Seconds")

Re: How do I edit my search to calculate Ticket Resolution Time?

jhoang — Tue, 02 Feb 2016 20:16:24 GMT

I was able to figure this out, ty

Re: How do I edit my search to calculate Ticket Resolution Time?

jhoang — Tue, 02 Feb 2016 20:17:04 GMT

I was able to figure this out ty