Powershell aggregation, count items in log file, csv output

Question

I have a log file that looks as follows:

URL       Environment  
127.0.0.1 Prod  
127.0.0.1 Prod  
127.0.0.2 Test  
127.0.0.4 Prod  
127.0.0.2 Test

Now I want to aggreate by url and then environment and output it in csv format, so the result should look e.g. like this:

URL,Count
127.0.0.1, 2
127.0.0.2, 2  
127.0.0.4, 1

and

Environment,Count
Prod, 3
Test, 2

What's the best way to do that?
I found a solution on a different thread using group-object and measure-object which is similar to this problem though not quite the same.

How exactly does Group-Object not help in this situation? You just need to call it twice assuming you have formatted your input properly. Is that the issue? — Matt
– Matt, Commented Feb 1, 2015 at 5:30

Matt · Accepted Answer · 2015-02-01 13:28:29Z

3

Assuming the input file is exactly as you have it display on screen I would do something like this:

$inputPath = "c:\temp\test.txt"
$outputPath = "C:\temp\results.txt"

$data = Get-Content $inputPath | Select -Skip 1 | ForEach-Object{
    $splitLine = $_.split(' ',2)
    [pscustomobject][ordered] @{
        URL = $splitLine[0].Trim()
        Environment = $splitLine[1].Trim()
    }
}

$data | Group-Object URL | Select-Object @{Label="URL";Expression={$_.Name}},Count | ConvertTo-Csv -NoTypeInformation | Set-Content $outputPath
Write-Output "" | Add-Content $outputPath

Take the input file and for each line split it on the first space and return only two elements. Trim each split element to make a new object with parameters for URL and Environment.

Take that $data and feed it into Group-Object twice. Once for URL and once for Environment. Feed that data in a csv format to the $outputPath.

Sample Ouput (contents of $outputPath)

"URL","Count"
"127.0.0.1","2"
"127.0.0.2","2"
"127.0.0.4","1"

"Environment","Count"
"Prod","3"
"Test","2"

I'm sleepy so hopefully I read this one right.

[ordered]

If you only have PowerShell 2.0 this keyword wont exist. You can simply remove it from the code and it should work in place. Word of warning. [ordered] helps the output appear as coded Url then Environment. Using the Select statement account for that and organizes the output. If you added more fields don't forget to update the 2 select statements.

edited Feb 1, 2015 at 13:28

answered Feb 1, 2015 at 5:45

Matt

47k9 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brent Gallagher Over a year ago

Hey that's epic, thx! Just a small addition, there is also a datetime column in the log file, so I wanna convert that to a weekday ("Monday"..) and aggregate by weekday as well. Also the log file has no header column but some random empty lines in between. Anyway I'll try to figure out that bit myself, cheers!

Brent Gallagher Over a year ago

Actually getting an error when running that code: [pscustomobject][ordered] <<<< @{ + CategoryInfo : InvalidOperation: (ordered:String) [], RuntimeException + FullyQualifiedErrorId : TypeNotFound

Matt Over a year ago

@BrentGallagher I am guessing that you have PowerShell 2.0 then? [ordered] is a keyword introduced in v3. Easily addressed.

Collectives™ on Stack Overflow

Powershell aggregation, count items in log file, csv output

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related