change request record - would like to see change request details + workflow activities status

Michael_Watson

hi, we use change request module in SCSM (using the cireson web portal).  in our implementation, every change request(CR)required a single approver before the change request is approved (by our CAB) before the implementer can proceed forward.  if we navigate to the "Activities" tab inside of the single CHange Request, we have three activities listed.  The first activity in creating the CR and submitted it for CAB review.  The 2nd activity is the CAB approving the CR.  the 3rd activity is for the implementer to complete the change request after they make their change.

I would like to see a report (in a single pane and inside of the cireson web portal) where I can see the change request and the 2nd activity status.  for example, have a list of records inside of the cireson portal that shows:
CR#,title,assigned to,change request status,activity #2 status,scheduled start time

Can someone help me so I can get the desired report/view of the change requests? THanks

Justin_Workman

@Michael_Watson - You should be able to create a SQL Table Widget on a dashboard page and use this query to get what your'e after:

SELECT cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'WorkItemID',

cr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'CR_Title',

assu.DisplayName as 'Assigned User',

statds.DisplayName AS 'CR_Status',

ra.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'Activity_ID',

aStatDS.DisplayName AS 'Activity_Status'

FROM MTV_System$WorkItem$ChangeRequest cr

JOIN Relationship assr

on assr.SourceEntityId = cr.BaseManagedEntityId

and assr.RelationshipTypeId = '15e577a3-6bf9-6713-4eac-ba5a5b7c4722' --Asigned User relationship

JOIN MT_System$Domain$User assu

on assu.BaseManagedEntityId = assr.TargetEntityId

JOIN DisplayStringView statDS

on statds.LTStringId = cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138

and statds.LanguageCode = 'enu'

JOIN Relationship pc

on pc.SourceEntityId = cr.BaseManagedEntityId

and pc.RelationshipTypeId = '2da498be-0485-b2b2-d520-6ebd1698e61b' -- Work Item contains Activity relationship

JOIN MTV_System$WorkItem$Activity$ReviewActivity ra

on pc.TargetEntityId = ra.BaseManagedEntityId

JOIN DisplayStringView aStatDS

on astatds.LTStringId = ra.Status_8895EC8D_2CBF_0D9D_E8EC_524DEFA00014

and aStatDS.LanguageCode = 'enu'

WHERE statds.DisplayName = 'In Progress'

AND ra.SequenceId_644EB7B4_745F_8E69_5695_833D7AFD28A0 = 1 --second activity in the list

Justin_Workman

@Michael_Watson - You should be able to create a SQL Table Widget on a dashboard page and use this query to get what your'e after:

SELECT cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'WorkItemID',

cr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'CR_Title',

assu.DisplayName as 'Assigned User',

statds.DisplayName AS 'CR_Status',

ra.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'Activity_ID',

aStatDS.DisplayName AS 'Activity_Status'

FROM MTV_System$WorkItem$ChangeRequest cr

JOIN Relationship assr

on assr.SourceEntityId = cr.BaseManagedEntityId

and assr.RelationshipTypeId = '15e577a3-6bf9-6713-4eac-ba5a5b7c4722' --Asigned User relationship

JOIN MT_System$Domain$User assu

on assu.BaseManagedEntityId = assr.TargetEntityId

JOIN DisplayStringView statDS

on statds.LTStringId = cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138

and statds.LanguageCode = 'enu'

JOIN Relationship pc

on pc.SourceEntityId = cr.BaseManagedEntityId

and pc.RelationshipTypeId = '2da498be-0485-b2b2-d520-6ebd1698e61b' -- Work Item contains Activity relationship

JOIN MTV_System$WorkItem$Activity$ReviewActivity ra

on pc.TargetEntityId = ra.BaseManagedEntityId

JOIN DisplayStringView aStatDS

on astatds.LTStringId = ra.Status_8895EC8D_2CBF_0D9D_E8EC_524DEFA00014

and aStatDS.LanguageCode = 'enu'

WHERE statds.DisplayName = 'In Progress'

AND ra.SequenceId_644EB7B4_745F_8E69_5695_833D7AFD28A0 = 1 --second activity in the list

Michael_Watson

Thank you for this query, This is helpful. In my original post.  One question, in my desired report format, I said "title" - this would be the Change Request title, not the Workflow Activity title, correct?

Justin_Workman

Yes.  The query is bringing back CR Title.  Do you want them both?

Michael_Watson

I just want to confirm it was the CR title - that is all.  Thank you very much for your SQL snippet.  I will give it a try on our test system.

Keren_Marroquin

Hi @Justin_Workman

This is helpful. How could I use this same code, but be able to see the activities within sequential activities?