Export to csv from multiple cmdlets

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Doug Morrison Doug Morrison 1 year, 9 months ago.

  • Author
    Posts
  • #20674
    Profile photo of Doug Morrison
    Doug Morrison
    Participant

    Hi,

    I need to run the two wmi queries shown below and output the result to a single csv. I have read about and tried to apply it to my own code but not quite there. The below will overwrite the csv every time it cycles around to a new PC and outputs 'System.object[]' under the column IP. At least error detection works!

    Anyone have any ideas?

    Thanks

     
    
    
        
           
    $computers = (Get-Content "C:\Powershell\Comps.txt")
                
              
        
        
        
        ForEach ($computer in $Computers) {
                    # Test connection to make sure computer is turned on / exists on domain.
        IF (Test-Connection -ComputerName $Computer -count 1 -quiet) {
    
        
    
                    # Query WMI on each machine to return Comp details and IP
                     
       $Comps=gwmi win32_computersystem -comp $computer | select USername,dnshostname,Manufacturer,model 
       $IPs= Gwmi win32_networkadapterconfiguration -Namespace root/cimv2 -comp $Computer | 
            where {$_.IPAddress} |select -expand ipaddress | where {$_ -like "192.128*"}
    
            foreach ($comp in $comps) {
                   $IP = $IPs | Where { $_.dnshostname -eq $comps.caption }
                $CompInfo = New-Object PSObject -Property @{
                    ComputerName = $Computer
                    Username = $Comps.Username
                    Manufacturer = $Comps.Manufacturer
                    Model = $Comps.model
                    IP = $IPs.ipaddress
                    
            }
               }
                    # Export to CSV
                        $CompInfo | export-csv C:\Powershell\Compstest.csv
                        
                          
                    # If the target system is unresponsive, return following to screen                          
                } ELSE {
                $obj = New-Object PSObject -Property @{
                        Computername = $Computer
                        IPAddress = 'Unreachable'
                       
                    } 
                        $obj   
                } 
            }
        
    
                
  • #20686
    Profile photo of Craig Duff
    Craig Duff
    Participant

    If you have powershell version 3, then Export-CSV has an -Append parameter.

    If you are stuck on powershell version 2, then you can redesign your loop to just output objects, no export csv, and assign the output of the loop to a variable. That variable, which holds all the exported objects, can then be exported

    $results = ForEach ($blah in $blahs) {
    
    }
    $results | Export-CSV
    
  • #20702
    Profile photo of Doug Morrison
    Doug Morrison
    Participant

    Hey Craig – thank you for the reply.

    Never heard of the append parameter, worked a treat thank you!

  • #22874
    Profile photo of William Parry
    William Parry
    Participant

    Did you fix your System.Object[] issue? I was looking for a solution to the same and was starting off with the following code:

    $FilePath = '\\servername\LoggedOn$\logon.txt'
    
    $date = Get-Date
    
    $LoggedOnInfo = "" | Select-Object User,Hostname,Date,Time,LogonServer,IPv4Address
    
    $LoggedOnInfo.User = "$env:userdomain\$env:username"
    $LoggedOnInfo.Hostname = hostname
    $LoggedOnInfo.Date = $date.ToShortDateString()
    $LoggedOnInfo.Time = $date.ToLongTimeString()
    $LoggedOnInfo.LogonServer = $env:LOGONSERVER
    $LoggedOnInfo.IPv4Address = Get-DnsClient | Where-Object {$_.ConnectionSpecificSuffix -eq "abc.example.com"} | Select-Object -Property InterfaceAlias | Get-NetIPAddress | where {$_.AddressFamily -eq "IPv4"} | Select-Object -ExpandProperty IPv4Address
    
    $LoggedOnInfo.User,$LoggedOnInfo.Hostname,$LoggedOnInfo.Date,$LoggedOnInfo.Time,$LoggedOnInfo.LogonServer,$LoggedOnInfo.IPv4Address -join "," | Out-File -FilePath $FilePath -Append
    }

    Moved some of the code to the beginning and put the rest into a foreach loop to iterate a possible problem with multiple IP addresses being returned:

    $FilePath = '\\servername\LoggedOn$\logon.txt'
    
    $date = Get-Date
    $interfaces = Get-DnsClient | Where-Object {$_.ConnectionSpecificSuffix -eq "abc.example.com"} | Select-Object -Property InterfaceAlias | Get-NetIPAddress
    
    foreach ($i in $interfaces) {
    if ($i.AddressFamily -eq "IPv4"){
    $LoggedOnInfo = "" | Select-Object User,Hostname,Date,Time,LogonServer,IPv4Address
    
    $LoggedOnInfo.User = "$env:userdomain\$env:username"
    $LoggedOnInfo.Hostname = hostname
    $LoggedOnInfo.Date = $date.ToShortDateString()
    $LoggedOnInfo.Time = $date.ToLongTimeString()
    $LoggedOnInfo.LogonServer = $env:LOGONSERVER
    $LoggedOnInfo.IPv4Address = $i | where {$_.AddressFamily -eq "IPv4"} | Select-Object -ExpandProperty IPv4Address
    
    $LoggedOnInfo.User,$LoggedOnInfo.Hostname,$LoggedOnInfo.Date,$LoggedOnInfo.Time,$LoggedOnInfo.LogonServer,$LoggedOnInfo.IPv4Address -join "," | Out-File -FilePath $FilePath -Append
    } else {}
    }

    It's still in testing, so I hope it works.

  • #22877
    Profile photo of Doug Morrison
    Doug Morrison
    Participant

    Hey William,

    I wasn't totally able to get rid of the System.Object[] message. We have three Comp OUs, and across 2 of them I get a 99% success rate with few of theses messages. For the last OU half of the returned lines have this message under the IP column.

    I just tried running your code for my own machine and it works fine although there is no returned logonserver. Are you seeing the System.Object[] message where you'd expect to see an IP address?

    Also, have you thought about exporting to CSV rather than a text file? Would be easier to read if you are querying multiple machines.

    Doug

You must be logged in to reply to this topic.