VB.net powershell output contains list and need to return string to DGV-CodePudding

I'm new around here so take it easy on me... :)

I have a powershell output that looks like so:

UserPrincipalName                 DisplayName  Licenses                         
-----------------                 -----------  --------                         
[email protected] Brandon Duke {bdtestenv:O365_BUSINESS_PREMIUM}
[email protected]   Test2 2      {bdtestenv:O365_BUSINESS_PREMIUM}

And converted the output to a datasource with the following code:

 Dim runSpace As Runspace = RunspaceFactory.CreateRunspace()
    runSpace.Open()
   
    Dim powerShellCode As String = "Get-MsolUser -All | select UserPrincipalName, DisplayName, Licenses"               'reader.ReadToEnd()
    
    Dim shell = PowerShell.Create()
    shell.Commands.AddScript(powerShellCode)
    Dim results As Collection(Of PSObject) = shell.Invoke()
    Dim tempTable As DataTable = New DataTable()
    tempTable.Columns.Add("UserPrincipalName")
    tempTable.Columns.Add("DisplayName")
    tempTable.Columns.Add("Licenses")

    For Each psObject As PSObject In results
        Dim row As DataRow = tempTable.NewRow()
        row("UserPrincipalName") = psObject.Properties("UserPrincipalName").Value.ToString()
        row("DisplayName") = psObject.Properties("DisplayName").Value.ToString()
        row("Licenses") = psObject.Properties("Licenses").Value.ToString()

        tempTable.Rows.Add(row)
    Next
    Licenses.Guna2DataGridView1.DataSource = tempTable
    '  GridView1.DataSource = tempTable
    ' GridView1.DataBind()
    runSpace.Close()

Which works just fine but when I add it to the DataTable the licenses get returned as the following:

System.Collections.Generic.List'1[Microsoft.Online.Administration.UserLicense]

I'm needing to convert the list to a string so I can display it in the DataGridView..

Sorry please let me know if you need more information

Thanks ALL!

CodePudding user response：

One way to solve this problem is to modify your PowerShell command so as to ensure that all property values e returned as scalars (non-collections), which you can do with a calculated property:

Dim powerShellCode As String = "Get-MsolUser -All | select UserPrincipalName, DisplayName, @{ Name='Licenses'; Expression={ $_.Licenses.AccountSkuId -join ' ' } }"

$_.Licenses.AccountSkuId extracts the .AccountSkuId property values from all license objects contained in the collection returned by .Licenses and joins them with a space as the separator to form a single list.

(Note that what I tried first - $_.Licenses -join ' ' which is equivalent to "$($_.Licenses)" - is not enough, because the collection elements as a whole do not stringify meaningfully - they stringify by their type name only, namely Microsoft.Online.Administration.UserLicense)

To provide a simple example:

# A custom class that simulates the license class
class UserLicense {
  [string] $AccountSkuId
  UserLicense([string] $name) { $this.AccountSkuId = $name }
}

# Simulate an array of users returned from Get-MsolUser
$users = @([pscustomobject] @{
  UserPrincipalName = '[email protected]'
  DisplayName = 'Jane Doe'
  Licenses = [Collections.Generic.List[UserLicense]] ([UserLicense]::new('LicenseA'), [UserLicense]::new('LicenseB')) 
})

# Apply the Select-Object call with the calculated user, and
# convert it to CSV to demonstrate how the properties stringify.
$users | 
  Select-Object UserPrincipalName, DisplayName, @{ Name='Licenses'; Expression={ $_.Licenses.AccountSkuId -join ' ' } } |
    ConvertTo-Csv

Output, showing that the calculated Licenses property stringifies meaningfully:

"UserPrincipalName","DisplayName","Licenses"
"[email protected]","Jane Doe","LicenseA LicenseB"

If you had just used Select-Object UserPrincipalName, DisplayName, Licenses, the original problem would surface:

"UserPrincipalName","DisplayName","Licenses"
"[email protected]","Jane Doe","System.Collections.Generic.List`1[UserLicense]"

Also note that using Select-Object's -ExpandProperty parameter is not a general solution, as it results in multiple output objects per user object if the .Licenses collection happens to contain more than one license - unless this kind of denormalization is what you want:

# !! -ExpandProperty creates a separate output object for each license.
$users | 
  Select-Object UserPrincipalName, DisplayName -ExpandProperty Licenses | 
    ConvertTo-Csv

Output - two objects:

"UserPrincipalName","DisplayName","AccountSkuId"
"[email protected]","Jane Doe","LicenseA"
"[email protected]","Jane Doe","LicenseB"

Also note how the column headers changed: the reason is that, due to -ExpandProperty, each output object is now a [UserLicense] instance, decorated with the other two requested properties, UserPrincipalName and DisplayName. The [UserLicense] itself is represented by its one public property, AccountSkuId.

CodePudding user response：

I was able to fix my issue using -ExpandProperty

Thanks everyone!