none
Save to a CSV the Computer Name, IPv4 and Mac Address for all Computers in OU RRS feed

  • Question

  • I have tried half a dozen different approaches to this and all of them have different problems. I will post my most recent attempt which i can get to work to a text file but when i go to put it in a CSV with Convertto-CSV i get the following error:

    ConvertTo-Csv : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

    And if I use Export-CSV the csv file just gives me the file lengths.

    Here is the code for my most recent attempt (apologies for it being a mess):

    #connect to AD and get computer list
    $compList = Get-ADComputer -filter * -SearchBase "OU=Computers,DC=CONTOSO,DC=COM" | Select-Object -ExpandProperty name
    
    $CompleteList = @()
             #Loop Through Complist to get specified properties
     foreach ($Computer in $complist) {            
      #test to see Computer Is Online
      if(Test-Connection -ComputerName $Computer -Count 1 -ea 0 -Quiet) {            
       try {            
        $Networks = Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $Computer -EA Stop | ? {$_.IPEnabled}            
       } catch {            
            Write-Warning "Error occurred while querying $computer."            
            Continue            
       }            
       foreach ($Network in $Networks) {            
        $IPAddress  = $Network.IpAddress[0]            
        $SubnetMask  = $Network.IPSubnet[0]            
        $DefaultGateway = $Network.DefaultIPGateway            
        $DNSServers  = $Network.DNSServerSearchOrder            
        $MACAddress  = $Network.MACAddress            
        $CompleteList += $Computer, $IPAddress, $MACAddress          
       }            
      }            
     }            
     #Save Complete List to CSV (WELL CURRENTLY TXT)
    $CompleteList | Out-File "C:\Users\GenericUser\Desktop\MacAndIP-2.txt"

    I have other attempts at this that ran into other problems but in at least a couple scripts to accomplish this i run into the same problem with not being able to Convertto-CSV as I get the error listed above. I know that some CMDLETs cannot be used to Pipe information but there has to be a way to get around it.

    Thanks in advance to those far better at this than I.


    Phil

    Wednesday, March 16, 2016 9:05 PM

Answers

  • Similar to jrv, here is another example of a way to do it:


    $params = @{
      "Filter" = "*"
      "SearchBase" = "OU=Computers,DC=fabrikam,DC=com"
      "SearchScope" = "Subtree"
    }
    $computers = Get-ADComputer @params
    foreach ( $computer in $computers ) {
      if ( (Test-Connection $computer.Name -Count 1 -Quiet) ) {
        $params = @{
          "Class" = "Win32_NetworkAdapterConfiguration"
          "ComputerName" = $computer.Name
          "Filter" = "IPEnabled=True"
        }
        Get-WmiObject @params | ForEach-Object {
          $ipAddresses = $_.IPAddress
          for ( $i = 0; $i -lt $ipAddresses.Count; $i++ ) {
            if ( $ipAddresses[$i] -match '(\d{1,3}\.){3}\d{1,3}' ) {
              $_ | Select-Object `
                @{Name="ComputerName";         Expression={$computer.Name}},
                @{Name="IPAddress";            Expression={$ipAddresses[$i]}},
                @{Name="IPSubnet";             Expression={$_.IPSubnet[$i]}},
                @{Name="DefaultIPGateway";     Expression={$_.DefaultIPGateway[$i]}},
                @{Name="DNSServerSearchOrder"; Expression={$_.DNSServerSearchOrder -join ';'}},
                MACAddress
            }
          }
        } | Export-Csv "C:\Temp\MyOutput.csv" -NoTypeInformation
      }
    }
    


    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by Piook Thursday, March 17, 2016 3:21 PM
    Wednesday, March 16, 2016 9:39 PM
    Moderator

All replies

  • Start small.  Learn how each piece works.  Guessing will only get you in trouble.

    Start with a simple example and work up from there:

    $properties=@(
    	@{n='IPaddress';e={$_.IpAddress[0]}},           
    	@{n='SubnetMask';e={$_.IPSubnet[0]}},          
    	'DefaultIPGateway',
    	@{n='DNSServers';e={$_.DNSServerSearchOrder}},       
    	'MACAddress'
    )
    $computers|%{
    	Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $_
    	}|
    	Select $properties


    \_(ツ)_/

    Wednesday, March 16, 2016 9:27 PM
  • Similar to jrv, here is another example of a way to do it:


    $params = @{
      "Filter" = "*"
      "SearchBase" = "OU=Computers,DC=fabrikam,DC=com"
      "SearchScope" = "Subtree"
    }
    $computers = Get-ADComputer @params
    foreach ( $computer in $computers ) {
      if ( (Test-Connection $computer.Name -Count 1 -Quiet) ) {
        $params = @{
          "Class" = "Win32_NetworkAdapterConfiguration"
          "ComputerName" = $computer.Name
          "Filter" = "IPEnabled=True"
        }
        Get-WmiObject @params | ForEach-Object {
          $ipAddresses = $_.IPAddress
          for ( $i = 0; $i -lt $ipAddresses.Count; $i++ ) {
            if ( $ipAddresses[$i] -match '(\d{1,3}\.){3}\d{1,3}' ) {
              $_ | Select-Object `
                @{Name="ComputerName";         Expression={$computer.Name}},
                @{Name="IPAddress";            Expression={$ipAddresses[$i]}},
                @{Name="IPSubnet";             Expression={$_.IPSubnet[$i]}},
                @{Name="DefaultIPGateway";     Expression={$_.DefaultIPGateway[$i]}},
                @{Name="DNSServerSearchOrder"; Expression={$_.DNSServerSearchOrder -join ';'}},
                MACAddress
            }
          }
        } | Export-Csv "C:\Temp\MyOutput.csv" -NoTypeInformation
      }
    }
    


    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by Piook Thursday, March 17, 2016 3:21 PM
    Wednesday, March 16, 2016 9:39 PM
    Moderator
  • I have done that and I can build in parts. Which is how I have gotten to where i am at.

    But I have ran into a wall with the csv part.

    Thank you though for the advice as it reaffirms that I am going about it for a while.


    Phil

    Wednesday, March 16, 2016 9:46 PM
  • Yes - a complete solution.  I wanted to focus on how to build a solution. This is a good learning opportunity.  To get from a flat pipeline to a logged and  protected operation is a worthwhile and very instructive exercise.

    Anyway -Good luck.


    \_(ツ)_/

    Wednesday, March 16, 2016 9:48 PM
  • Thank you Bill!

    I thought...

    This is working beautifully. All i needed was to modify the parameters section and where the file saves to and it works...

    But it seems to only be pulling one record or overwriting the record each time, because the CSV only has the one computer and its info listed in it? I have tried some things to see if i can fix that but so far to no avail. I am really not sure what is happening. Any further help as to why this is only writing 1 computer and its info to the csv would be appreciated.

    Can you tell me why yours is able to use Convertto-CSV and mine was not? I think it is a matter of the fact that I was dumping the needed info straight into a variable and then trying to pipe that variable into the Convertto-CSV cmdlet. Where as you get the info and pipe it into the cmdlet directly. That and the use of the For-Each in getting the info.

    Can you tell me what this code of yours is doing?

     Get-WmiObject @params | ForEach-Object {
          $ipAddresses = $_.IPAddress
          for ( $i = 0; $i -lt $ipAddresses.Count; $i++ ) {
            if ( $ipAddresses[$i] -match '(\d{1,3}\.){3}\d{1,3}' ) {
              $_ | Select-Object `
                @{Name="ComputerName";         Expression={$computer.Name}},
                @{Name="IPAddress";            Expression={$ipAddresses[$i]}},
                @{Name="IPSubnet";             Expression={$_.IPSubnet[$i]}},
                @{Name="DefaultIPGateway";     Expression={$_.DefaultIPGateway[$i]}},
                @{Name="DNSServerSearchOrder"; Expression={$_.DNSServerSearchOrder -join ';'}},
                MACAddress

    Primarily the line with "$i=0;..."  and the use of "@...      Expression=...:"

    That would be really helpful.

    Thanks again for all the help!


    Phil


    • Edited by Piook Thursday, March 17, 2016 5:19 PM Found Out not working quite right
    Thursday, March 17, 2016 4:04 PM
  • I found a partial fix by just adding -append to the Convertto-CSV line. which confirms it was overwriting what was being written there.

    If you have another fix for this issue then great. If not then no worries.

    Thanks to all of you. Bill and jrv!


    Phil

    Thursday, March 17, 2016 5:24 PM
  • To fix:

    1. Remove the " | Export-Csv ..." part completely. (Left that in there by accident.)

    2. Save the lines to a .ps1 file; for example: C:\Scripts\Report.ps1

    3. Run the script at the prompt and redirect its output to a CSV file; for example:


    PS C:\> C:\Scripts\Report.ps1 | Export-Csv C:\Scripts\Report.csv -NoTypeInformation

    No need for -Append.


    -- Bill Stewart [Bill_Stewart]

    Thursday, March 17, 2016 5:45 PM
    Moderator