8 Replies Latest reply on Jun 26, 2012 3:00 PM by Cris Coffey

    Question on SLA calculation



      We have been live on Trackit 10.5 for a few months now and all is going well. I use the change management module quite a bit to route approved tickets to our outsourcing group in India and then we use some of the SLA reports to measure the efficient of the tickets routed to them.Our tickets auto-calculate a due date which is then used as the basis for SLA review.

      One issue our India partners found unfair in relation to SLA was the fact that when they work on a ticket and then ask the user to review the work, they find the user/requestor may take a lot of time on this task hence keeping the ticket open longer than necessary and hence affecting the SLA stats. One method we said we would tyr to introduce to ensure the integrity of the SLA stats was to add a new 'status' option called 'Feedback'. So whenever our India group would require input from the user/requestor they would change the ticket status to 'Feedback' and when the user responded the ticket status would be changed back to 'Open'. In this way we would then  adjust the SLA stats to exclude the feedback time associated with the ticket and only measure our India partners on the time the ticket was 'in their court'.

      To update the SLA reports to exclude the feedback time I need to get access to the ticket audit trail information so I can extract the total feedback time associated with a particular ticket. (the audit trail will show every time the ticket status has changed from open to feedback and back to open again so I will be able to work out the total feedback time).

      Can someone tell me the name of the ticket audit trail table or whether there is a better approach to the problem.

      By the way...great product.


      Thanks and regards,


        • 1. Question on SLA calculation
          Chris McLane

          Hi Brian,


          Your dilemma is not an uncommon one. Unfortunately there is currently no built-in method for someone to "stop the clock" to account for delays that are out of the hands of the assigned technician. The time to complete a ticket is simply taken from the time it was opened until the time it was closed. A new status may work for you, but then again, it wouldn't be hard for the technicians to simply change the status to "Feedback" whenever they felt like it to help pad their stats. :-)


          At any rate, the information displayed under the Audit Trail tab of the work orders is actually taken from a few different tables and it isn't easy to extract the data.


          These are the tables that you would need to link together:



          DbChangeLogValue (linked to DbChangeLog by DBChangeLogID)

          DbChangeLogColumn (linked to DbChangeLogValue by DbChangeLogColumnID)


          The columns you may be the most interested in would be:


          DBChangeLog.PkValue (work order number)

          DBChangeLog.CreatedTime (date/time that a change was made)

          DBChangeLog.PrincipalName (technician that made the change)

          DBChangeLogColumn.TableName (you would want records associated with the TASKS table)

          DBChangeLogColumn.InsertUpdateDeleteCode (type of change that was made; insert, update, or delete)

          DBChangeLogValue.StringValueNew (new value that was set. For example, if the status is changed)


          Not entirely easy to put it all together but if you know how to code and run SQL queries maybe the information above will help get you going.

          • 2. Question on SLA calculation

            Hi Chris


            Thanks for that. Yes, I realise that the feedback option may be abused so am watching it closely.

            I am ok with SQL and creating a new Crystal report based on the table information supplied.

            Thanks very much for your help.




            • 3. Question on SLA calculation
              Chris McLane

              No problem. Hopefully that will allow you to extract the data that you want. That is something that many others do ask about.


              • 4. Question on SLA calculation
                Alejandro Araujo

                Just curious Chris: since this is, as you say, "something that many others do ask about", and clearly a much requested and needed function, are you able to say whether this feature is being seriously considered for inclusion any time soon?



                • 5. Question on SLA calculation
                  Chris McLane

                  Hi Alejandro,


                  Unfortunately I am not certain on the timeframe, but the good news is that it is indeed something that is being seriously considered for inclusion in a future release.

                  • 6. Question on SLA calculation
                    Cris Coffey

                    People ask for variances of this type of feature for different reasons. We seriously consider all feature requests and prioritize them based on the # of users who ask for them. Unfortunately, I cannot give a timeframe because the order of priority for items like this changes all the time.

                    • 7. Question on SLA calculation
                      Alejandro Araujo

                      Well, ok, I know you guys "seriously consider all feature requests" - it would be foolish not to listen to the users. But what I meant was "is there any way to tell whether this is up there in the priority count, and likely to be included in a near-future release, or is this something that just a couple of nit-pickers asked, and will be included sometime within the next 150 years?".  I am assuming that this is a "much requested and needed function", because I would love to see it, but there's no way to really know - other than trying to sneak the question to the development team! 

                      • 8. Question on SLA calculation
                        Cris Coffey

                        Unfortunately, we cannot give specifics. Being a public company, we cannot give specifics on release dates for features because it could affect revenue recognition of purchases. It is unfortunately very complicated.


                        It will definately be some time between now and 150 years from now though.