6 Replies Latest reply on May 18, 2011 5:37 AM by Stu McNeill

    Date IPC reached a certain Status?

    Julian Wigman ITSMMVPGroup

      Is it possible, via SQL query,  to determine when say an Incident reached a certain status?


      I want to believe that the audit trail may hold the key to this even if the normal audit trail tree doesnt show.


      I have an "Under Investigation" status in my Incident process.   I want to be able to report on the date diff between the Incident Create Date and the Date it entered this "Under Investigation" status. 



        • 1. Re: Date IPC reached a certain Status?
          karenpeacock SupportEmployee



          Is there a particular action that moves it to this status?  I think you've seen this article before (as you've commented on it) but I'd start by using this to extract all the rows in the audit trail for one particular incident and use this as the starting point.


          Best wishes


          • 2. Re: Date IPC reached a certain Status?
            Julian Wigman ITSMMVPGroup

            Hi Karen,


            "Been there done that" with the audit trail before I posted the message.


            The audit trail is very good it seems at telling you when and who executed an Action but not so good at timestamping a status change.    I can see some challenges as the process can enter the same status many times (via process loops) etc but again with creating querying (ie pick latest) you could potentially still get at what you want.   What's interesting though in the audit trail is that it does log the status but in what context;  the status the IPC was in when the action was pressed or is it the (destination) status that will result post action?


            It would be very good in reporting if we could report the status the IPC was in at any point in time.


            Maybe Service Desk doesnt allow that right now then.



            • 3. Re: Date IPC reached a certain Status?

              Isn't this one of the noted features of the MI Module? In terms of being able to see what status a call was at during a particular point, as apposed to what it currently is, which is what the standard reporting tools provide?


              I could be wrong, but I thought that was one of the supposed main benefits of MI, so perhaps that's worth looking into, although, I don't believe MI is supported in 7.4 until the next update as it doesn't appear to be widely used: http://www.landesk.com/products/management-information.aspx


              One line on that page does mention  'Perform trend analysis and forecasting based on historic data' but whether this drills down enough for you into specific enough time stamps as I'm not sure, as apposed to it simply logging what status the call was each day.

              • 4. Re: Date IPC reached a certain Status?
                Stu McNeill Employee

                Hi Julian,


                As you've seen already there is a status held with each audit trail item.  To clarify on its purpose its the destination status that gets stored.  Therefore It would be very easy to find the first time a status is entered:


                select top 1 tps_creation_datetime from tps_audit_trail where tps_status_guid = <something> order by tps_creation_datetime


                To get the latest time would be more difficult but if you know which action takes you to that status you can find the latest instance of that instead of relying on the status itself.

                • 5. Re: Date IPC reached a certain Status?
                  Julian Wigman ITSMMVPGroup



                  Are you sure on that SQL statement?


                  I would have said the SELECT and WHERE sections need to include the process GUID as well.  So lots of joins back from Audit to get at the ID/GUID.



                  • 6. Re: Date IPC reached a certain Status?
                    Stu McNeill Employee

                    Hi Julian,


                    You're correct that wouldn't be the full statement, it was just to illustrate the use of the "top 1 tps_creation_datetime", "order by..." and "where tps_status_guid = ..." components which are what would give you the date/time a status was entered.  You'll need to add whatever other conditions you'd like to find the particular IPC in question.