IP Address filtering and reverse DNS checking

Executive Summary

This project was designed to help the Network Security Engineers working for The Church of Jesus Christ of Latter Day saints. The church has a large network that requires the protection of an entire team of Network security professionals. This team of professionals works 24/7 to protect all of the churches assets, personal information and other sensitive information from getting into “bad guys” hands.

This team of professionals is given the daunting task of reviewing thousands of log files per day looking for the proverbial needle in the haystack. This takes considerable amounts of time. While many of the tasks take a large amount of skill, some of the tasks are quite simple, and could easily be automated.

Problem Description

When I was interning at the church as a Network Security Engineer one of the most time consuming tasks that we did was reviewing what IP addresses the Point of Sale machines in the distribution centers from around the country were contacting. We reviewed them to determine if the IP address was valid (from a known payment processing service), or invalid (facebook,  malware, etc).

My co-workers had developed a spreadsheet that listed valid IP Addresses. However, all of the comparisons were manually done, which took forever. Most addresses were expected, it was simply a matter of sorting through all of the other addresses to determine if yours really was in the list.

This final project will attempt to fix the tedious process of doing this manually. Specifically it includes these functions.

·         Easy importing for IP addresses that are being tested via CSV file, because this was the only way other than straight copy paste to get data into excel.

·         Automate the removal of duplicate entries, to reduce calculation time.

·         Remove the “expected” Ip addresses from the list of accessed IP addresses. Leaving us with the “unexpected” or “dangerous” Ip addresses and their resolved domain names.

·         Automate logging into the reverse dns checking website to determine what the hostname of the unexpected IP address is.

·         For testing purposes I Generated a random list of valid routable IP addresses to test with.

I spent an hour or two a day (at least) manually looking up information that should have been automated. I haven’t checked with the church to see if they still need this, but this was my first ever realization that I should probably learn VBA, because if I had automated that, I think I would have easily gotten a raise, and a job offer. This task is what prompted me to take this VBA class.

Links to:

Write up: http://files.gove.net/shares/files/12f/mniland/Final_Project_Writeup.pdf
Final Project: http://files.gove.net/shares/files/12f/mniland/FinalProject.xlsm