Action Log queries
The following query will let you grab the Action Log + Analyst + End User comments from an Incident. This requires the latest version of Analytics available with v11.2 of the portal.
--Define the Incident whose Action Log you want to grab DECLARE @incidentID nvarchar(10) SET @incidentID = 'IR1235' --Get the Action Log select ir.WorkItemId, al.Title, al.[Description], al.EnteredDate, 'Task' as 'Entered By', 'N/A' as 'IsPrivate' from SM_WorkItem_Incident as ir inner join SM_WI_Relationship as alRel on ir.Id = alRel.SourceEntityId inner join SM_WorkItem_TroubleTicket_ActionLog as al on alRel.TargetEntityId = al.BaseManagedEntityId where ir.WorkItemId = @incidentID and alRel.TargetTypeId = 'dbb6a632-0a7e-cef8-1fc9-405d5cd4d911' UNION ALL -- Get Analyst Comments select ir.WorkItemId, 'Analyst Comment' as 'Title', ac.Comment as 'Description', ac.EnteredDate, ac.EnteredBy as 'Entered By', CASE WHEN ac.IsPrivate = '0' THEN 'false' WHEN ac.IsPrivate = '1' THEN 'true' ELSE 'false' END AS 'Private' from SM_WorkItem_Incident as ir inner join SM_WI_Relationship as acRel on ir.Id = acRel.SourceEntityId inner join SM_WorkItem_TroubleTicket_AnalystCommentLog as ac on acRel.TargetEntityId = ac.BaseManagedEntityId where ir.WorkItemId = @incidentID UNION ALL -- Get End User Comments SELECT ir.WorkItemId, 'End User Comment' as 'Title', eu.Comment as 'Description', eu.EnteredDate, eu.EnteredBy as 'Entered By', 'false' as 'Private' from SM_WorkItem_Incident as ir inner join SM_WI_Relationship as euRel on ir.Id = euRel.SourceEntityId inner join SM_WorkItem_TroubleTicket_UserCommentLog as eu on euRel.TargetEntityId = eu.BaseManagedEntityId where ir.WorkItemId = @incidentID --Sort the whole thing by the common EnteredDate value ORDER BY 'EnteredDate'