Exporting arrays to a .csv file

tysonokeefe5343 · 2013-05-14T12:01:12.000Z

Goal: Script that produces a .csv file that lists all server names with their IP and MAC addresses

Current script:

import-module ActiveDirectory

#Create .csv file with a list of all of our servers
get-adcomputer -filter * -SearchBase 'ou=servers, ou=firm, dc=domain, dc=com' | select dnshostname | export-csv "\\computer\c$\scripts\servernames.csv"

#create an array from the csv file
$servernames = @(import-csv "C:\scripts\servernames.csv")

#Loop through each element in the array to retrieve Server name, MAC and IP Address
foreach ( $servername in $servernames )
{

#Get MAC and IP Address
$colItems = Get-WmiObject -Class "Win32_NetworkAdapterConfiguration" -ComputerName $servername.dnshostname -Filter "IpEnabled = TRUE"

#Write info to screen
write-host Server name is: $servername.dnshostname

    foreach ($objItem in $colItems)
    {
    write-host MAC Address is: $objItem.MACAddress
    write-host IP Address is: $objItem.IPAddress
    }

}

Problem: The above script works great and produces exactly what I want in the shell but I want to instead send this output to a file (.txt, .csv, doesn’t matter). This is where I’m hitting a road block and can’t seem to figure out how to do it. My searches have just confused me as I don’t think I know the proper terms to ask my question.

My attempt:

$nameis = "Server name is: $servername.dnshostname"

    foreach ($objItem in $colItems)
    {
    $macis = "MAC Address is: $objItem.MACAddress"
    $ipis = "IP Address is: $objItem.IPAddress"
    }

$outarray = @($nameis, $macis, $ipis)
}

$outarray | export-csv "\\computer\c$\scripts\final.csv"

I know i’m not setting my variables correctly here as the values are not what I want but that’s another thing I’m not sure how to do or word it correctly. How would I set the $ipis variable for example so that it’s value shows as: IP Address is: 127.0.0.1?

chrisseiter · 2013-05-14T12:31:36.000Z

What is it doing now? Is it just showing the last machine queried? If so, you need to have the array add new items each iteration of the loop.

There’s a command in the back of my head that would send output to the screen and file in one step, but i can’t remember it right now.

tysonokeefe5343 · 2013-05-14T12:33:49.000Z

I think i just figured out how to do the last part to my question with the following:

[array]$temp = $servername.dnshostname
 $nameis = "Server name is: $temp"

But I still need help with exporting it to a .csv

With the code I posted in my first post, when I try to export it to a .csv file it just lists the length of the array string but not the actual values.

artb · 2013-05-14T13:09:18.000Z

These two lines need to be fixed.

$macis = "MAC Address is: $objItem.MACAddress"
$ipis = "IP Address is: $objItem.IPAddress"

to be this:

$macis = "MAC Address is: $($objItem.MACAddress)"
$ipis = "IP Address is: $($objItem.IPAddress)"

Inside a string, you need to enclose anything that needs to be expanded in $() or what your going to get is the standard string expansion of (for example) $objItem followed by the string .MACAddress.

Or, you can do it this way:

$OutArray = @() 
foreach ($Item in $colItems) { 
    $OutArray += New-Object PsObject -Property @{ 
        'NameIs' = $ServerName.DnsHostName 
        'MacIs' = $Item.MACAddress 
        'IPIs' = $Item.IPAddress 
    } 
} 
$OutArray | Export-Csv "\\computer\C$\scripts\final.csv" -NoTypeInformation

tysonokeefe5343 · 2013-05-14T15:47:33.000Z

Thanks Art, I was able to accomplish the same thing with my previous post but I like your way better.

However, i’m now realizing that I don’t even really need those lines sense I want to create a .csv file. It was good with testing the values and their output to the screen but I think 3 columns labled Server, MAC, IP would be better. Still not able to get the output into the .csv file how I want it though. Here’s where I’m at with the foreach loop now:

#Loop through each element in the array to retrieve Server name, MAC and IP Address
foreach ( $servername in $servernames )
{

#Get MAC and IP Address
$colItems = Get-WmiObject -Class "Win32_NetworkAdapterConfiguration" -ComputerName $servername.dnshostname -Filter "IpEnabled = TRUE"

$myhash = @{Server = $objItem.DNSHostName; MAC = $objItem.MACAddress; IP = $objItem.IPAddress} 
New-Object PSobject -property $myhash
}

$myhash | export-csv \\computer\c$\scripts\final.csv

and I get the following in my .csv file:

#TYPE System.Collections.Hashtable
IsReadOnly IsFixedSize IsSynchronized Keys Values SyncRoot Count
FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 3

anthony · 2013-05-14T16:01:10.000Z

Building off what ArtB was saying, you’ll want to use the += operator instead of the = as well as use the ‘New-Object’ cmdlet like:

