Export-csv options for dynamic sizes outputs

PowerShell Help
1

Hello everyone,

I’m looking to export in CSV a report of all Azure AD Access Package assignments with their questions and answers.
Now everything is static EXCEPT the Questions+Answers which vary from one access package to the other. can be any number between 0 and “many” :smiley:

My usual way of exporting to csv doesn’t work, please see the code below.
Could you please help me get a resolution please?

Code:

# 256-Color Foreground & Background Charts
 ##Change to beta profile##
Select-MgProfile -Name "beta"

##Import Modules##
Import-Module Microsoft.Graph.Identity.Governance

####   Define Variables ####

    # Array to be exported in csv
[System.Collections.ArrayList]$outarray = @()

    # Access Package ID
$accessPackageID = "5b0a0945-821a-4fbd-bf8b-82cf27df055e"

## Create CSV Headers



##Connect to MSGraph#
Connect-MgGraph -Scopes EntitlementManagement.Read.All


## Invokes the Graph API it will output as a JSON
##$x = Invoke-MgGraphRequest -Method GET -Uri "https://graph.microsoft.com/beta/identityGovernance/entitlementManagement/accessPackageAssignmentRequests?filter=accessPackage%2FId eq '$accessPackageID'"

$x = Invoke-MgGraphRequest -Method GET -Uri "https://graph.microsoft.com/beta/identityGovernance/entitlementManagement/accessPackageAssignmentRequests?filter=accessPackage%2FId eq '$accessPackageID'&expand=requestor"

    ##1st Question Text. Increment the 2nd Index to get all Questions Text. If NUll, No more questions left.
    ##$x.Value[0].answers.answeredQuestion[0].Text.defaultText
    $questions = $x.Value[0].answers.answeredQuestion.Text.defaultText
    $a = "Apple","Dog"
for($i=0; $i -le $x.value.Count; $i++){

    ## Create the Output hashtable
    $properties = @{
                        ##Requestor Name
                        Requestor = $x.value[$i].requestor.displayName

                        ##Requestor Email
                        Email = $x.value[$i].requestor.principalName

                        ##Time Request was created
                        CreatedDateTime = $x.Value[$i].createdDateTime

                        ## Time Request Started
                        StartDateTime = $x.Value[$i].schedule.startDateTime

                        ##Time Request Will End
                        EndDateTime = $x.Value[$i].schedule.expiration.endDateTime

                        ##Business Justification
                        Justification = $x.Value[$i].justification

                        ##Status of request
                        RequestStatus= $x.Value[$i].requestStatus ## Example FulfilledNotificationTriggered

                    }
    $Obj = New-Object -TypeName psobject -Property $properties
    $outarray.Add($obj) | Out-Null

} 

$outarray | export-csv -Path .\AccessPackage.csv -NoTypeInformation

##DisConnect from MSGraph#
Disconnect-Graph

##Get the Desired Access Package by name
#$accessPackage = Get-MgEntitlementManagementAccessPackage -DisplayNameEq "Mentimeter Application"


##Get Access Package Requests
## As a parameter write -Debug to see everything that is returned and some metadata
#$requests = Get-MgEntitlementManagementAccessPackageAssignmentRequest -Debug -AccessPackageId $accessPackage.Id 


##Get Access Package Assignments
#$assignments = Get-MgEntitlementManagementAccessPackageAssignment -AccessPackageId $accessPackage.Id
<#
##1st Question Text. Increment the 2nd Index to get all Questions Text. If NUll, No more questions left.
$x.Value[0].answers.answeredQuestion[0].Text.defaultText

##1st Question Answer
$x.Value[0].answers[0].value

##Time Request was created
$x.Value[0].createdDateTime

## Time Request Started
$x.Value[0].schedule.startDateTime

##Time Request Will End
$x.Value[0].schedule.expiration.endDateTime

##Business Justification
$x.Value[0].justification

##Status of request
$x.Value[0].requestStatus ## Example FulfilledNotificationTriggered
$x.Value[0].requestState ## Example  Delivered

##Requestor
$x.value[0].requestor

##Requestor Name
$x.value[0].requestor.displayName
#>
2

If some example output could be shared from X, there could be more assistance. Not sure why all of the indexes are necessary. From the looks of the parsing, may something like this sudo code:

$results = foreach ($val in $x.Values) {
    [pscustomobject]@{
         Email = $val.requestor.principalName
         CreatedDateTime = $val.createdDateTime
         StartDateTime = $val.schedule.startDateTime
    }
}

$results | Export-Csv...
3

Thank you for your response Rob,

How would your solution accommodate dynamically a varied number or “Questions”
This is how I’d do it “statically” if i know beforehand how many questions an access package has. but this means i need to edit the code for every single Access Package

# 256-Color Foreground & Background Charts
$results = foreach ($val in $x.Values) {
    [pscustomobject]@{
         Email = $val.requestor.principalName
         CreatedDateTime = $val.createdDateTime
         StartDateTime = $val.schedule.startDateTime
        Question0 = $val.answers[0].answeredQuestion.etc.etc
        Question1 = $val.answers[1].answeredQuestion.etc.etc
    }
}
4

It depends. If there a max number of questions, you could do what you doing there or do a for i loop 1-10. Say the first object has 2 questions and the next has 6 questions, you would build a dynamic object like this:

$obj = @()
$obj += [PSCustomObject]@{
    foo = 'foo'
    question1 = 'answer1'
    question2 = 'answer2'
}
$obj += [PSCustomObject]@{
    foo = 'foo'
    question1 = 'answer1'
    question2 = 'answer2'
    question3 = 'answer3'
    question4 = 'answer4'
    question5 = 'answer5'
    question6 = 'answer6'
}

But if you look at $obj, it will only have properties where both objects have values:

$obj

foo question1 question2
--- --------- ---------
foo answer1   answer2
foo answer1   answer2

if you go the dynamic route, you need to keep track of the highest question number (in the for i loop) to build a property list like this:

 $obj | Select foo, question1,question2,question3,question4,question5

foo       : foo
question1 : answer1
question2 : answer2
question3 : 
question4 : 
question5 : 

foo       : foo
question1 : answer1
question2 : answer2
question3 : answer3
question4 : answer4
question5 : answer5

dynamically:

#Captured in your for i as if ($i -gt $max) {$max = $i}
$max = 6
$questionProperties = @()
$questionProperties += 'foo'
$questionProperties += for($i=1;$i -le $max;$i++){
    'question{0}' -f $i
}

$obj | Select $questionProperties

If you know there is a max, you could have a consistent object schema with 10 questions and if $val.answers[4].answeredQuestion.etc.etc isn’t there, it would just be null and look like the first object result above.