Query to determine which reviewers have voted on a Review Activity

Jonathan_Moore-Wrigh

Hi there, I'm trying to get a query working to help our CAB operations, to see which reviewers have not yet voted on a particular RA. I am finding this is a bit of a puzzle that is testing my pretty meagre SQL skills. I have been working on something based heavily on Brett Moffet's very helpful blog post that (I think) should surface the required data from the WorkItem and WorkItem$Review tables within the ServiceManagement db. But, it only appears to work some of the time and I'm not sure what I'm doing wrong.

This is the query I'm playing with:

---------

select WorkItemId  

, IIF(WorkItem.ClassId = 'bfd90aaa-80dd-0fbb-6eaf-65d92c1d8e36', ReviewerCIUser.DisplayName, WorkItem.AssignedUser) as AssignedUser

, ReviewObjects.DecisionDate 

, ReviewObjects.Comments

, ParentWorkItemId

, WorkItem.Id

from ServiceManagement.dbo.WorkItem 

 

outer apply (

select top 10 * from ServiceManagement.dbo.WorkItem$Review as ReviewObjects

where ReviewObjects.ReviewActivityId = WorkItem.Id 

and ReviewObjects.ReviewerId is not null

order by ReviewObjects.ReviewId Desc --newer reviewers are more likely to have a person, instead of the OOB blank entry.

) as ReviewObjects

left join ServiceManagement.dbo.CI$User as ReviewerCIUser on ReviewerCIUser.ID = ReviewObjects.ReviewerId

where ParentWorkItemId like '%cr781022%'

--------------

Looking in the Portal, a number of people have reviewed this RA:

but the query results have null entries:

I'd appreciate any insight you may have

thanks

-jmw

Peter_Muttenthaler

Hi @Jonathan_Moore-Wrigh

I'm doing some of these queries, but I'm using the table [ServiceManagement].[dbo].[Reviewer_WorkItem_CI$User] left joining it to the workitem table

probably the table Workitem$reviewer doesn't get synced well

you can try use to add the SCSM "MT_System$Reviewer" Table to the dynamic Data and get a table like "csnCached_MT_System_WorkItem_ServiceRequest"