Format CSV From Object

Question

I am trying to figure out how to create a .csv from a PowerShell object that has the appropriate columns. I want to create a .csv file with three columns (User ID, Country, Count). It also needs to sort by User ID and then Country (A to Z). In the example code below, I want the output to be like this:

User ID, Country, Count
Bob, China, 2
Joe, Canada, 1
Joe, United States, 1

$path = "C:\test\test.csv"
Set-Content -Path $path -Value “UserId, Country”
Add-Content -Path $path -Value "Joe, United States", "Bob, China", "Bob, China", "Joe, Canada"
(Import-Csv -Path $path) | Group-Object -Property UserID, Country -NoElement | Select-Object * -ExcludeProperty Values, Group | Select-Object @{Name="User ID, Country";Expression={$_.Name}}, Count | Export-Csv -Path $path -NoTypeInformation -Force
Get-Content -Path $path

Unfortunately, when I run this, User ID and Country are in the same column as below:

"User ID, Country","Count"
"Joe, United States","1"
"Bob, China","2"
"Joe, Canada","1"

I believe the problem stems from Select-Object @{Name="User ID, Country";Expression={$_.Name}}. How can I get this to be in three columns sorted as requested above? As a bit of an aside, I do not fully understand the syntax of the problem code. If you could explain what the @{Name="User ID, Country";Expression={$_.Name}} does, I would appreciate it.

EDIT Here is my actual code in case this helps with the problem at all.

Set-ExecutionPolicy RemoteSigned

$lastMonth = (get-date).AddMonths(-1)
$startDate = get-date -year $lastMonth.Year -month $lastMonth.Month -day 1
$endDate = ($startDate).AddMonths(1).AddSeconds(-1)
$operation = UserLoginFailed
$path = "C:\Failed Logins $($lastMonth.ToString("yyyy_MM")).csv"

Set-Content -Path $path -Value “UserId, Country”
Function Connect-EXOnline {
    $credentials = Get-Credential -Credential $credential     
    $Session = New-PSSession -ConnectionUri https://outlook.office365.com/powershell-liveid/ `
        -ConfigurationName Microsoft.Exchange -Credential $credentials `
        -Authentication Basic -AllowRedirection
    Import-PSSession $Session -AllowClobber
}
$credential = Get-Credential
Connect-EXOnline

$Logs = @()
do {
    $logs += Search-unifiedAuditLog -SessionCommand ReturnLargeSet -SessionId "UALSearch" -ResultSize 5000 -StartDate $startDate -EndDate $endDate -Operations $operation
}while ($Logs.count % 5000 -eq 0 -and $logs.count -ne 0)
 
$userIds = $logs.userIds | Sort-Object -Unique
 
foreach ($userId in $userIds) {
    $ips = @()
    $searchResult = ($logs | Where-Object {$_.userIds -contains $userId}).auditdata | ConvertFrom-Json -ErrorAction SilentlyContinue 
    $ips = $searchResult.clientip
    foreach ($ip in $ips) {
        $mergedObject = @{}
        $singleResult = $searchResult | Where-Object {$_.clientip -contains $ip} | Select-Object -First 1
        Start-sleep -m 400
        $ipresult = Invoke-restmethod -method get -uri http://ip-api.com/json/$ip
        $UserAgent = $singleResult.extendedproperties.value[0]
        $singleResultProperties = $singleResult | Get-Member -MemberType NoteProperty
        foreach ($property in $singleResultProperties) {
            if ($property.Definition -match "object") {
                $string = $singleResult.($property.Name) | ConvertTo-Json -Depth 10
                $mergedObject | Add-Member -Name $property.Name -Value $string -MemberType NoteProperty    
            }
            else {$mergedObject | Add-Member -Name $property.Name -Value $singleResult.($property.Name) -MemberType NoteProperty}          
        }
        $property = $null
        $ipProperties = $ipresult | get-member -MemberType NoteProperty
 
        foreach ($property in $ipProperties) {
            $mergedObject | Add-Member -Name $property.Name -Value $ipresult.($property.Name) -MemberType NoteProperty
        }
        $mergedObject | Select-Object UserId, Country  | Export-Csv $path -Append -NoTypeInformation
    }
}

(Import-Csv -Path $path) | Group-Object -Property UserID, Country -NoElement | Select-Object * -ExcludeProperty Values, Group | Select-Object @{Name="User ID, Country";Expression={$_.Name}}, Count | Export-Csv -Path $path -NoTypeInformation

Basically, I will have a .csv file that contains a bunch of email addresses and failed login attempts by country. The last line is used to count how many failed attempts each email address has from each company. This is where the problem is.

Answer 1

I have simplified your problem statement, in order to tackle one issue at a time.
I set out to solve the simple problem of producing the output CSV file you want from the input CSV file you stated. I created the input file with notepad, instead of with powershell.

Here is what I came up with:

<# Summarizes log by user and country.   #>

Import-csv logfile.csv |
    Sort-Object  UserID, Country |
    Group-Object UserID, Country | 
    foreach { $b = $_.name -split ', '
        [PSCustomobject] @{UserID = $b[0]; Country= $b[1]; Count=$_.count}}|
    Export-Csv summary.csv

Import-Csv logfile.csv | Format-table
Import-Csv summary.csv | Format-table

The last two steps just display the input and the output CSV files. This is what it looked like when I ran it.

UserID Country      
------ -------      
Joe    United States
Bob    China        
Bob    China        
Joe    Canada       



UserID Country       Count
------ -------       -----
Bob    China         2    
Joe    Canada        1    
Joe    United States 1    

Answer 2

As mentioned in my comment, you'll need two calculated properties to split the Name again.

(Import-Csv $path) | Group-Object 'User ID',Country -NoElement | 
    Select-Object @{n='UserID';e={($_.Name -split ', ')[0]}},
                  @{n='Country';e={($_.Name -split ', ')[1]}},
                  Count | Export-Csv $Path -NoTypeInformation

This will yield the same output as Walter Mitty's solution.

Answer 3

You can make your life much easier when you use the availabe built in cmdlets for CSV data like this:

$path = 'C:\test\test.csv'
$Data = @'
UserID,Country,Count
Bob,China,2
Joe,Canada,1
Joe,United States,1
'@

ConvertFrom-Csv -Delimiter ',' -InputObject $Data -OutVariable CSVData
$CSVData | Export-Csv -Path $path -NoTypeInformation -Delimiter ','

You simply build your data as you need it completely and you convert it or export it in once.