Splunk Search

How to Calculate SLA of Different Teams for a Specific Ticket

gozdeyildizz
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:

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

 

Expected:

Ticket NoTransitionTime
Ticket 1Level 1 to Level 22 hours
Ticket 1Level  2 to level 32 hours,14 mins
Ticket 2Level 1 to Level 23 hours
Ticket 2Level  2 to level 32 hours,20 mins

 

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

Labels (5)
0 Karma
1 Solution

scelikok
SplunkTrust
SplunkTrust

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.

 

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

View solution in original post

nickhills
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!
0 Karma

gozdeyildizz
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 

0 Karma

scelikok
SplunkTrust
SplunkTrust

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

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

gozdeyildizz
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? 

0 Karma

scelikok
SplunkTrust
SplunkTrust

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.

 

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

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...