Splunk Search

## How to Calculate SLA of Different Teams for a Specific Ticket

Engager

Hi all,

We are trying to calculate SLA from Jira logs in our Splunk. What we want to achieve to calculate the time between Team field changes for a specific ticket. Our current and expected log results  are as below.

Current:

 Time Team Ticket No 09/12/2020 08:22 Level 3 Ticket 1 08/12/2020 06:08 Level 2 Ticket 1 08/12/2020 04:08 Level 1 Ticket 1 09/12/2020 16:22 Level 3 Ticket 2 08/12/2020 12:08 Level 2 Ticket 2 08/12/2020 10:08 Level 1 Ticket 2

Expected:

 Ticket No Transition Time Ticket 1 Level 1 to Level 2 2 hours Ticket 1 Level  2 to level 3 2 hours,14 mins Ticket 2 Level 1 to Level 2 3 hours Ticket 2 Level  2 to level 3 2 hours,20 mins

I hope I explained clearly. Any help is really appreciated, thank you!

Labels (5)

• ### transaction

1 Solution
SplunkTrust

The query was based on your sample data. I updated the query to process events only if Team field is different. It also calculates the time diff correctly even multiple updates between Team changes.

``````| sort "Ticket No" _time
| autoregress Team P=1
| where Team!=Team_p1 OR isnull(Team_p1)
| autoregress _time P=1
| autoregress Team P=1
| eval Time=tostring(_time-_time_p1,"duration")
| where isnotnull(Time) AND Team!=Team_p1
| eval Transition=Team_p1." to ".Team
| eval Time=replace(Time,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days, \2 hours,\3 mins")
| eval Time=replace(Time,"(\d+)\:(\d+)\:(\d+)","\1 hours,\2 mins")
| eval Time=replace(Time,",00\s(hours|mins)","")
| eval Time=replace(Time,"0(\d)\s","\1 ")
| table "Ticket No" Transition Time``````

If this solves your problem, upvote appreciated.

If this reply helps you an upvote and "Accept as Solution" is appreciated.
Ultra Champion

@gozdeyildizz Thats great news!
Don't forget to accept an answer and upvote posts that helped you!

If my comment helps, please give it a thumbs up!
Engager

Many thanks that worked! I have also added the command | where NOT LIKE(Team_p1,Team) to filter out  time calculations for the updates from the same team

SplunkTrust

You can use below query;

``````| sort "Ticket No" _time
| autoregress _time p=1
| autoregress Team P=1
| eval Transition=Team_p1." to ".Team
| eval Time=tostring(_time-_time_p1,"duration")
| where isnotnull(Time)
| eval Time=replace(Time,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days, \2 hours,\3 mins")
| eval Time=replace(Time,"(\d+)\:(\d+)\:(\d+)","\1 hours,\2 mins")
| eval Time=replace(Time,",00\s(hours|mins)","")
| eval Time=replace(Time,"0(\d)\s","\1 ")
| table "Ticket No" Transition Time``````

If this solves your problem, upvote appreciated.

If this reply helps you an upvote and "Accept as Solution" is appreciated.
Engager

Hi,

Thank you for your help. It solved partially but the problem is with my ticket logs because It does not have any field saying it is a log related to the team field change.  So SPL is calculating Time for every event for example commenting on the ticket or updating any value in the ticket. Therefore I am having the value of "L2 to L2 " for transition field with 0 minutes calculations. I am trying to find a workaround to dismiss those one. Any idea?

SplunkTrust

The query was based on your sample data. I updated the query to process events only if Team field is different. It also calculates the time diff correctly even multiple updates between Team changes.

``````| sort "Ticket No" _time
| autoregress Team P=1
| where Team!=Team_p1 OR isnull(Team_p1)
| autoregress _time P=1
| autoregress Team P=1
| eval Time=tostring(_time-_time_p1,"duration")
| where isnotnull(Time) AND Team!=Team_p1
| eval Transition=Team_p1." to ".Team
| eval Time=replace(Time,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days, \2 hours,\3 mins")
| eval Time=replace(Time,"(\d+)\:(\d+)\:(\d+)","\1 hours,\2 mins")
| eval Time=replace(Time,",00\s(hours|mins)","")
| eval Time=replace(Time,"0(\d)\s","\1 ")
| table "Ticket No" Transition Time``````

If this solves your problem, upvote appreciated.

If this reply helps you an upvote and "Accept as Solution" is appreciated.
Get Updates on the Splunk Community!

#### Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

#### New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

#### 2024 Splunk Career Impact Survey | Earn a \$20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...