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
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?