Query open ReviewActivities by user
Andreas_Voigt
Customer IT Monkey ✭
Hello there everyone,
I am struggeling with some users that dont finish their review-activitys on time. At the moment I have to dig through the "in progress-activiets" but im thinking about creating a query that gives me the users that have in-progress review activitys and so i can contact them directly.
Does anybody have a idea what path i should take ? WIll this be something with Powershell or more lile SQL ?
I would actually prefer Powershell.
Thanks in advance
Andreas
I am struggeling with some users that dont finish their review-activitys on time. At the moment I have to dig through the "in progress-activiets" but im thinking about creating a query that gives me the users that have in-progress review activitys and so i can contact them directly.
Does anybody have a idea what path i should take ? WIll this be something with Powershell or more lile SQL ?
I would actually prefer Powershell.
Thanks in advance
Andreas
0
Best Answer
-
Konstantin_Slavin-Bo
Customer Ninja IT Monkey ✭✭✭✭
Hi Andreas,If you have SMLets (if not, you should
), something like this should give you what you need:<div>ipmo smlets<br><br># Get classes<br>$RAclass = Get-SCSMClass -Name System.WorkItem.Activity.ReviewActivity<br>$hasReviewerRel = Get-SCSMRelationshipClass System.ReviewActivityHasReviewer<br>$reviewerRel = Get-SCSMRelationshipClass System.ReviewerIsUser<br><br># Get Active RAs<br>$RAs = Get-SCSMObject -Class $class -Filter "Status -eq 11fc3cef-15e5-bca4-dee0-9c1155ec8d83"<br><br># Declare output array<br>$RAreviewers = @()<br><br>foreach($RA in $RAs)<br>{<br> # Create object for output<br> $output = New-Object -TypeName PSObject<br> Add-Member -InputObject $output -MemberType NoteProperty -Name RA -Value $RA.Id<br><br> # Create array of reviewer objects<br> $reviewers = @()<br> $reviewers = Get-SCSMRelatedObject -SMObject $RA -Relationship $hasReviewerRel<br><br> # Create array of users from each reviewer<br> $users = @()<br> foreach($reviewer in $reviewers)<br> {<br> $users += Get-SCSMRelatedObject -SMObject $reviewer -Relationship $reviewerRel<br> }<br><br> Add-Member -InputObject $output -MemberType NoteProperty -Name Reviewers -Value $users<br> $RAreviewers += $output<br>}<br></div>The script gets all active RAs, and builds an array of objects with RA ID and array of reviewers.
1
Answers
<div>ipmo smlets<br><br># Get classes<br>$RAclass = Get-SCSMClass -Name System.WorkItem.Activity.ReviewActivity<br>$hasReviewerRel = Get-SCSMRelationshipClass System.ReviewActivityHasReviewer<br>$reviewerRel = Get-SCSMRelationshipClass System.ReviewerIsUser<br><br># Get Active RAs<br>$RAs = Get-SCSMObject -Class $class -Filter "Status -eq 11fc3cef-15e5-bca4-dee0-9c1155ec8d83"<br><br># Declare output array<br>$RAreviewers = @()<br><br>foreach($RA in $RAs)<br>{<br> # Create object for output<br> $output = New-Object -TypeName PSObject<br> Add-Member -InputObject $output -MemberType NoteProperty -Name RA -Value $RA.Id<br><br> # Create array of reviewer objects<br> $reviewers = @()<br> $reviewers = Get-SCSMRelatedObject -SMObject $RA -Relationship $hasReviewerRel<br><br> # Create array of users from each reviewer<br> $users = @()<br> foreach($reviewer in $reviewers)<br> {<br> $users += Get-SCSMRelatedObject -SMObject $reviewer -Relationship $reviewerRel<br> }<br><br> Add-Member -InputObject $output -MemberType NoteProperty -Name Reviewers -Value $users<br> $RAreviewers += $output<br>}<br></div>The script gets all active RAs, and builds an array of objects with RA ID and array of reviewers.