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

Question

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

I have a powershell output that looks like so:

UserPrincipalName                 DisplayName  Licenses                         
-----------------                 -----------  --------                         
Brandon@bdtestenv.onmicrosoft.com Brandon Duke {bdtestenv:O365_BUSINESS_PREMIUM}
Test2@bdtestenv.onmicrosoft.com   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!

Answer 1

.Licenses contains a collection of license objects and not only does the collection as a whole not stringify meaningfully with .ToString() ("System.Collections.Generic.List'1[Microsoft.Online.Administration.UserLicense]"), neither do its elements ("Microsoft.Online.Administration.UserLicense").

Perhaps the simplest way to get a meaningful single-string representation of the .Licenses collection is to modify your PowerShell command to create such a representation with the help of a calculated property:

' Note the calculated property at the end:
' @{ Name='Licenses'; Expression={ $_.Licenses.AccountSkuId -join ' ' }
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 -join ' ' joins them with a space as the separator to form a list of values as a single string (adjust the separator string as needed).

.AccountSkuId is the property assumed to yield a meaningful representation of a given instance of .NET type [Microsoft.Online.Administration.UserLicense] (which doesn't appear to be documented).

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 = 'jdoe@example.org'
  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"
"jdoe@example.org","Jane Doe","LicenseA LicenseB"

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

"UserPrincipalName","DisplayName","Licenses"
"jdoe@example.org","Jane Doe","System.Collections.Generic.List`1[UserLicense]"

Also note that using Select-Object's -ExpandProperty parameter (as recommended in your own answer) 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, which now comprises two objects, one for each license:

"UserPrincipalName","DisplayName","AccountSkuId"
"jdoe@example.org","Jane Doe","LicenseA"
"jdoe@example.org","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] instance itself is represented by its one public property, AccountSkuId.

---

Optional reading: Why PowerShell's default output formatting resulted in a meaningful representation:

As shown in the screenshot in the question, outputting the PowerShell command to the PowerShell console did result in meaningful stringification of the .Licenses property values, courtesy of PowerShell's rich for-display output-formatting system:

For types other than [pscustomobject]^[1], PowerShell looks for a (public) property whose name is or ends in Id (irrespective of case) and then uses that property's value to represent the object as a whole.^[2] If no such property exists, the .ToString() return value is used.

The logic is that such a property is assumed to represent an ID, i.e. an identifying piece of information that is therefore a suitable representation of the object as a whole.

In the case at hand, PowerShell therefore implicitly represented the [Microsoft.Online.Administration.UserLicense] instances by their . AccountSkuId property values, which is what the code at the top does explicitly.

Here's a simplified example:

# Define a sample class.
class Foo {
  [int] $Foo = 1
  # This property's value will be used in the output formatting,
  # because it ends in "id"
  [string] $SomeId
  Foo([string] $id) { $this.SomeId = $id }
}

# Construct a custom object and output it to the console.
# Its .Collection property contains two [Foo] instances with
# distinct .SomeId values.
[pscustomobject] @{
      # Note: Showing the objects by the first "id"-suffixed property's values 
      #       would NOT be applied if the .Collection property were assigned as *scalar*.
Collection = [Foo]::new('Hi'), [Foo]::new('there')
}

This results in the following display output:

Collection
----------
{Hi, there}

Note the use of the .SomeId property values to represent the instances.
If no ID-like property existed, the [Foo] instances would be represented by their .ToString() value, which is just their type name, namely Foo.^[3]

---

^{[1] Instances of [pscustomobject] are represented with all their properties, using a hashtable-like representation, such as @{foo=bar; baz=quux} - see this answer for more information.}

^{[2] If multiple such properties exist, the first one, in definition order, is used.}

^{[3] The type names of PowerShell custom classes have no namespace component, unlike regular .NET types (e.g. Microsoft.Online.Administration.UserLicense). Returning just the full type name from .ToString() calls on instances of the type is the default behavior and applies to most types. However, certain types override the .ToString() method in order to provide a more meaningful representation; e.g., System.Text.RegularExpressions.Regex returns the text of the regular expression from .ToString().}

Answer 2

I was able to fix my issue using -ExpandProperty

Thanks everyone!