How to Calculate SLA of Different Teams for a Specific Ticket

[gozdeyildizz]

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!

[nickhills]

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

[gozdeyildizz]

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 

[scelikok]

@gozdeyildizz,

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.

[gozdeyildizz]

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? 

[scelikok · Accepted solution]

Hi @gozdeyildizz,

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.