Powershell Software Audit Output -csv format separated columns

Question

My current Powershell script spits out a .txt doc of the computer name, and software that I would like to be able to import into a csv file, with each computer name being a new column.

currently the output looks like this:

PC1=

productname

SoftwareA

SoftwareB

PC2=

productname

SoftwareA

SoftwareB

how can I script this to appropriately sort this data? a straight import to csv will have all of this info in a single column. Is there something I can throw on the foreach loop to have it write to the next column? Or could I have each loop write to it's own .txt, and then grab each .csv and have them import into a new sheet

here's the source code:

 $ComputerNames = get-content ".\Computers.txt"


foreach ($Computer in $ComputerNames)
 {$arryStandardSoftware = get-content -path ".\StandardSoftware.txt"| Foreach-Object{$_.trim()}
 $AuditResult = (Get-WMIObject -namespace "root\cimv2\sms" -class sms_installedsoftware -computername "$computer"|
               Select-Object productname|Where-Object{$arryStandardSoftware -notcontains "$($_.productname)"})
 echo "$Computer ="$AuditResult | out-file ".\SoftwareAudit.txt" -append}

Answer 1

The power of powershell is objects (PsCustomObject). In order to output each computer as a column, you can construct custom object and add new property to it, using computer name as property name (so long the computer name does not contain spaces or special characters). The following script should output something like this:

ProductName,  PC1,  PC2
SoftwareA,    true, false
SoftwareB,    false, true

Haven't tested it, but you should get the basic idea.

$ComputerNames = get-content ".\Computers.txt"
$arryStandardSoftware = get-content -path ".\StandardSoftware.txt"| Foreach-Object{$_.trim()}
$reports = $arryStandardSoftware | select @{N="ProductName";E={$_}}

foreach ($Computer in $ComputerNames)
{
    $installed = Get-WMIObject -namespace "root\cimv2\sms" -class sms_installedsoftware -computername "$computer" | select ProductName

    foreach ($r in $reports)
    {
        Add-Member -InputObject $r -MemberType NoteProperty -Name $Computer -Value ($installed -contains $r.ProductName)
    }
}

$reports | export-csv .\SoftwareAudit.txt -NoTypeInformation

Answer 2

What you want is an array of arrays.

ComputerA      ComputerB      ComputerC
SoftwareX      SoftwareX      SoftwareY
SoftwareY      SoftwareZ      SoftwareZ
SoftwareZ                     SoftwareA
                              SoftwareB

To get this result, you need to compile each array as you loop through the WMI results. Find the length of the longest array and then write out each row.

Here is a brute force approach to doing that:

$ComputerNames = get-content ".\Computers.txt"
$ComputerIndex = 0
$MasterArray = New-Object object[] $ComputerNames.Count

#collect the list in an array of arrays
foreach ($Computer in $ComputerNames) {
    $arryStandardSoftware = get-content -path ".\StandardSoftware.txt"| Foreach-Object{$_.trim()}
    $AuditResult = (Get-WMIObject -namespace "root\cimv2\sms" -class sms_installedsoftware -computername "$computer"|
               Select-Object productname|Where-Object{$arryStandardSoftware -notcontains "$($_.productname)"})  

    $SoftwareArray = @()
    $SoftwareArray += $Computer

    $AuditResult | % { $SoftwareArray += $_.productname }
    $MasterArray[$ComputerIndex] = $SoftwareArray
    $ComputerIndex += 1
 }

In the previous loop, an array is built for each computer. The first element is the computer name and the rest of the array is the list of software.

Now find out which of the arrays is the longest.

$longest = 0
for ($i=0;$i -lt $MasterArray.Count; $i++) {
    if ($MasterArray[$i].Count -gt $longest){
        $longest = $MasterArray[$i].Count
    }
}

Once we know the maximum column length, we can iterate through all the arrays, building the rows which will be output to the CSV file.

$MyOutput = $null

for ($i=0;$i -lt $longest; $i++) {
    $row = ""
    for ($j=0;$j -lt $MasterArray.Count; $j++) {
        if ($i -lt $MasterArray[$j].Count){
            $row += $MasterArray[$j][$i]
        }         
        if ($j -lt ($MasterArray.Count - 1) ){
            $row += "`t"
        }
    }    
   $MyOutput += ($row + "`r`n")
} 

$MyOutput > 'My.csv'

Like I said, this is a brute force approach, but the requirement to have each computer's software list as a column restricts the output options available.