Action Log queries

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