Extracting data from the incident audit trail

Version 3

    Verified Product Versions

    Service Desk 7.6Service Desk 7.7.xService Desk 7.8.xService Desk 2016.xAsset Manager 2016.xAsset Manager 2017.xService Desk 2017.x


    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:

    Please note that this script will only show you all the Actions on the Incident. It will not show the Creation Date or any attributes set to be Auditable


    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
    ORDER BY T1.tps_creation_datetime ASC