Trying to combine data into a single line

Question

I work for a company that has a few web servers that is load-balanced. I've created a PowerShell script that goes against all our IIS servers and pulls the 500 error entries and then pulls the clients IP address' and shows me a count of how many time that client's IP address has caused a 500 error (yes, I know we can use LogParser for this - but it was an excuse to help learn some basic PowerShell skills).

Right now my output is showing the web server name, the count and the IP address. Ideally I would like the count to aggregate the count across all web servers and show the IP Address. I've included the code below, with both the actual output and the desired output.

$source      = "WebServer"
$destination = "LogServer01"
$date        = "190122"

if (Test-Path \\$destination\Drive$\Temp\LogFiles\IISLogs\"IISLogs-for-$($date)".txt) {
    Remove-Item \\$destination\Drive$\Temp\LogFiles\IISLogs\"IISLogs-for-$($date)".txt  
}
for ($i = 1; $i -lt 13; $i++) {
    if ($i -lt 10) {
        $serverName = "$($source)0$($i)"
    } else {
        $serverName = "$($source)$($i)"
    }
    # Grabbing the logs
    Get-Content -Path \\$serverName\D$\LogFiles\WebSite\W3SVC20000\"u_ex$($date)*.log" |
        Where-Object { $_.Contains(" 500")} |
        ForEach-Object { $_.Split(" ")[15] } |
        Group-Object |
        Where-Object { $_.Count -gt 1 } |
        Where-Object { $_.Name -ne "-" } |
        Sort-Object Count -Descending |
        Format-Table Count, Name >> \\$destination\D$\Temp\LogFiles\IISLogs\"IISLogs-for-$($date)".txt
};

Desired output:

Count    Name
17       186.0.25.8
15       202.58.5.16
12       96.58.1.58

Actual output:

Webserver01   Count   Name
              5       186.0.25.8
              3       202.58.5.16
              2       96.58.1.58
Webserver02   Count   Name
              4       186.0.25.8
              2       202.58.5.16
              1       96.58.1.58
Webserver03   Count   Name
              4       186.0.25.8
              1       202.58.5.16
              2       96.58.1.58

Answer 1

Here is the solution I came up with, all I had to do is add to the Format-Table cmdlet a -Group By "Name" and set -Property to "Count", here is my code for those that are interested:

$source         =       "Webserver"
$destination    =       "LogServer01" 
$date           =       "190122"
if (Test-Path \\$destination\D$\Temp\LogFiles\IISLogs\"IISLogs-for-$($date)".txt) {
  Remove-Item \\$destination\D$\Temp\LogFiles\IISLogs\"IISLogs-for-$($date)".txt  
}
for($i = 1; $i -lt 13; $i++)
    {if ($i -lt 10)
        {
            $serverName = "$($source)0$($i)"
        }
     else {
            $serverName = "$($source)$($i)"
        }
        Get-Content -Path \\$serverName\D$\LogFiles\WebSite\W3SVC20000\"u_ex$($date)*.log" |
        Where-Object {   $_.Contains(" 500")} |
        ForEach-Object {$_.split(" ")[15]} |
        Group-Object | ## Grouping the data by name column (IP Address)
        Where-Object {$_.Count -gt 1} |
        Where-Object {$_.Name -ne "-"} |
        Sort-Object Count -Descending |
        Format-Table -GroupBy Name -Property Count >> \\$destination\D$\Temp\LogFiles\IISLogs\"IISLogs-for-$($date)".txt
            };

Answer 2

Simple approach: add the server name as a calculated property:

... |
Group-Object -NoElement |
Select-Object @{n='ServerName';e={$serverName}}, Count, Name |
...

That won't give you exactly the desired output, though. Instead you'll get:

ServerName   Count  Name
----------   -----  ----
Webserver01  5      186.0.25.8
Webserver01  3      202.58.5.16
Webserver01  2      96.58.1.58
Webserver02  4      186.0.25.8
Webserver02  2      202.58.5.16
Webserver02  1      96.58.1.58
Webserver03  4      186.0.25.8
Webserver03  1      202.58.5.16
Webserver03  2      96.58.1.58

However, I would consider this format preferable, because by replacing Format-Table ... >> ... with Export-Csv you can export the data in a format that can easily be read by other applications.

If you want the output exactly like in your question you'll need to create custom output, e.g. via the format operator (-f).

$data = Get-Content -Path ... |
        ...
        Sort-Object Count -Descending

'{0,-15} Count  Name' -f $serverName | Set-Content 'C:\output.txt'
$data | ForEach-Object {
    '{0,-15}  {1,-5}  {2}' -f '', $_.Count, $_.Name
} | Add-Content 'C:\output.txt'