Run Function On Unique Value in CSV

Welcome Forums General PowerShell Q&A Run Function On Unique Value in CSV

This topic contains 2 replies, has 2 voices, and was last updated by

1 month, 2 weeks ago.

  • Author
  • #119658

    Points: 15
    Rank: Member

    I have a script that pulls events from an event log, then outputs Usernames, IPs, and a Timestamp to a CSV for failed ADFS login attempts in the last 24 hours. This script then runs through and pulls out any events with the same IP address showing up multiple times and runs a geolocation REST URI on these.

    Because I only get 10k free location lookups each month, I need to get it so that the function that pulls geolocation only runs once on each unique IP address, but I still need to see every attempt in the output.

    For example, my output might look like this:

    ExtIP          Country_Name          Region_Name          Account          DateTime         USA                             Michigan                   User1               Oct 29, 2018 10:03:02         USA                             Michigan                   User1               Oct 29, 2018 10:03:02         USA                             Michigan                   User2               Oct 29, 2018 10:03:06


    What I need is more like this:

    ExtIP          Country_Name          Region_Name          Account          DateTime         USA                             Michigan                   User1               Oct 29, 2018 10:03:02                                                                                   User1               Oct 29, 2018 10:03:02         USA                             Michigan                   User2               Oct 29, 2018 10:03:06

    Here is the code I have currently:

    #Pull out only the items where the external IP causing the issue has more than 1 entry in our list.
    Import-Csv -Path "c:\adfselp\ADFS Lockout Report.csv" | Group-Object -Property ExtIP | Where-Object {$_.count -ge 2 } | Foreach-Object {$_.Group} | Select ExtIP, Account, DateTime | Export-csv -Path "c:\adfselp\Intermediate ELP Report.csv" -NoTypeInformation
    #This function adds Country and 'Region' Names to the multiple attempts report
    Import-Csv -Path "c:\adfselp\Intermediate ELP Report.csv" | Foreach-object {
    $IPAddress = $_.ExtIP
    $URI = "$($IPAddress)?access_key="
    $request = Invoke-RestMethod -Method Get -Uri $URI
    Add-Content -Path "c:\adfselp\Multiple Attempts Report.csv" -Value "$($_.ExtIP),$($request.country_name),$($request.region_name),$($_.Account),$($_.DateTime)"
  • #119730

    Points: 195
    Helping Hand
    Rank: Participant

    I would devide the problem into two steps.
    In this example it will generate the same list as in your first example but without the redundant lookups.
    Afterwards you can select, filter however you want 🙂

    1. Generate a list of unique IPs and then collect the information for those IPs.

    $ipAddressInfo = @()
    $csvData = Import-Csv -Path "c:\adfselp\Intermediate ELP Report.csv"
    $uniqueIps = $csvData | Select-Object -unique ExtIp
    foreach($u in $uniqueIps)
     $IPAddress = $u.ExtIP
     $URI = "$($IPAddress)?access_key="
     $request = Invoke-RestMethod -Method Get -Uri $URI
     $ipAddressInfo += [PSCustomObject]@{ExtIP = $u.ExtIp
                                         Country_Name = $request.country_name
                                         Region_Name = $request.region_name

    2. The second step would be to combine the information from the csv file using the $ipAddressInfo as an information source.

    $result = @()
    foreach($c in $csvData)
     $ipInfo = $ipAddressInfo | Where {$_.ExtIp -eq $c.ExtIp}
     $result += [PSCustomObject]@{ExtIp = $c.ExtIp
                                  Country_Name = $ipInfo.Country_Name
                                  Region_Name = $ipInfo.Region_Name
                                  Account = $c.Account
                                  DateTime = $c.DateTime

    3. The final step would just be to export the result list to .csv

    $result | Export-CSV -Path 'some_path.csv'

    So basically the first step generates an array with an object with the data requested for each IP.
    The PSCustomObject part is an accelerator instead of using New-Object "manually".

    The second step, just combines the information by lookup up the IP from the created list in the first step.
    Using the same technique to create the result and finally export it to .csv.

    • #119737

      Points: 15
      Rank: Member

      This is exactly the sort of thing I was looking for. Thank you!

You must be logged in to reply to this topic.