2 Replies Latest reply on May 17, 2011 6:31 AM by RHSvof

    Extract extended data from tps_audit_trail

    RHSvof Rookie

      Hi, I want to extract some data from tps_audit_trail.


      I´ve found this scipt here Extracting data from the incident audit trail and modify it.


      SELECT DISTINCT T3.im_id as "Incident ID", T1.tps_creation_datetime as "Date/Time", T2.lc_title as "Event", tps_user.tps_title AS "Triggered By", tps_group.tps_title AS "Supporter Group", T4.im_full_name AS "Categorie" --, T5.tps_name AS "Creator"




      tps_audit_trail T1 left JOIN lc_action T2 on T1.tps_function_id = T2.lc_business_function_guid INNER JOIN im_incident T3 on T1.tps_object_guid = T3.pm_guid INNER JOIN tps_user on T1.tps_creation_user = tps_user.tps_guid  INNER JOIN tps_group ON T1.tps_create_group_guid = tps_group.tps_guid INNER JOIN im_incident_category AS T4 ON T3.im_category_guid = T4.im_guid


      WHERE T3.im_id = 1


      ORDER BY T3.im_id ASC, T1.tps_creation_datetime ASC


      So i get the Support User an his Group in clear text.


      But now i need the User who created the Incident.


      I´ve got the user with the following SQL query but in can´t integrate it


      SELECT     dbo.tps_user.tps_title
      FROM         dbo.tps_user INNER JOIN
                            dbo.pm_process INNER JOIN
                            dbo.tps_audit_trail INNER JOIN
                            dbo.im_incident ON dbo.tps_audit_trail.tps_object_guid = dbo.im_incident.pm_guid ON dbo.pm_process.pm_guid = dbo.im_incident.pm_guid                     ON dbo.tps_user.tps_guid = dbo.pm_process.pm_raise_user_guid


      Is there anybody out there, who can help me please?

        • 1. Re: Extract extended data from tps_audit_trail
          gramsay Specialist

          Something like this?



                                TOP (100) PERCENT T3.im_id AS [Incident ID], T1.tps_creation_datetime AS [Date/Time], T2.lc_title AS Event, dbo.tps_user.tps_title AS [Triggered By],
                                dbo.tps_group.tps_title AS [Supporter Group], T4.im_full_name AS Categorie, tps_user_1.tps_title AS [Raise User]

          FROM         dbo.pm_process INNER JOIN
                                dbo.tps_audit_trail AS T1 LEFT OUTER JOIN
                                dbo.lc_action AS T2 ON T1.tps_function_id = T2.lc_business_function_guid INNER JOIN
                                dbo.tps_user ON T1.tps_creation_user = dbo.tps_user.tps_guid INNER JOIN
                                dbo.tps_group ON T1.tps_create_group_guid = dbo.tps_group.tps_guid ON dbo.pm_process.pm_guid = T1.tps_object_guid INNER JOIN
                                dbo.im_incident_category AS T4 INNER JOIN
                                dbo.im_incident AS T3 ON T4.im_guid = T3.im_category_guid ON dbo.pm_process.pm_guid = T3.pm_guid INNER JOIN
                                dbo.tps_user AS tps_user_1 ON dbo.pm_process.pm_raise_user_guid = tps_user_1.tps_guid

          WHERE     (T3.im_id = 1)

          ORDER BY [Incident ID], [Date/Time]
          • 2. Re: Extract extended data from tps_audit_trail
            RHSvof Rookie

            Yes, thank you very much.


            I´ve tried something like this but I didn´t work .


            Your´s works fine.


            Ok now I have to try my best and find my mistake



            Thanks again and with kind regards