Many organizations have IP address blocks spread out across the country or world, and must rely on centralized tools that may or may not report location and owner details in as granular a fashion as sometimes required. Splunk can come to the rescue in resolving an IP Address into a location and responsible team. If you work with Splunk you likely have used Lookups to enrich event data,. This is a demonstration of the advanced matching capabilities using CIDR that can save you from trying to deal with networks that don’t end on an octet boundary (e.g. /27s) and thus are harder to deal with in Excel spreadsheets or other tabulation tools.

Excel for sorting IP addresses

As a preliminary, a note on using Excel to manipulate and sort IP addresses. If all of your networks end on an octet boundary, an Excel spreadsheet or Google sheet may be all you need to analyze the IP addresses and locations in your Enterprise . Simply break the full addresses into octets using Text to Columns , sort , and then remove the last column and reassemble. Now if you combine the below steps with a network-to-location lookup table and use the VLOOKUP function, you can quickly create an analysis.

However, Excel is not as simple to apply if you use subnets or supernets that don’t fall on an octet boundary. As an alternative approach in your spreadsheet, IP addresses can be converted to either binary or decimal Integers –you can find or wrap your own Visual Basic to resolve them to Integers (e.g. octet1 * 256**3, …) and match them as ranges corresponding to locations. After this conversion it’s simple to sort into corresponding networks.

As an exercise, suppose one wanted to count the number of network-regions in a given output of raw IP addresses. Do the analysis above to sort into networks, then use a COUNTIF function to find the occurrences (as shown in the below formula bar). Use a de-dup to get the final network count. Note that it’s necessary to get the count via the formula, then copy and paste the G column to an adjacent column using values only (otherwise the de-dup will break the count).

Splunk CIDR-based lookup matching

What if your locations don’t neatly break into octet boundaries? Then it’s more work to do this lookup in Excel. Beyond that, it is very useful to stream output with location/owner/… data instantly available for review. Splunk can be used as a more flexible alternative. Here is a scaled down example, suppose your organization has the following IP map:

Suppose also that you have a bulk data set that you have to act on (e.g. break down into locations/owners and create tickets) in the form of a bulk CSV of systems, as below.

You can easily create a Lookup to process the bulk file uploads into any format of report you need. Here is an example of loading the lookup file, subnetmap.csv, and setting the definition.

With the Lookup loaded, simply upload your bulk data file in order to execute searches against it using the enriched events. Here we loaded the file into an index called ip_analysis.

Obviously this only scratches the surface. A bulk data loader is not actually needed: one can apply this to any event data across an Enterprise that can be enriched by location or owner. It would be simple to write a custom app that allows for input lists on the fly to be enriched by location data: a use case might be that one exports a list of non-compliant hosts from a tool such as Tenable Security Center or McAfee ePO and wants to mass-generate tickets.

Of course, any miscellaneous raw Splunk query can be enriched on the fly: it’s also simple to combine the lookup with tags that delineate classes of hardware (e.g. routers). A use case might be to query Splunk for all routers in California, for example.