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

Give it a stab yourself then come back here with some code if you get stuck. You should be able to import-csv then iterate over it using string splitting to separate the first csv column using the ip.dstport/src values. — Scepticalist
– Scepticalist, Commented Aug 14, 2019 at 19:09

Mathias R. Jessen · Accepted Answer · 2019-08-14 19:48:28Z

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: '$_'"
            }
        }
    }
}

answered Aug 14, 2019 at 19:48

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

1 Comment

Cole Cash Over a year ago

Showing my noobness here, but saving the above as a .ps1 I can't get it to run from a prompt. I ended up defining the variable $LiteralPath manually and then highlighting the 'process' section within ISE and running it that way.

user6811411 · Accepted Answer · 2019-08-14 20:27:30Z

2

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

answered Aug 14, 2019 at 20:27

user6811411

1 Comment

Cole Cash Over a year ago

Thank you, and Mathias, for this. This one is a little simpler to wrap my brain around and demonstrates the logic that was escaping me yesterday for some reason. The output is clean and can easily be exported to a csv or just about anything I suppose. Thank you

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related