›

Query open ReviewActivities by user

Andreas_VoigtAndreas_Voigt Customer IT Monkey ✭
in General Discussion
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:

    ipmo smlets

    # Get classes
    $RAclass = Get-SCSMClass -Name System.WorkItem.Activity.ReviewActivity
    $hasReviewerRel = Get-SCSMRelationshipClass System.ReviewActivityHasReviewer
    $reviewerRel = Get-SCSMRelationshipClass System.ReviewerIsUser

    # Get Active RAs
    $RAs = Get-SCSMObject -Class $class -Filter "Status -eq 11fc3cef-15e5-bca4-dee0-9c1155ec8d83"

    # Declare output array
    $RAreviewers = @()

    foreach($RA in $RAs)
    {
        # Create object for output
        $output = New-Object -TypeName PSObject
        Add-Member -InputObject $output -MemberType NoteProperty -Name RA -Value $RA.Id

        # Create array of reviewer objects
        $reviewers = @()
        $reviewers = Get-SCSMRelatedObject -SMObject $RA -Relationship $hasReviewerRel

        # Create array of users from each reviewer
        $users = @()
        foreach($reviewer in $reviewers)
        {
            $users += Get-SCSMRelatedObject -SMObject $reviewer -Relationship $reviewerRel
        }

        Add-Member -InputObject $output -MemberType NoteProperty -Name Reviewers -Value $users
        $RAreviewers += $output
    }
    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:

    ipmo smlets

    # Get classes
    $RAclass = Get-SCSMClass -Name System.WorkItem.Activity.ReviewActivity
    $hasReviewerRel = Get-SCSMRelationshipClass System.ReviewActivityHasReviewer
    $reviewerRel = Get-SCSMRelationshipClass System.ReviewerIsUser

    # Get Active RAs
    $RAs = Get-SCSMObject -Class $class -Filter "Status -eq 11fc3cef-15e5-bca4-dee0-9c1155ec8d83"

    # Declare output array
    $RAreviewers = @()

    foreach($RA in $RAs)
    {
        # Create object for output
        $output = New-Object -TypeName PSObject
        Add-Member -InputObject $output -MemberType NoteProperty -Name RA -Value $RA.Id

        # Create array of reviewer objects
        $reviewers = @()
        $reviewers = Get-SCSMRelatedObject -SMObject $RA -Relationship $hasReviewerRel

        # Create array of users from each reviewer
        $users = @()
        foreach($reviewer in $reviewers)
        {
            $users += Get-SCSMRelatedObject -SMObject $reviewer -Relationship $reviewerRel
        }

        Add-Member -InputObject $output -MemberType NoteProperty -Name Reviewers -Value $users
        $RAreviewers += $output
    }
    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.