Home Analytics

Cireson Analytics - get all work items and MAs

Ryan_KelleyRyan_Kelley Customer IT Monkey ✭
edited July 2022 in Analytics

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

Best Answers

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭
    Answer ✓

    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_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    Answer ✓

    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 😁

Answers

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭
    Answer ✓

    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_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    Answer ✓

    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_KelleyRyan_Kelley Customer IT Monkey ✭
    edited July 2022

    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 
    
Sign In or Register to comment.