Add columns to csv and do a hostname lookup

This topic contains 3 replies, has 2 voices, and was last updated by  Rob Simmers 5 months, 2 weeks ago.

  • Author
    Posts
  • #70234

    Daniel
    Participant

    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.

  • #70236

    Rob Simmers
    Participant

    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...
    
    • #70254

      Daniel
      Participant

      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.

  • #70257

    Rob Simmers
    Participant

    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
    

You must be logged in to reply to this topic.