#Loop through each element in the array to retrieve Server name, MAC and IP Address
$myhash = @()
foreach ( $servername in $servernames )
{
	#Get MAC and IP Address
	$colItems = Get-WmiObject -Class "Win32_NetworkAdapterConfiguration" -ComputerName $servername.dnshostname -Filter "IpEnabled = TRUE"

	$myhash += New-Object PsObject -Property @{'Server' = $colItems.DNSHostName; 'MAC' = $colItems.MACAddress; 'IP' = $colItems.IPAddress} 
	New-Object PSobject -property $myhash
}

$myhash | export-csv \\computer\c$\scripts\final.csv

I also noticed you named the variable objItems instead of colItems.

artb · 2013-05-14T16:52:30.000Z

Tyson: You were very close, but as was just shown, you need to put quote marks around the property names in the hash table. Also, you don’t need to use so many internal variables that only get used once. This is all you really need to get the output the way you want it…

$OutArray = @()
#Loop through each element in the array to retrieve Server name, MAC and IP Address
foreach ( $servername in $servernames ) {
    #Get MAC and IP Address
    $colItems = Get-WmiObject -Class "Win32_NetworkAdapterConfiguration" -ComputerName $servername.dnshostname -Filter "IpEnabled = TRUE"
    foreach ($Item in $colItems) {
        $OutArray += New-Object PsObject -Property @{
            'Server' = $ServerName.DnsHostName
            'MAC' = $Item.MACAddress
            'IP' = $Item.IPAddress
        }
    }
}
$OutArray | Export-Csv "\\computer\C$\scripts\final.csv" -NoTypeInformation

tysonokeefe5343 · 2013-05-14T17:23:24.000Z

Thank you for all the help but my final .csv file just looks like this:

#TYPE System.Management.Automation.PSCustomObject
Server MAC IP

It doesn’t actually fill in anything in the columns.

Here’s where i’m at with the code:

#create an array from the csv file
$servernames = @(import-csv "C:\scripts\servernames.csv")

#create array used to capture hostname, mac and ip address
$outarray = @()

#loop through each element in the array to retrieve Server name, mac and ip Address
foreach ( $servername in $servernames )
{

#get mac and ip address
$colItems = Get-WmiObject -Class "Win32_NetworkAdapterConfiguration" -ComputerName $servername.dnshostname -Filter "IpEnabled = TRUE"

#populate array with results
    foreach ($item in $colitems)
    {
        $outarray += New-Object PsObject -property @{
        'Server' = $objItem.DNSHostName
        'MAC' = $objItem.MACAddress
        'IP' = $objItem.IPAddress
        }
    } 
}

#export to .csv file
$outarray | export-csv \\computer\c$\scripts\final.csv

tysonokeefe5343 · 2013-05-14T17:39:45.000Z

Okay - I figured this one out. I needed to drop the “obj” and just call item.dnshostname.

I’m ALMOST there! I’m getting the .csv to output almost exactly how I want it except the IP address shows up as System.String

Example:

#TYPE System.Management.Automation.PSCustomObject
Server MAC IP
servername 00:00:00:00:00:00 System.String
servername2 00:00:00:00:00:00 System.String

I’m assuming this is because the IP address is a string value and can’t be read into the array that way? or the Array is expecting a string value but the IP address is of a different property? I’m assuming I need to somehow convert the item.ipaddress prior to filling the $outarray?

tysonokeefe5343 · 2013-05-14T17:44:32.000Z

Woo Hoo - I figured it out. I needed to put [string] in front of the $item.ipaddress call.

Here is my finished script (thanks to you guys!):

import-module ActiveDirectory

#create .csv file with a list of all of our servers
get-adcomputer -filter * -SearchBase 'ou=servers, ou=firm, dc=domain, dc=com' | select dnshostname | export-csv "\\computer\c$\scripts\servernames.csv"

#create an array from the csv file
$servernames = @(import-csv "C:\scripts\servernames.csv")

#create array used to capture hostname, mac and ip address
$outarray = @()

#loop through each element in the array to retrieve Server name, mac and ip Address
foreach ( $servername in $servernames )
{

#get mac and ip address
$colItems = Get-WmiObject -Class "Win32_NetworkAdapterConfiguration" -ComputerName $servername.dnshostname -Filter "IpEnabled = TRUE"

#populate array with results
    foreach ($item in $colitems)
    {
        $outarray += New-Object PsObject -property @{
        'Server' = $item.DNSHostName
        'MAC' = $item.MACAddress
        'IP' = [string]$item.IPAddress
        }
    } 
}

#export to .csv file
$outarray | export-csv \\computer\c$\scripts\final.csv

beaupellowski · 2015-05-18T16:10:31.000Z

Thanks, this helped me in one of my scripts by adding the [string] to convert it from an array to a string in the output as a powershell object!