PSObject is returning system.object and not a string value

Question

I am trying to merge data from two different sources and export that into a CSV or txt file by making use of a PSObject.

$qry = New-Object System.Data.SqlClient.SqlCommand("SELECT Name, IsCompliant,LatestEntry,ComputerManufacturerName,ComputerModelName,OperatingSystemName,MachinesUsersNames,ErrorInfoName FROM ComplianceCore.MachinesComplianceView WHERE isCompliant='false'", $conn)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $qry
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null

$conn.Close


$resultObject = New-Object -TypeName psobject

foreach ($data in $dataset){
    $resultObject | Add-Member -MemberType NoteProperty -Name Name -Value $dataset.Tables.Name
    $resultObject | Add-Member -MemberType NoteProperty -Name ComputerManufacturer -Value $dataset.Tables.ComputerManufacturerName
    $resultObject | Add-Member -MemberType NoteProperty -Name OperatingSystemName -Value $dataset.Tables.OperatingSystemName
    $resultObject | Add-Member -MemberType NoteProperty -Name Tenant -Value (get-adcomputer -filter * -property CN | Where-Object { $_.Name -in $dataset.tables.name } | select-object CN)
}

$resultObject | Out-file -filePath "C:\BAC\WSUSScripts\MBAM Compliance\MBAMErrors.txt"

Also I'd like to run the query against the AD to retrieve some tenant information but no success.

The result is either when i export to csv a system.object or a value something like this {Microsoft Corporation, Dell Inc., Dell Inc., Dell Inc....} when i want everything to be formatted underneath each other in a table.

Guenther Schmitz · Accepted Answer · 2020-09-16 05:40:39Z

1

You have to create an array and add each data/result object as entry/row to the array.

$SQLServer = "SQL_SERVER"
$SQLDatabase = "SQL_DATABASE"
$conn = New-Object System.Data.SqlClient.SqlConnection("Server=$SQLServer;Database=$SQLDatabase;Integrated Security=True")
$qry = New-Object System.Data.SqlClient.SqlCommand("SELECT Name, IsCompliant,LatestEntry,ComputerManufacturerName,ComputerModelName,OperatingSystemName,MachinesUsersNames,ErrorInfoName FROM ComplianceCore.MachinesComplianceView WHERE isCompliant='false'", $conn)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $qry
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null

$conn.Close
    
$resultArray = foreach ($data in $dataset.Tables[0]){
    $resultObject = New-Object -TypeName psobject
    $resultObject | Add-Member -MemberType NoteProperty -Name Name -Value $data.Name
    $resultObject | Add-Member -MemberType NoteProperty -Name ComputerManufacturer -Value $data.ComputerManufacturerName
    $resultObject | Add-Member -MemberType NoteProperty -Name OperatingSystemName -Value $data.OperatingSystemName
    $resultObject | Add-Member -MemberType NoteProperty -Name Tenant -Value (get-adcomputer -filter * -property CN | Where-Object { $_.Name -in $data.name } | select-object CN)

    $resultObject
}

$resultArray | ConvertTo-CSV -NoTypeInformation | Out-file -filePath "C:\BAC\WSUSScripts\MBAM Compliance\MBAMErrors.txt"

edited Sep 16, 2020 at 5:40

answered Sep 15, 2020 at 9:01

Guenther Schmitz

2,0191 gold badge13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

claeys Over a year ago

Still returns system.object as the values

Guenther Schmitz Over a year ago

what does Write-Host $resultArray give you?

claeys Over a year ago

@{Name=System.Object[]; ComputerManufacturer=System.Object[]; OperatingSystemName=System.Object[]; Tenant=System.Object[]}

Guenther Schmitz Over a year ago

and whats the result of $dataset.Tables.Name | gm?

notjustme Over a year ago

$data instead of $dataset within the foreach?

|

Collectives™ on Stack Overflow

PSObject is returning system.object and not a string value

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related