How to extract PowerShell results to DataTable in vb.net

Question

I would like to extract the PowerShell output and extract the data and store in DataTable in VB.NET, I want to display the output in a GridView I need extract the column and value from the PowerShell output in VB.NET

Here is the PowerShell:

Get-ADComputer -Filter { OperatingSystem -NotLike '*Windows Server*'} -Property * | 
select Name, operatingSystem, LastLogonDate, Description, whenChanged | 
Where {($_.LastLogonDate -lt (Get-Date).AddDays(-90)) -and ($_.LastLogonDate -ne $NULL)}

Here is the PowerShell result:

Name            : PCO37
operatingSystem : Windows 7 Professional
LastLogonDate   : 1/13/2020 10:04:23 AM
Description     :
whenChanged     : 1/17/2020 1:22:08 PM

Name            : PCO41
operatingSystem : Windows 7 Professional
LastLogonDate   : 2/10/2019 4:43:31 PM
Description     :
whenChanged     : 2/10/2019 4:47:05 PM

Name            : PCO40
operatingSystem : Windows 7 Professional
LastLogonDate   : 4/8/2019 10:03:38 PM
Description     :
whenChanged     : 4/11/2019 6:29:25 AM

Name            : PCO09
operatingSystem : Windows 7 Professional
LastLogonDate   : 3/6/2019 8:04:59 AM
Description     :
whenChanged     : 3/6/2019 8:09:39 AM

Here is my code in VB.NET:

Function PSObjectToDataTable(ByVal command As String) As DataTable

    ' Initialize PowerShell engine
    Dim Ps As PowerShell = PowerShell.Create()

    ' add the script the PowerShell command
    Ps.Commands.AddScript(command)

    ' Execute the script
    Dim results = Ps.Invoke()

    Dim dt As DataTable = New DataTable()
    dt.Columns.Add("Detail")
    Dim Name As String
    Dim CanonicalName As String
    For Each psObject As PSObject In results
        dt.Rows.Add(psObject.ToString)
    Next

    Return dt

End Function

Here is the result of the VB.NET:

Detail
@{Name=PC037; operatingSystem=Windows 7 Professional; LastLogonDate=1/13/2020 10:04:23 AM; Description=; whenChanged=1/17/2020 1:22:08 PM}   
@{Name=PC041;  operatingSystem=Windows 7 Professional; LastLogonDate=2/10/2019 4:43:31 PM; Description=; whenChanged=2/10/2019 4:47:05 PM}   
@{Name=PC040; operatingSystem=Windows 7 Professional; LastLogonDate=4/8/2019 10:03:38 PM; Description=; whenChanged=4/11/2019 6:29:25 AM}    
@{Name=PC009;  operatingSystem=Windows 7 Professional; LastLogonDate=3/6/2019 8:04:59 AM; Description=; whenChanged=3/6/2019 8:09:39 AM}

