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