System.Object in Excel export

I have a PS script which pulls configuration back from a NetApp cluster using REST. I’m then exporting config to an Excel spreadsheet. I’m having a modicum of success but my PS skills (or lack of PS skills) are just not enabling me to get over the final hurdle.

Here is a snippet from my script:

#------------------------------------------------------------------------------
# Export Policies
#------------------------------------------------------------------------------
# Set URI variable for retrieving properties
[String]$Uri = "https://$ClusterIP/api/protocols/nfs/export-policies?fields=**&return_records=true&return_timeout=15"
$WorkSheet = "Export_Policies"

Try{
    # Trigger REST API call and store in a variable
    $UriResponse = Invoke-RestMethod -Method GET -Uri $Uri -Headers $Headers -ErrorAction Stop

    # Select properties and format PSCustomObject which otherwise display 'System.Object[]' and store as a variable
    $ObjectProperties = $UriResponse.records | Select-Object -Property @{Name='export_policy';Expression={$_.name}},
                                                @{Name='svm';Expression={$_.svm.name}},
                                                @{Name='rules';Expression={[string]::join(";",($_.rules))}}

}
Catch{
                                                  
    Write-Warning -Message $("Failed enumerating properties from storage using URI ""$uri"". Error " + $_.Exception.Message + ". Status Code " + $_.Exception.Response.StatusCode.value__)
    Break;

}
                                                
# FUNCTION: Open Excel, add new worksheet and write out properties
Add-ExcelWorksheet

The results look like this in Excel:

I’d like to display all of the object contents in the Excel ‘rules’ column as per the below. Is this possible please?

PS C:\Windows\system32> $uriresponse.records.rules

index                 : 1
clients               : {@{match=0.0.0.0/0}}
protocols             : {nfs}
ro_rule               : {any}
rw_rule               : {any}
anonymous_user        : 65534
superuser             : {any}
allow_device_creation : True
ntfs_unix_security    : fail
chown_mode            : restricted
allow_suid            : True

Second issue, you can’t see in the image above however each of the cells in the ‘rules’ row has more than one rule (index=1,index=2, index=3, etc) . Is it possible to list the rules in separate roles and also relate them back to the relevant ‘export_policy’ and ‘svm’ cells? For example, something like the below (imagine an Excel worksheet asI can’t add more than one image into the post):

|export_policy |svm |rules|


|policy1 |svm1 |index 1, ……|
|policy1 |svm1 |index 2, ……|
|policy1 |svm1 |index 3, ……|
|policy2 |svm2 |index 1, ……|
|policy2 |svm2 |index 2, ……|

Thanks in advance

tao,
Welcome to the forum. :wave:t4:

Your code seems incomplete to me.

You need a nested loop to expand each single rule inside of a record.

If I got it right something like this should work as a start.

[String]$Uri = "https://$ClusterIP/api/protocols/nfs/export-policies?fields=**&return_records=true&return_timeout=15"
Try {
    $UriResponse = Invoke-RestMethod -Method GET -Uri $Uri -Headers $Headers -ErrorAction Stop
    $ObjectProperties = 
    foreach ($Record in $UriResponse.records) {
        $RuleList = $Record.rules
        foreach ($Rule in $RuleList) {
            [PSCustomObject]@{
                export_policy = $Record.name
                svm           = $Record.svm.name
                Index         = $Rule.Index
                Protokols     = $Rule.Protokols
                allow_suid    = $Rule.allow_suid
                #  ... and so on ... ;-)
            }
        }
    }
}
Catch {
    Write-Warning -Message $("Failed enumerating properties from storage using URI ""$uri"". Error " + $_.Exception.Message + ". Status Code " + $_.Exception.Response.StatusCode.value__)
    Break
}
                                                
$ObjectProperties

May I ask why you want to use Excel?

1 Like

Thank you so much Olaf. What you’ve suggested makes sense to me. I’ve amended the script:

Try{
    # Trigger REST API call and store in a variable
    $UriResponse = Invoke-RestMethod -Method GET -Uri $Uri -Headers $Headers -ErrorAction Stop

    # Select properties and format PSCustomObject which otherwise display 'System.Object[]' and store as a variable
    $ObjectProperties = 
    foreach ($Record in $UriResponse.records) {
        $RuleList = $Record.rules
        foreach ($Rule in $RuleList) {
            [PSCustomObject]@{
                svm = $Record.svm.name
                export_policy = $Record.name
                index = $Rule.index
                protocols = $Rule.protocols
                clients = $Rule.clients
                ro_rule = $Rule.ro_rule
                rw_rule = $Rule.rw_rule
                anonymous_user = $Rule.anonymous_user
                superuser = $Rule.superuser
                allow_device_creation = $Rule.allow_device_creation
                ntfs_unix_security = $Rule.ntfs_unix_security
                chown_mode = $Rule.chown_mode
                allow_suid = $Rule.allow_suid

            }
        }
    }
}

I now have a situation where I have System.Object in the information cells. What’s happening here and how can I display the information please?
image

Why Excel? I have multiple worksheets that contain different configuration items, so it seemed easier to display this in Excel. I would like to export everything to a single, nicely formatted HTML page, but I don’t think I’m there with the PS knowledge yet.

Thanks again

That usually indicates that you have an array instead of a single element. You can use another nested loop to separate the elements into individual rows or you join them with -join and display them in a single cell.

What I meant was if you’re using it only for display purposses it’s fine. I just would not recommend it as data storage for further steps.

That’s a little more complex but not impossible. :wink:

Olaf, that works and I’ve learnt something - thanks very much. Look out for my HTML report posts :grinning: