How to use Powershell to loop through CSV lines and export tables

Question

I'm a bit new to Powershell, but within my new job I'm finding lots of uses for it, and at the moment it's one of the only scripting languages I get access to. I'm trying to audit some reports of firewall traffic and the reports come as PDF and CSV. The organization of the report makes this a bit tricky (at least to me). It presents as only two columns, with the second column being session count. The first column is the tricky part. It contains the destination address by itself, the destination port, indented by a couple of tabs with a title, and the source IP for each destination port count indented a couple more, with a title.

The snippet i've provided is a small sample, and I've changed the information for public consumption. Ultimately I'd like to be able to focus on a destination port and learn more about which destination addresses are the most chatty on that port, and by which source IPs. The ultimate goal is to be able to audit our many, many existing firewall rules that seem overly permissive and start making new rules above them to 'peel' out some of this traffic. Since i'm going to have to do it a lot, I thought scripting the analysis of these reports would help streamline the process for myself, and anyone in the future.

192.168.250.12,46420
          ip.dstport  135,          32783
                    ip.src  10.10.248.111,                    32761
                    ip.src  172.16.152.96,                    22
          ip.dstport  23,          13597
                    ip.src  10.10.248.111,                    13590
                    ip.src  172.16.152.96,                    7
          ip.dstport  445,          32
                    ip.src  172.16.152.96,                    26
                    ip.src  10.10.248.111,                    6
          ip.dstport  22,          8
                    ip.src  172.16.152.96,                    8
192.168.250.14,44788
          ip.dstport  135,          31213
                    ip.src  10.10.248.111,                    31182
                    ip.src  172.16.152.96,                    31
          ip.dstport  23,          12991
                    ip.src  10.10.248.111,                    12984
                    ip.src  172.16.152.96,                    7
          ip.dstport  80,          377
                    ip.src  10.10.53.133,                    215
                    ip.src  10.10.139.42,                    83
                    ip.src  10.10.53.109,                    35
                    ip.src  10.10.15.196,                    32
                    ip.src  10.10.85.155,                    6
                    ip.src  10.10.15.123,                    4
                    ip.src  10.10.15.148,                    2
          ip.dstport  445,          146
                    ip.src  10.10.15.123,                    87
                    ip.src  172.16.152.96,                    30
                    ip.src  10.10.185.151,                    8
                    ip.src  10.10.3.51,                    6
                    ip.src  10.10.248.111,                    5
                    ip.src  10.10.15.19,                    4
                    ip.src  10.10.15.103,                    2
                    ip.src  10.10.248.175,                    2
                    ip.src  10.10.12.37,                    1
                    ip.src  10.10.91.101,                    1
          ip.dstport  1433,          20
                    ip.src  10.10.248.111,                    20
          ip.dstport  3389,          15
                    ip.src  10.10.51.122,                    8
                    ip.src  10.10.51.126,                    4
                    ip.src  10.10.15.123,                    3
          ip.dstport  53,          13
                    ip.src  172.16.152.96,                    13
          ip.dstport  8443,          9
                    ip.src  10.10.91.101,                    9
          ip.dstport  8080,          4
                    ip.src  172.16.152.96,                    4

After manually moving things around in Excel I got a table that allows for this. But automating this would be the goal

ip.dst  ip.dstport  ip.src  Session_count
192.168.250.12  135             10.10.248.111   32761
192.168.250.12  135             172.16.152.96   22
192.168.250.12  8080                10.10.248.111   13590
192.168.250.12  8080                172.16.152.96   7
192.168.250.12  445             172.16.152.96   26
192.168.250.12  445             10.10.248.111   6
192.168.250.12  8443                172.16.152.96   8
192.168.250.14  135             10.10.248.111   31182
192.168.250.14  135             172.16.152.96   31
192.168.250.14  8443                10.10.248.111   5
192.168.250.14  8443                172.16.152.96   3
192.168.250.14  8080                10.10.248.111   12984
192.168.250.14  8080                172.16.152.96   7
192.168.250.14  80              10.10.53.133    215
192.168.250.14  80              10.10.139.42    83
192.168.250.14  80              10.10.53.109    35

Answer 1

Mathias R.Jessen beat me ;-)

This script generates the same output using 3 REs with named capture groups:

## Q:\Test\2019\08\14\SO_57500376.ps1
$RE1 = '^(?<IpDst>(\d{1,3}\.){3}\d{1,3}),'
$RE2 = '\s*ip\.dstport\s*(?<IpDstPort>\d+)'
$RE3 = '\s*ip\.src\s*(?<IpSrc>(\d{1,3}.){3}\d{1,3}),\s*(?<Sessions>\d+)'

$FileIn = '.\fw_log.txt'

$Data = Get-Content $FileIn | ForEach-Object {
    If($_ -match $RE1){$IpDst = $Matches.IpDst}
    If($_ -match $RE2){$IpDstPort = $Matches.IpDstPort}
    If($_ -match $RE3){
        [PSCustomObject]@{
            'ip.dst'        = $IpDst
            'ip.dstport'    = $IpDstPort
            'ip.src'        = $Matches.IpSrc
            'Session_count' = $Matches.Sessions 
        }
    }
}
$Data

Answer 2

You can parse this type of format and create meaningful output from it line-by-line easily, either with ForEach-Object or a simple loop over Get-Content.

I'd personally go for a switch in -regex mode:

function Import-FireWallLog
{
    param(
        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [Alias('PSPath')]
        [string[]]$LiteralPath
    )

    process {
        Write-Host $LiteralPath -ForegroundColor Cyan
        switch -regex -file ($LiteralPath) {
            '^(\d{1,3}(?:\.\d{1,3}){3}),' {
                # Grab the capture block value (the dst ip), save for later
                $dstIP = $Matches[1]
                continue
            }

            'ip\.dstport\s+(\d+),' {
                # Grab the capture block value (the dst port), save for later
                $dstPort = $Matches[1]
                continue
            }

            'ip\.src\s+(\d{1,3}(?:\.\d{1,3}){3}),\s+(\d+)' {
                # Emit a new object with the ip.src, session_count and previously stored dst info
                [pscustomobject]@{
                    'ip.dst'        = $dstIP
                    'ip.dstport'    = $dstPort
                    'ip.src'        = $Matches[1]
                    'session_count' = $Matches[2]
                }
                continue
            }

            default {
                # a line format we don't recognize
                # let's write it to the Debug stream 
                Write-Debug -Message "Encountered unrecognized input: '$_'"
            }
        }
    }
}