Saturday, May 13, 2017

Cookbook for turning large network captures into firewall change requests

Cookbook for turning large network captures into firewall change requests

I started with 15 million captured packets. The instructions below illustrate how I reduced that data to a unique list of outbound connections. I’m sharing this in case any of you need to do this in the future.

1.    12 hour capture on source machine, writing to 50 MB capture files. Implement this via a scheduled task / script. (This is the source of the 15 million packets.)
2.    Use Log Parser on a 2003/XP machine to export capture files to .CSV files
3.    Combine all .CSV files into one file. Notepad cannot open a file this large.
type *.csv>>result.txt
4.    Use a custom written script to break the large file into separate files 1048576 lines in length. 1048576 is the largest row set Excel can use in a single worksheet. (In the second pass, I wrote a C# program to do this.)
5.    Open each file in Excel. I suggest using unique instances of Excel for each file, which cuts down significantly on delays. It helps if you have a laptop with a lot of RAM and 64-bit Office when doing this. I used this command to launch multiple instances of Excel:
for /f %x in (‘dir *.csv /b’) do start /separate “Excel” excel.exe %x

In each Excel file:
6.    Filter out all inbound connections
7.    In a new column, concatenate target IP and target port to DstIP:DstPort
=concatenate(a1,”:”,a2)
8.    Use filtering to filter DstIP:DstPort down to only unique entries
9.    Combine all uniques in a single summary workbook and use filtering to reduce that to a list of uniques

At this point, I had a complete list of unique outbound connections. I needed to put this in an “ISM7 friendly” format.

Resolve host names:
10.    Use filtering to generate a list of unique IP addresses
11.    Use a custom written script to resolve each IP address to a DNS name. The script outputs a file in the format “IP,FQDN”
12.    Import the result to a new worksheet (comma delimited text file import), then use vlookup to associate host names with IP addresses in the main worksheet

Consolidate the data:
13.    Sort by target IP
14.    Manually search through the list to identify multiple connections to a single IP
15.    Identify any servers that appear to require the use of high or dynamic ports (many connections to the same IP using unique high port numbers). Most of these cases require opening 1025-65536
16.    Identify servers that require multiple specific ports and consolidate the port requirement to a single line, deleting duplicates
17.    Identify connections to AD DCs and delete them (should be covered by a “tier zero” ISM7, which is a documented standard)

At this point, I had a nearly complete list of unique outbound connections with no duplicates. All that was left was to massage this data into the firewall change request format, add the source server information, and submit the request.

- - - - -

This was a bit of work (a few hours), but not impossible. In the future, I would create an application that would do most of the heavy lifting. The application would:
•    Read in a capture file in the capture tool’s native format. There are open source C# projects that read in a variety of capture formats, including MS Network Monitor
•    Reduce the result to a list of unique targets (DstIP:DstPort) and perform this comparison in memory. This would speed up the data analysis process dramatically.
•    Once the unique target data is available, there would still be some manual human analysis that would be necessary, but the above would speed up the data processing dramatically.


If you made it this far, thanks for reading.