Working With Data: IP and Port Filtering

  During our day-to-day work, there’s a lot of data that we interact with.  In order to make good use of it, you need to be able to work with it in a way that makes sense for the task at hand.  In today’s example, we’re going to consider a situation where the expected scan output contains IP addresses and port information in this format, IP:Port.  This type of output is common for scanning tools, and it’s just as common to have it copy/pasted into an Excel spreadsheet.

  The difficulty we run into is that the ports can be very similar when doing a general filter in Excel.  This isn’t difficult to sort through when it’s 50 or 100 rows of data, but it’s entirely different when you need to parse larger datasets with hundreds of thousands of rows.  

  This is where filtering comes in handy.  This can help you differentiate between cells with similar results, or find specific information that you need.  Excel offers a number of ways to filter data, including custom filters.  This will allow us to narrow down our results and filter out certain rows, based on specific criteria.  We'll be using Excel for this demonstration, but the same principles can be applied in any software that deals with data.

  For example, let’s say that we want to search for all of the rows that end with port 80 (i.e., but we want to exclude the rows which end with port 8080 (  To accomplish this, we’ll follow the steps below:

  • Select your column filter and go to Text_Filters > Custom_Filter
  • Set the first condition to contains and the value to :80 
  • Use the And operator
  • Set the second condition to does not contain and the value to :80? 

  And voila! When you apply your filter, only those ending with port 80 will remain.  Anything that begins with :80 , but has additional characters after that, will be ignored and filtered out.  If you want to refine your filters further, then it may be helpful to know that you can use other wildcards too, such as *.  

  A walkthrough of this exercise can be found in the video demonstration here

  Additionally, if you’re interested in security fundamentals, we have a Professionally Evil Fundamentals YouTube channel that covers a variety of technology topics.  Finally, if you’re looking for a penetration test, training for your organization, or just have general security questions please Contact Us.