0

I am having a script which takes list of servers, goes through their registry and looks for registry values using wildcard. For each hostname it produces object, but it's properties vary. These objects are added into collection and then I export-csv this collection. I would love to have all the possible column headers there but it seems that I get always just those from the first object of the collection. What better way to do it if I don't know all the possible object properties in advance?

$res=@()
$hosts = Get-Content hosts.txt
foreach ($hostname in $hosts){
 $server = New-Object -TypeName psobject
 $server | Add-Member -MemberType NoteProperty -Name hostname -Value $hostname
 $server | Add-Member -MemberType NoteProperty -Name online -Value ""
 $server | Add-Member -MemberType NoteProperty -Name IP -Value ""
 $server | Add-Member -MemberType NoteProperty -Name OS -value ""
 $rtn=test-connection $hostname -Count 2 -BufferSize 16 -erroraction silentlycontinue
 if(!$rtn){
   $server.online=$false
   write-host "$hostname offline" -ForegroundColor red;
 }else{
   $server.online=$true
   $server.IP=(($rtn.properties|? name -eq "ProtocolAddress").value|select -first 1)

   $wmi=get-wmiobject -computer $hostname win32_operatingsystem -ErrorAction SilentlyContinue
   $server.OS=$wmi.caption

   $s=Invoke-Command -cn $hostname -ScriptBlock {
       Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\*|where {$_.displayname -like "*McAfee*"}|select displayname,displayversion    
   }
   $s|foreach{
       $server | Add-Member -MemberType NoteProperty -Name $_.displayname -Value $_.DisplayVersion
   }
 }
 $res+=$server
}
$res
$res|export-csv "versions.csv" -NoTypeInformation
notepad "versions.csv"

just test it for yourself with a few hostnames and search for something in registry preferably in different OSes and put there also some hostname which is unreachable.

an example output is here:

hostname     : server1
online       : True
IP           : secretip
OS           : Microsoft Windows Server 2008 R2 Standard 
McAfee Agent : 5.0.5.658

hostname                                            : server2
online                                              : True
IP                                                  : secretip
OS                                                  : Microsoft Windows Server 2016 Standard
McAfee Agent                                        : 5.0.5.658
McAfee Endpoint Security Threat Prevention          : 10.5.1
McAfee_EndpointSecurityForServer_10.5.1_0_x64_P0_EN : 10.5.1

hostname     : server3
online       : True
IP           : secretip
OS           : Microsoft Windows Server 2008 R2 Standard 
McAfee Agent : 5.0.5.658

hostname     : server4
online       : True
IP           : secretip
OS           : Microsoft Windows Server 2008 R2 Standard 
McAfee Agent : 5.0.5.658

hostname : server5
online   : False
IP       : 
OS       : 

hostname                                            : server6
online                                              : True
IP                                                  : secretip
OS                                                  : Microsoft Windows Server 2012 R2 Standard
McAfee Agent                                        : 5.0.5.658
McAfee Endpoint Security Threat Prevention          : 10.5.1
McAfee_EndpointSecurityForServer_10.5.1_0_x64_P0_EN : 10.5.1

hostname                                   : server7
online                                     : True
IP                                         : secretip
OS                                         : Microsoft Windows Server 2016 Standard
McAfee Agent                               : 5.0.5.658
McAfee Endpoint Security Threat Prevention : 10.5.1
McAfee Endpoint Security Platform          : 10.5.1

the resulting CSV from the above has only columns from the first object in the collection, but I want all the possible variants of those strings McAfee something

2 Answers 2

1

I suggest to:

  • save every servers data to a seperate csv file and then
  • first gather the column data and apply it to every csv with a Select-Object which
  • creates empty columns on files without that column and orders all columns equally

So instead of

$res+=$server

insert a

$server | Export-Csv "X:\path\$($server.Hostname).csv" -NoTypeInformation

and have this script do the unifying:

## Q:\Test\2019\01\18\sf_949737.ps1

$Columns = @('hostname','online','IP','OS')
$Files = (Get-ChildItem server*.csv)

## gather all columns/header
ForEach($File in $Files){
    ((Import-Csv $File)[0].psobject.Properties).Name | ForEach-Object{
        if($Columns -notcontains $_){$Columns += $_}
    }
}
"All columns`r`n-----------"
$Columns
"="*75
@() | Select-Object $Columns | Export-Csv Versions.csv -NoTypeInformation

ForEach($File in $Files){
    Import-Csv $File | Select-Object $Columns | Export-Csv Versions.csv -Append -NoTypeInformation
}

Import-Csv Versions.csv

Sample output based on your above data:

> Q:\Test\2019\01\18\sf_949737.ps1
All columns
-----------
hostname
online
IP
OS
McAfee Agent
McAfee_EndpointSecurityForServer_10.5.1_0_x64_P0_EN
McAfee Endpoint Security Threat Prevention
McAfee Endpoint Security Platform
===========================================================================


hostname                                            : server1
online                                              : True
IP                                                  : secretip
OS                                                  : Microsoft Windows Server 2008 R2 Standard
McAfee Agent                                        : 5.0.5.658
McAfee_EndpointSecurityForServer_10.5.1_0_x64_P0_EN :
McAfee Endpoint Security Threat Prevention          :
McAfee Endpoint Security Platform                   :

hostname                                            : server2
online                                              : True
IP                                                  : secretip
OS                                                  : Microsoft Windows Server 2016 Standard
McAfee Agent                                        : 5.0.5.658
McAfee_EndpointSecurityForServer_10.5.1_0_x64_P0_EN : 10.5.1
McAfee Endpoint Security Threat Prevention          : 10.5.1
McAfee Endpoint Security Platform                   :

...
0

Thanks for your solution, it is useful. Meanwhile I have found a shorter one without using other CSV files:

  $firstrow=$res|select -first 1
  $initObj=$firstrow
  $res|foreach{
      foreach($object_properties in $_.PsObject.Properties){
          if(!(Get-Member -inputobject $initobj -name $object_properties.Name -Membertype Properties)){
              write-host "adding property "$object_properties.Name
              $initObj | Add-Member -MemberType NoteProperty -Name $object_properties.Name -Value $firstrow.($object_properties.Name)
          }
      }
  }
  $resfinal=@()
  $resfinal+=$initobj
  foreach ($row in ($res| select -skip 1)){
      $resfinal+=$row
  }

This creates new object $initObj with values from the first row or $res, then goes through all objects in previously created $res array and checks if their property names are in $initobj. If not, they are added. Then I create new array $resfinal, add $initobj as a first element and then add all other elements from $res.

1
  • improved this solution not to produce empty first row
    – Vitas
    Jan 7, 2020 at 9:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .