CODEWITHSUNDEEP
powershellcsv
Jeff
Jeff

Reputation: 3

Powershell - Multiple AD Commands, exporting into multiple columns csv

My goal is to take multiple PS commands, export the results into individual columns.There are no relations between the data. Currently I run 15-20 individual PS scripts that each output one column of data into their own csv file. I then take each of those 15-20 csv documents and copy/paste their individual columns into a new, single spreadsheet--into their own columns. I am trying to make this process more efficient by running one script that generates one csv file that contains multiple columns with their respected results.

Here is what my final output should look like:

Here is what my current output looks like

Here is my current rendition of the code. I have tried many variations although this is the closest I've been to a working product.

$ExportPath="\\NetworkLocation\test.csv"
$ADSG1="AD_Security_Group_Name"
$OUpath='OU=Servers,DC=sample,DC=sample,DC=org'

$TST_Script1=Get-ADGroupMember $ADSG1 | Select-object -ExpandProperty Name | Out-String
$TST_Script2=Get-ADComputer -Filter * -SearchBase $OUpath | Select-object -ExpandProperty Name | Out-String

$Object = [pscustomobject]@{
    TST_Script1 = $TST_Script1
    TST_Script2 = $TST_Script2
}
$Object | Export-Csv -Path $ExportPath -NoTypeInformation

Upvotes: 0

Views: 1045

Answers (1)

AdminOfThings
AdminOfThings

Reputation: 25031

You can do the following:

$TST_Script1=Get-ADGroupMember $ADSG1 | Select-object -ExpandProperty Name
$TST_Script2=Get-ADComputer -Filter * -SearchBase $OUpath | Select-object -ExpandProperty Name

0..(($TST_Script1.Count,$TST_Script2.Count | Sort -Desc)[0]-1) | Foreach-Object {
    [pscustomobject]@{
        TST_Script1 = $TST_Script1[$_]
        TST_Script2 = $TST_Script2[$_]
    }
} | ConvertTo-Csv -NoType

Export-Csv converts an input object's properties into column headers. The values of those properties are added as data rows aligning with the headers. When piping in a collection, each object in the collection is converted.

The issue with your attempt is you are outputting one object with two properties. Each of those properties then contains an array of values. That is different than outputting multiple objects with each having two properties and those properties contain singular values.

Out-String muddies things even more by taking an array output and converting it to a single string preserving the formatted array output.

As an aside, doing it the way you want does not make much sense to me. Both $TST_Script1 and $TST_Script2 seem disjointed as in they don't depend on each other and could contain different object types. Typically, you would want your data rows to represent objects that share schema or object type and contain properties that describe each of those objects. Often, data from different objects or commands is used to create properties for a current object. But the purpose is to better describe the object you are outputting. Your objective is to take two different, independent properties about two different objects and mash them together to appear as one object.

Upvotes: 1

Related Questions