Sort CSV Data

This topic contains 5 replies, has 2 voices, and was last updated by  Daniel Krebs 5 months, 1 week ago.

  • Author
    Posts
  • #70342

    Chris
    Participant

    Hi All,

    I'm very new to PS but would like to use it to achieve my goal, for training and reference purposes.

    I have a CSV Weblog, that i want to grab the IP's from and sort into a different text document, sounds easy enough, but when i try and grab the IP's it doesn't seem to play nice,

    ,User,URL,Categories,Action,Reason,Requests,%
    1,mandidavison,https://0.hola,Uncategorized,blocked,,2,0.0
    2,mandidavison,https://gisker.com,Uncategorized,blocked,,1,0.0
    3,lynneandrews,http://revolution-x.com,Uncategorized,blocked,1,0.0
    4,johnsmith,http://216.58.206.46,Uncategorized,blocked,category,2,0.0
    5,johnsmith,https://172.217.23.42,Uncategorized,blocked,category,4,0.0
    6,johnsmith,https://185.60.216.11,Uncategorized,blocked,category,36,0.2
    7,johnsmith,https://185.60.216.15,Uncategorized,blocked,category,4,0.0
    8,johnsmith,https://185.60.216.52,Uncategorized,blocked,category,3,0.0
    9,johnsmith,https://216.58.198.170,Uncategorized,blocked,category,2,0.0

    That's the format it comes out in, i just want to grab the IP's, which in this case are under URL. Is there an easy way to do this? Apologies if this is a bit simple 🙂

  • #70351

    Daniel Krebs
    Moderator

    Hi Chris,

    Welcome to the Forum and the world of PowerShell. Here are a couple of examples for you to test and digest. Please don't hesitate to ask follow-up questions to understand below better and use Get-Help for additional details.

    $records = @'
    ,User,URL,Categories,Action,Reason,Requests,%
    1,mandidavison,https://0.hola,Uncategorized,blocked,,2,0.0
    2,mandidavison,https://gisker.com,Uncategorized,blocked,,1,0.0
    3,lynneandrews,http://revolution-x.com,Uncategorized,blocked,1,0.0
    4,johnsmith,http://216.58.206.46,Uncategorized,blocked,category,2,0.0
    5,johnsmith,https://172.217.23.42,Uncategorized,blocked,category,4,0.0
    6,johnsmith,https://185.60.216.11,Uncategorized,blocked,category,36,0.2
    7,johnsmith,https://185.60.216.15,Uncategorized,blocked,category,4,0.0
    8,johnsmith,https://185.60.216.52,Uncategorized,blocked,category,3,0.0
    9,johnsmith,https://216.58.198.170,Uncategorized,blocked,category,2,0.0
    '@ | ConvertFrom-Csv
    
    # $records = Import-Csv -Path C:\My\Input.csv
    
    # Example 1 - No regular expressions
    foreach ($url in $records.URL) {
        $ipaddress = $null
    
        $address = $url.ToLower().Replace('http://', '').Replace('https://', '')
        if ([IPAddress]::TryParse($address, [ref] $ipaddress)) {
            $ipaddress.IPAddressToString
        }
    }
    
    # Example 2 - Using the -replace operator with a regular expression to remove http/https://
    #             so we can attempt to parse it into a valid IP address
    foreach ($url in $records.URL) {
        $ipaddress = $null
    
        $address = $url -replace 'https?://', ''
        if ([IPAddress]::TryParse($address, [ref] $ipaddress)) {
            $ipaddress.IPAddressToString
        }
    }
    
    # Example 3 - Using the -match operator with a more complex regular expression to extract the IP address
    foreach ($url in $records.URL) {
        if ($url -match '(?[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})') {
            $Matches.IPAddress
        }
    }
    
    # Example 4 - Using the -match operator with a more regular expression to extract the IP address
    #             and casting to IPAddress to ensure we get a valid IP address
    foreach ($url in $records.URL) {
        if ($url -match '(?[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})') {
            ([IPAddress] $Matches.IPAddress).IPAddressToString
        }
    }
    
  • #70352

    Chris
    Participant

    Hi Daniel,

    Thank you, i'm excited to get involved!

    Do i import the CSV first, let it populate, then use one of the expressions?

    Or do i pipe the expression after, eg Import-CSV C:\webreport.csv | *expression"

    I really appreciate your assistance 🙂

  • #70355

    Daniel Krebs
    Moderator

    Usually, I would import the CSV from disk with the Import-Csv cmdlet. The block of code line 1 to 12 is only a hack to get the example CSV you've posted into a useable state without creating a file on my machine. The resulting array of objects is the same as using Import-Csv for a file on disk.

    Import-Csv does a bit of magic. It imports a CSV file and outputs an array of objects with properties for each header column in your file instead of lines of plain-text like Get-Content. That makes handling the content easier for processing. If your CSV file would come without a header you could specify a custom header with the -Header parameter but for your example CSV that is not necessary.

    You could pipe the output of Import-Csv but the code would look slightly different.

    $csvFileName = 'C:\My\Input.csv'
    
    # Example 1 - No regular expression
    Import-Csv -Path $csvFileName | 
        ForEach-Object {
            $ipaddress = $null
    
            $address = $PSItem.URL.ToLower().Replace('http://', '').Replace('https://', '')
            if ([IPAddress]::TryParse($address, [ref] $ipaddress)) {
                $ipaddress.IPAddressToString
            }
        }
    
    # Example 2 - Using the -replace operator with a regular expression to remove http/https://
    #             so we can attempt to parse it into a valid IP address
    Import-Csv -Path $csvFileName | 
        ForEach-Object {
            $ipaddress = $null
    
            $address = $PSItem.URL -replace 'https?://', ''
            if ([IPAddress]::TryParse($address, [ref] $ipaddress)) {
                $ipaddress.IPAddressToString
            }
        }
    
    # Example 3 - Using the -match operator with a more complex regular expression to extract the IP address
    Import-Csv -Path $csvFileName | 
        ForEach-Object {
            if ($PSItem.URL -match '(?[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})') {
                $Matches.IPAddress
            }
        }
    
    # Example 4 - Using the -match operator with a more regular expression to extract the IP address
    #             and casting to IPAddress to ensure we get a valid IP address
    Import-Csv -Path $csvFileName | 
        ForEach-Object {
            if ($PSItem.URL -match '(?[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})') {
                ([IPAddress] $Matches.IPAddress).IPAddressToString
            }
        }
    

    To get some other basic examples just run below in a PowerShell session on a machine with Internet access. It will open your default web browser and open the online help of Import-Csv.

    Get-Help Import-Csv -Online

    I hope above makes sense. If not, please ask follow-up questions. Happy to help.

    Additionally, you might want to pick up the book "Learn Windows PowerShell in a Month of Lunches" or watch the "Getting Started with PowerShell" series on Channel 9 (https://channel9.msdn.com) or the Microsoft Virtual Academy (https://mva.microsoft.com). Both the book and the video series cover PowerShell basics very well.

    – Daniel

  • #70357

    Chris
    Participant

    Hi Daniel,

    Yes, i'm currently working through the MVA Powershell course and saw that book recommended, definitely worth a purchase,

    I have tried running each of those methods in the ISE and they all error out (I've replaced the file location with mine)

    For example, method 4 returns the following:

    parsing "(?[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})" - Unrecognized grouping construct.
    At line:4 char:13
    +         if ($PSItem.URL -match '(?[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3} ...
    +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : OperationStopped: (:) [], ArgumentException
        + FullyQualifiedErrorId : System.ArgumentException

    I really appreciate your time with this.

  • #70360

    Daniel Krebs
    Moderator

    Oh sorry, IPAddress and the angle brackets got filtered out by the forum engine in the regular expressions of example 3 and 4.

You must be logged in to reply to this topic.