If you're looking to convert an array of PSObject to .Net's DataTable type, take a look at `ConvertTo-DataTable`, defined here: https://powersnippets.com/convertto-datatable/. If you need to interact with VB.Net, please can you share the VB.Net code used to invoke PowerShell, or describe a bit more about the interaction between the two languages? — JohnLBevan, Apr 12 '20 at 09:00
You might also take a look at this idea: [`#11987` Extend **DataTable** with easy constructor and AddRow method](https://github.com/PowerShell/PowerShell/issues/11987) — iRon, Apr 12 '20 at 11:16
I need a code to extract the data from PowerShell output to VB.NET, i dont need the DataTable inside PowerShell — user1172579, Apr 12 '20 at 16:48

score 1 · Accepted Answer · answered Apr 12 '20 at 21:39

Finally i found the answer by myself.

To extract the properties from the PowerShell Object, i have to specific the item name, cannot use the index of the PowerShell Object property.

## Correct Syntax ##

psObject.Properties("Name").Value.ToString
psObject.Properties("CanonicalName").Value.ToString
psObject.Properties("operatingSystem").Value.ToString
psObject.Properties("LastLogonDate").Value.ToString
psObject.Properties("whenChanged").Value.ToString
psObject.Properties("Description").Value.ToString

## Wrong Syntax ##

psObject.Properties(0).Value.ToString
psObject.Properties(1).Value.ToString
psObject.Properties(2).Value.ToString
psObject.Properties(3).Value.ToString
psObject.Properties(4).Value.ToString
psObject.Properties(5).Value.ToString

Here is the code (dynamic create the column in Function: PSObjectToDataTable):

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim func As New clsFunction
    Dim command As String = "Get-ADComputer -Filter { OperatingSystem -NotLike '*Windows Server*'} -Property * | select Name, CanonicalName, operatingSystem, LastLogonDate, Description, whenChanged | Where {($_.LastLogonDate -lt (Get-Date).AddDays(-90)) -and ($_.LastLogonDate -ne $NULL)}"

    Dim arr As New ArrayList
    arr.Add("Name")
    arr.Add("CanonicalName")
    arr.Add("operatingSystem")
    arr.Add("LastLogonDate")
    arr.Add("whenChanged")
    arr.Add("Description")

    Me.GridView1.DataSource = func.PSObjectToDataTable(command, arr)
    Me.GridView1.DataBind()

End Sub

Function PSObjectToDataTable(ByVal command As String, ByVal arr As ArrayList) As DataTable

    ' Initialize PowerShell engine
    Dim Ps As PowerShell = PowerShell.Create()

    ' add the script the PowerShell command
    Ps.Commands.AddScript(command)

    ' Execute the script
    Dim results = Ps.Invoke()

    Dim dt As DataTable = New DataTable()
    Dim dr As DataRow = Nothing

    'Dynamic create cloumn
    For i = 0 To arr.Count - 1
        dt.Columns.Add(arr(i))
    Next

    For Each psObject As PSObject In results

        'Assign value to dynamic column
        dr = dt.NewRow()
        For i = 0 To arr.Count - 1

            'Check if object is null
            If psObject.Properties(arr(i)).Value Is Nothing Then
                dr(i) = ""
            Else
                dr(i) = psObject.Properties(arr(i)).Value.ToString
            End If

        Next
        dt.Rows.Add(dr)
    Next

    Return dt

End Function

i am working on a lib wrapping all this stuff for you. i will open source it in the next days. — Bhaal22, Jun 04 '20 at 23:58
@DarrenRose excellent reminder ! i had too much to do in the last months but I will work on it. It's almost there. — Bhaal22, Dec 30 '20 at 21:12
@Bhaal22 - excellent - look forward to hearing more in due course — Darren Rose, Jan 03 '21 at 19:34

score 0 · Answer 2 · answered Apr 02 '21 at 16:38

I figured I would shoot an example in here as well. I tried making it as dynamic as possible including field types. Int32 String etc.. Be gentle it is my first time posting in here.

Imports System.Management.Automation

Function PSObjectToDataTable(ByVal command As String) As DataTable

        ' Initialize PowerShell engine
        Dim Ps As PowerShell = PowerShell.Create()

        ' add the script the PowerShell command
        Ps.Commands.AddScript(command)

        ' Execute the script
        Dim results = Ps.Invoke()
        Dim dt As DataTable = New DataTable()
        Dim x As Integer = 0
        For Each psObject As PSObject In results
            If x = 0 Then
                For Each prop As PSPropertyInfo In psObject.Properties
                    dt.Columns.Add(prop.Name, prop.Value.GetType)
                Next
            End If
            Dim dtrow As DataRow
            dtrow = dt.NewRow
                For Each prop As PSPropertyInfo In psObject.Properties
                    Try
                        dtrow(prop.Name) = prop.Value
                    Catch ex As Exception
                        'do some debugging in here if you want to
                    End Try
                Next
                dt.Rows.Add(dtrow)
            x = x + 1
        Next
        Return dt

End Function

How to extract PowerShell results to DataTable in vb.net

2 Answers2