Home› Analytics

Action Log queries

Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
edited September 2021 in Analytics

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'


Happy querying!

Comments

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Want the same but for a Service Request? Just change a table or two and...

    --Define the Service Request whose Action Log you want to grab
DECLARE @serviceRequestID nvarchar(10)
SET @serviceRequestID = 'SR3639'

--Get the Action Log
select sr.WorkItemId,
  al.Title,
  al.[Description],
  al.EnteredDate,
  'Task' as 'Entered By',
  'N/A' as 'IsPrivate'
from SM_WorkItem_ServiceRequest as sr
  inner join SM_WI_Relationship as alRel on sr.Id = alRel.SourceEntityId
    inner join SM_WorkItem_TroubleTicket_ActionLog as al on alRel.TargetEntityId = al.BaseManagedEntityId
where sr.WorkItemId = @serviceRequestID
  and alRel.TargetTypeId = 'dbb6a632-0a7e-cef8-1fc9-405d5cd4d911'

UNION ALL

-- Get Analyst Comments
select sr.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_ServiceRequest as sr
  inner join SM_WI_Relationship as acRel on sr.Id = acRel.SourceEntityId
    inner join SM_WorkItem_TroubleTicket_AnalystCommentLog as ac on acRel.TargetEntityId = ac.BaseManagedEntityId
where sr.WorkItemId = @serviceRequestID

UNION ALL

-- Get End User Comments
SELECT sr.WorkItemId,
  'End User Comment' as 'Title',
  eu.Comment as 'Description',
  eu.EnteredDate,
  eu.EnteredBy as 'Entered By',
  'false' as 'Private'
from SM_WorkItem_ServiceRequest as sr
  inner join SM_WI_Relationship as euRel on sr.Id = euRel.SourceEntityId
    inner join SM_WorkItem_TroubleTicket_UserCommentLog as eu on euRel.TargetEntityId = eu.BaseManagedEntityId
where sr.WorkItemId = @serviceRequestID

--Sort the whole thing by the common EnteredDate value
ORDER BY 'EnteredDate'
  • Simon_ZeinhoferSimon_Zeinhofer Customer Ninja IT Monkey ✭✭✭✭
    edited November 2021

    Amazing :)

    Would it be possible to transform this into a daily job, to sync all comments (so of every incident in the productive DB which has the "Closed" status for example)? The problem is, most of the time you don't know you'll need the comments before the incident gets deleted in the productive database ;)

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Analytics sync time is configured during portal setup. So yes...it's very possible! 😁

    value-png.png


  • Simon_ZeinhoferSimon_Zeinhofer Customer Ninja IT Monkey ✭✭✭✭
    https://community.cireson.com/discussion/comment/23500#Comment_23500

    Hello Adam,

    yes I know, but the Comment Sync is something you have to trigger by yourself right? Or is it an in built Job already?

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    It's a built in job, so no extra configuration needed!

Sign In or Register to comment.