topic Re: How to Calculate SLA of Different Teams for a Specific Ticket in Splunk Search

How to Calculate SLA of Different Teams for a Specific Ticket

gozdeyildizz — Thu, 17 Dec 2020 15:07:38 GMT

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!

Re: How to Calculate SLA of Different Teams for a Specific Ticket

scelikok — Thu, 17 Dec 2020 15:56:28 GMT

@gozdeyildizz,

You can use below query;

If this solves your problem, upvote appreciated.

Re: How to Calculate SLA of Different Teams for a Specific Ticket

gozdeyildizz — Fri, 18 Dec 2020 11:35:25 GMT

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?

Re: How to Calculate SLA of Different Teams for a Specific Ticket

scelikok — Fri, 18 Dec 2020 12:17:02 GMT

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.

If this solves your problem, upvote appreciated.

Re: How to Calculate SLA of Different Teams for a Specific Ticket

gozdeyildizz — Fri, 18 Dec 2020 13:44:26 GMT

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

Re: How to Calculate SLA of Different Teams for a Specific Ticket

nickhills — Fri, 18 Dec 2020 14:41:55 GMT

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