7 Replies Latest reply on Oct 23, 2015 4:09 PM by mushoku

    Reporting SLA breaches by assignment

    DipeshLBH Apprentice

      Hi all,


      I was wondering if anyone has some insight they could share please on our reporting situation.


      Rather than the Service Desk owning Incidents and assigning out Tasks to a 2nd Line team for investigation, they will re-assign the Incident itself. We have no OLAs in place on our Incident lifecycle.


      So if the SLA breaches, we attribute 'blame' for that to the team that resolved the Incident. But this isn't always fair as the team that resolved may only get the Incident for a very small amount of time, before the SLA breaches.

      It may have been other teams that essentially were responsible for the breach.


      Does anyone else have the situation and how do you overcome it? I guess looking at the longest assignments is one way. Or, is the implementation of OLAs really the best way around it?



      Many thanks


        • 1. Re: Reporting SLA breaches by assignment

          Hi Dipesh,


          Do you want the clock to continue running when the Incident is assigned to the other teams?  I ask as the simplist way round this (and without changing your process too much) would be to stop the clock when the Incident is with the other team and re-start it when it is assigned back.


          If that's not acceptable then using Tasks or Escalations running during "with other group" statuses could be used for reporting purposes.  Of course, setting up something like that could involve a lot of Process design work (depending on how sophisticated you want it and how your process currently works) and extensive re-thinking of your current SLA's.





          • 2. Re: Reporting SLA breaches by assignment
            DipeshLBH Apprentice

            Hi Lara,


            Thanks for your reply - The escalation "with other group" status idea is an intresting one - is my understanding correct? :


            1) Service Desk would assign to Desktop Support, going from Status A from Status B and a new esclation would then start.

            2) Desktop Support can't fix the issue, they would assign back to the Service Desk to Status A.

            3) Service Desk could then assign to a different team in Status B

            4) But if Desktop Support could fix the issue as part of step 2, they would simply resolve.


            This would require the use of re-escalations ?


            As you say, this could be quite a lengthy exercise to implement


            Many thanks again

            • 3. Re: Reporting SLA breaches by assignment
              aparker Employee

              Just a quick thought on this...


              You can define assignment based escalations that allow you to have the main SLA running, but when a process is assigned a different escalation is activited that is only completed once the process is assigned to someone else.



              • 4. Re: Reporting SLA breaches by assignment

                How do you report on these incident assignments ? I don't see a IsBreached flag on the Incident - Assignment object

                • 5. Re: Reporting SLA breaches by assignment
                  mushoku Apprentice

                  So I've been pondering this for a while now and think I may have found a solution.


                  1) Drag Assignment onto Incident to create a relationship (I called this "Breached With") -- answer "No" to creating a collection

                  2) Add a Scheduled calculation to Breached With

                  import System
                  static def GetAttributeValue(Incident):
                      Value = null
                      if Incident.IsBreached and Incident.BreachTime != null:
                          Sorted = List(Incident.Assignments as Collections.IEnumerable)
                          Sorted.Sort() do (first, last):
                              return first.CreationDate.CompareTo(last.CreationDate)
                          for Assn in Sorted:
                              if Assn.CreationDate < Incident.BreachTime:
                                  Value = Assn
                      return Value

                  3) Create system query "Set Breached With" with criteria

                       - IsBreached = true

                       - AND BreachedWith.Group.Name = null

                       - AND BreachedWith.User.Name = null

                       - AND BreachedWith.Role.Name = null

                       - AND Status.IsReadOnly = false

                       - AND BreachTime != null

                  4) Create scheduled task associating the query (3) to the calculation (2) that goes off hourly (or whatever meets your needs)


                  This is really great in that it will calculate for incidents that are previously breached, and can be both queried and reported.  There are two downsides:

                  1) This will not update tickets in a read-only status (that criterion is there to prevent errors, not to limit the scope) EDIT - see my next reply for how to do this

                  2) It cannot account for any incidents where IsBreached == true yet BreachTime == null


                  You might be thinking, "How can an incident be IsBreached yet BreachTime be null?"  If so, we are in agreement; if not, please, tell me, how is this possible?  I have one incident like this and I cannot figure out how it happened, and I certainly can't think of a way to figure out where it was when it breached.


                  karenpeacock, dmshimself - you've both been involved with this topic on other threads, so tagging in case you are interested.

                  • 6. Re: Reporting SLA breaches by assignment

                    Appreciated and thanks. 

                    • 7. Re: Reporting SLA breaches by assignment
                      mushoku Apprentice

                      OK, I found a way to retroactively update incidents, just requires some SQL.  I closed out of the query after completing this, so had to re-create the SQL query and may not have gotten it 100% accurate, but here goes...


                      Oh, and, just so you really are aware, these instructions are provided as-is, and I take no responsibility for any adverse effects, explosions, or implosions that they may have or cause on your system, your sanity, and/or any other component within or without the world at large.  BACK UP YOUR DATABASE FIRST!!!  Also, test all of this in a test environment first.



                      0) Back up your database


                      1) Use a SQL query to find all of your pertinent data

                      select im_incident.pm_guid as 'inc_guid', im_incident.im_id as 'inc_id', im_incident.im_breach_time as 'breach time', im_incident_assignment.pm_guid as 'assn_guid', pm_process_assignment.pm_creation_date as 'assn_date'
                      from im_incident
                      join im_incident_assignment on im_incident.pm_guid = im_incident_assignment.im_incident_guid
                      join pm_process_assignment on im_incident_assignment.pm_guid = pm_process_assignment.pm_guid
                      where im_breach_time IS NOT NULL and usr_breachedwith IS NULL and im_is_breached = 1


                      2) Copy all of the output with headers to Excel (I love Excel)


                      3) Sort ascending by assn_date


                      4) Sort ascending by inc_id


                      -- It should now have all incidents grouped together, with the oldest assignment for each incident first

                      5) Add this formula to F2, then copy/paste to each row 3 through the last row:



                      6) Filter column F to only show TRUE results (if you do not filter, you will hate life)


                      7) Add to the first non-header row in column G, then copy/paste to each following row displayed:

                      update im_incident set usr_breachedwith = '


                      8) Add to column H:

                      ' where im_id =


                      9) Add to column I:



                      10) Copy all of column I and paste to a text file to ensure that they copy correctly


                      11) Manually confirm some number of update statements until you are satisfied that it will update the correct assignment as the "breachedwith" assignment


                      12) Test executing 1-3 of the manually confirmed update statements and confirm that they execute correctly and that the updated information is accurate


                      13) Execute the REMAINING update statements