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

[jhoang]

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"

[Murali2888]

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

[jhoang]

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"

[Murali2888]

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

[jhoang]

same results

[Murali2888]

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.

[jhoang]

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.

Intensive|blank
Intermediate|blank
Moderate|blank
Minimal|blank
Not assigned|blank

[Murali2888]

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.

[jhoang]

I was able to figure this out ty

[jhoang]

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

[Murali2888]

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

[jhoang]

should I be replacing ticketClosed - ticketCreated with:

Resolved Time - Created Time and

[pgreer_splunk]

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

[jhoang]

I was able to figure this out, ty

[masonmorales · Accepted solution]

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

[jhoang]

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?

[masonmorales]

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 

See also: http://docs.splunk.com/Documentation/Splunk/6.0.3/Search/Identifyandgroupeventsintotransactions#Tran...