Sort CSV Data

PowerShell Help
1

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 :slight_smile:

2

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
    }
}
3

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 :slight_smile:

4

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
5

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.

6

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