Cireson Analytics - get all work items and MAs

Ryan_Kelley

Hello,

I am trying to with a query that gets all MAs tied to SRs.

So far though I do not get all MAs - in some cases I only get two MAs although here are more - the goal would be to get all MAs:

select s.Id
   ,c.[WorkItemId]
      ,c.[Title]
,s.Title
   ,c.[Created]
      ,c.[AssignedUser]
      ,c.[Category]
         ,c.[PriorityId]
         ,s.Tier
,c.DisplayName 'Hostname'
   ,c.[ParentWorkItemId] as 'SRWorkItemID'
     ,s.[AssignedUser] 
      ,s.[Title]
   ,c.[ParentWorkItemType]
from dbo.SM_WorkItem_ServiceRequest S
join dbo.SM_WI_Relationship r
on r.SourceEntityId = s.Id
--and r.RelationshipTypeId = '2DA498BE-0485-B2B2-D520-6EBD1698E61B' --System.WorkItemRelatesToConfigItem
join dbo.SM_WorkItem_ManualActivity c
on c.Id = r.TargetEntityId

Ryan_Kelley

To provide more info - I would like to get all the MAs for an SR - however after some research I think the query above is not picking up the other MAs because they are under a PA -- So how do I tie them in like the query above?

Thank you for any assistance.

Adam_Dzyacky

All Manual Activities tied to Service Requests...

I think the first suggestion I have is coming at this from a different angle. Rather than Service Request to Manual Activity, do it backwards - Manual Activity to Service Request. The reason I suggest this is that from the Manual Activity table, you can find a column called "Parent Work Item Type"

Thereby enabling you a simple addition to a WHERE clause and yes - Parent Work Item ID is there as well so you can join back up to Service Requests 😁

Ryan_Kelley

To provide more info - I would like to get all the MAs for an SR - however after some research I think the query above is not picking up the other MAs because they are under a PA -- So how do I tie them in like the query above?

Thank you for any assistance.

Adam_Dzyacky

All Manual Activities tied to Service Requests...

I think the first suggestion I have is coming at this from a different angle. Rather than Service Request to Manual Activity, do it backwards - Manual Activity to Service Request. The reason I suggest this is that from the Manual Activity table, you can find a column called "Parent Work Item Type"

Thereby enabling you a simple addition to a WHERE clause and yes - Parent Work Item ID is there as well so you can join back up to Service Requests 😁

Ryan_Kelley

Perfect! Thank you for helping! That helped me-

also I used this which got me in the same direction as well in case someone comes across this question:

SELECT wi.WorkItemId, wi.Title,sr.[Created], wi.AssignedUser, wi.ParentWorkItemId PARENT_ID, sr.AffectedUser PARENT_AFFECTEDUSER 
FROM [WorkItem] wi LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
INNER JOIN [WORKITEM] sr ON sr.WorkItemId=wi.ParentWorkItemId