Home General Discussion

Query open ReviewActivities by user

Andreas_VoigtAndreas_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


Best Answer

  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Ninja IT Monkey ✭✭✭✭
    Accepted Answer
    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>&nbsp;&nbsp;&nbsp; # Create object for output<br>&nbsp;&nbsp;&nbsp; $output = New-Object -TypeName PSObject<br>&nbsp;&nbsp;&nbsp; Add-Member -InputObject $output -MemberType NoteProperty -Name RA -Value $RA.Id<br><br>&nbsp;&nbsp;&nbsp; # Create array of reviewer objects<br>&nbsp;&nbsp;&nbsp; $reviewers = @()<br>&nbsp;&nbsp;&nbsp; $reviewers = Get-SCSMRelatedObject -SMObject $RA -Relationship $hasReviewerRel<br><br>&nbsp;&nbsp;&nbsp; # Create array of users from each reviewer<br>&nbsp;&nbsp;&nbsp; $users = @()<br>&nbsp;&nbsp;&nbsp; foreach($reviewer in $reviewers)<br>&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $users += Get-SCSMRelatedObject -SMObject $reviewer -Relationship $reviewerRel<br>&nbsp;&nbsp;&nbsp; }<br><br>&nbsp;&nbsp;&nbsp; Add-Member -InputObject $output -MemberType NoteProperty -Name Reviewers -Value $users<br>&nbsp;&nbsp;&nbsp; $RAreviewers += $output<br>}<br></div>
    The script gets all active RAs, and builds an array of objects with RA ID and array of reviewers.

Answers

  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Ninja IT Monkey ✭✭✭✭
    Accepted Answer
    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>&nbsp;&nbsp;&nbsp; # Create object for output<br>&nbsp;&nbsp;&nbsp; $output = New-Object -TypeName PSObject<br>&nbsp;&nbsp;&nbsp; Add-Member -InputObject $output -MemberType NoteProperty -Name RA -Value $RA.Id<br><br>&nbsp;&nbsp;&nbsp; # Create array of reviewer objects<br>&nbsp;&nbsp;&nbsp; $reviewers = @()<br>&nbsp;&nbsp;&nbsp; $reviewers = Get-SCSMRelatedObject -SMObject $RA -Relationship $hasReviewerRel<br><br>&nbsp;&nbsp;&nbsp; # Create array of users from each reviewer<br>&nbsp;&nbsp;&nbsp; $users = @()<br>&nbsp;&nbsp;&nbsp; foreach($reviewer in $reviewers)<br>&nbsp;&nbsp;&nbsp; {<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $users += Get-SCSMRelatedObject -SMObject $reviewer -Relationship $reviewerRel<br>&nbsp;&nbsp;&nbsp; }<br><br>&nbsp;&nbsp;&nbsp; Add-Member -InputObject $output -MemberType NoteProperty -Name Reviewers -Value $users<br>&nbsp;&nbsp;&nbsp; $RAreviewers += $output<br>}<br></div>
    The script gets all active RAs, and builds an array of objects with RA ID and array of reviewers.
  • Andreas_VoigtAndreas_Voigt Customer IT Monkey ✭
    Thanks a lot, thats exactly what i needed. Working fine now
Sign In or Register to comment.