Run Function On Unique Value in CSV

PowerShell Help
1

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

10.1.1.1 USA Michigan User1 Oct 29, 2018 10:03:02

10.1.1.1 USA Michigan User1 Oct 29, 2018 10:03:02

10.1.1.2 USA Michigan User2 Oct 29, 2018 10:03:06


 

What I need is more like this:

ExtIP Country_Name Region_Name Account DateTime

10.1.1.1 USA Michigan User1 Oct 29, 2018 10:03:02

10.1.1.1 User1 Oct 29, 2018 10:03:02

10.1.1.2 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 = "http://api.ipstack.com/$($IPAddress)?access_key=<my 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)"

}
2

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

  1. Generate a list of unique IPs and then collect the information for those IPs.
    E.g.
$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 = "http://api.ipstack.com/$($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
                                     }
}
  1. 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
                             }
}
  1. 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.

3

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