Author Posts

May 5, 2017 at 5:15 pm

I would like ps to do the following, and it's kicking my ass as a complete powershell nub. The report has the IP addresses already and I need the hostnames. I have a CSV that is a report from a log server and appears like this

Source Device,Translated IP,Source IP,Destination IP,Port,Protocol,Description,Messages,Out Bytes
device name,111.111.111.111,222.222.222.222,333.333.333.333,80,tcp,ftp,2,1

I need to add fields to the header, every row below it, and do grab the hostnames from the ip's. The header should look like this

Source Device,Translated IP,TransName,Source IP,SourceName,Destination IP,Port,Protocol,Description,Messages,Out Bytes
FW_Primary,111.111.111.111,222.222.222.222,somewebsite,333.333.333.333,somewebsite,21,tcp,ftp,2,0

As a nub, I've managed to get SOME aspects of this done. For instance I have a script using get-content from the source csv and export-csv the lookup on a fresh file. Problem is I don't know how I could use get-content to pull from anything but the first column. So for it to work, I'd have to delete everything but 'Source IP'

Import-Csv wc.csv |
foreach-object {

$_ |
add-member -membertype noteproperty -Name 'Source IP Name' -value "" -passthru |
add-member -membertype noteproperty -Name 'Dest IP Name' -value "" -passthru |
} |
Export-Csv wc1.csv -NoTypeInformation

I've managed to figure out how to add columns using the add-member function but, I can't figure out how to embed the columns so that they aren't placed in the end. Any help would be appreciated.

May 5, 2017 at 5:27 pm

You can attempt a calculated property:

$ips = @()
$ips += [pscustomobject]@{IPAddress="98.139.183.24"}
$ips += [pscustomobject]@{IPAddress="204.79.197.203"}
$ips += [pscustomobject]@{IPAddress="172.217.10.100"}
$ips += [pscustomobject]@{IPAddress="23.194.120.88"}

$ips | Select *,
       @{Name="HostName";Expression={[System.Net.Dns]::GetHostbyAddress($_.IPAddress) | Select -ExpandProperty HostName}}

Output:

IPAddress      HostName                
---------      --------                
98.139.183.24  ir2.fp.vip.bf1.yahoo.com
204.79.197.203 a-0003.a-msedge.net     
172.217.10.100 lga34s15-in-f4.1e100.net
23.194.120.88  a23-194-120-88.deploy...

May 5, 2017 at 8:27 pm

Hmmmm. That would work, but the problem I have is adding those fields and populating them with the gethostbyaddress function.

I believe the steps would be (I could be wrong) the following, based on what the need is:

Step 1. Add a header row between 3 & 4
Step 2. Add a header row between 4 & 5
Step 3. Add names to each row
Step 4. Add commas to each row down the csv
Step 5. Pull hostnames and input them to the new rows
Step 6. Save csv

That's where my problems lie. Your code snippet would work specifically for fetching.

May 5, 2017 at 9:25 pm

There are a couple of ways. The $csv object is basically emulating CSV import, which generates a PSObject. You can specify order with Select-Object (Select), which is generating a new PSObject:

#Emulate CSV Import and generate PSObject
$csv = @()
$csv += [pscustomobject]@{
    Column1="98.139.183.24"
    Column2 = "yellow"
    Column5 = "foo"
}
$csv += [pscustomobject]@{
    Column1="204.79.197.203"
    Column2 = "red"
    Column5 = "blah"
}
$csv += [pscustomobject]@{
    Column1="172.217.10.100"
    Column2 = "blue"
    Column5 = "fooblah"
}

$csv | Select Column1,
              Column2,
              @{Name="Column3";Expression={[System.Net.Dns]::GetHostbyAddress($_.Column1) | Select -ExpandProperty HostName}},
              @{Name="Column4";Expression={"The color is {0}" -f $_.Column2}},
              Column5

#$csv | Export-CSV ...

or use [ordered] to generate a ordered hash table (a little more of a manual approach):

$newCsv = foreach ($row in $csv) {
    $props = [ordered] @{
        Column1 = $row.Column1
        Column2 = $row.Column2
        Column3 = [System.Net.Dns]::GetHostbyAddress($row.Column1) | Select -ExpandProperty HostName
        Column4 = "The color is {0}" -f $row.Column2
        Column5 = $row.Column5
    }

    New-Object -TypeName PSObject -Property $props
}

$newCsv #| Export-CSV ...

either method generates:

Column1 : 98.139.183.24
Column2 : yellow
Column3 : ir2.fp.vip.bf1.yahoo.com
Column4 : The color is yellow
Column5 : foo

Column1 : 204.79.197.203
Column2 : red
Column3 : a-0003.a-msedge.net
Column4 : The color is red
Column5 : blah

Column1 : 172.217.10.100
Column2 : blue
Column3 : lga34s15-in-f4.1e100.net
Column4 : The color is blue
Column5 : fooblah