Service Desk 7.1.3 upwards (not before)
The audit trail of an incident logs all actions and is a useful tool for documenting actions such as call assignments, or a call being closed. There is currently no way to display this info other than in the audit trail pane, so where is the data?
The audit trail is stored in the 'tps_audit_trail' table. It contains a row for each entry, which includes these relevant columns:
'tps_object_guid' = The guid of the incident.
'tps_creation_datetime' = The date and time of the entry.
'tps_creation_user' = The guid of the user that caused the entry.
'tps_function_id' = The guid of the action (Stop Clock etc).
To translate the 'tps_function_id' into the name of the action, this is stored in the 'md_business_function' table, where the 'md_guid' matches the guid and 'lc_title' is the name (Stop Clock, Start Clock, etc).
The incident ID can be obtained from the 'im_incident' table where 'pm_guid' matches the 'tps_object_guid' from the audit trail table, and 'im_id' is the ID shown within Console.
A Crystal report can be created to show this information, or below is a quick SQL query to demonstrate it:
SELECT T3.im_id AS "Incident Id", T1.tps_creation_datetime AS "Date/Time", T2.md_title AS "Event", T4.tps_title AS "Triggered By" FROM tps_audit_trail T1 INNER JOIN md_business_function T2 ON T1.tps_function_id = T2.md_guid INNER JOIN im_incident T3 ON T1.tps_object_guid = T3.pm_guid INNER JOIN tps_user T4 ON T1.tps_creation_user = T4.tps_guid WHERE T3.im_id = 1 --UPDATE TO INCIDENT REF NUMBER ORDER BY T1.tps_creation_datetime ASC