Extracting data from the incident audit trail

Version 3

    Verified Product Versions

    LANDESK Service Desk 7.6LANDESK Service Desk 7.7.xLANDESK Service Desk 7.8.xLANDESK Service Desk 2016.xLANDESK Asset Central 2016.xLANDESK Asset Central 2017.xLANDESK Service Desk 2017.x

    Environment

    Service Desk 7.1.3 upwards (not before)

    Question

    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?

    Answer

    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
    WHERE T3.im_id = 1 --UPDATE TO INCIDENT REF NUMBER
    ORDER BY T1.tps_creation_datetime ASC