As promised in that post, here is some more detailed information on the use of country-by-country data sets in firewall configurations, where it may be appropriate, and methods by which one may use the sets to create traffic reports. While the methods listed and tools available are created specifically for ISA, the concept can be applied to any product that supports the necessary data elements.

Before we begin, I will post a disclaimer: This is a technical discussion. Nothing in the following dissertation is motivated by any political, religious, racial, or other ideal or prejudice. Processes discussed and opinions reached are based on technical and statistical research -- No other bias is intended nor should be inferred.

Background

One will recall that the SQL Slammer worm exploited a vulnerability in the SQL Browser service of Microsoft SQL Server 2000 and MSDE a single packet payload delivery over UDP 1434 to randomly -- or psudo-randomly -- generated IP targets. Slammer was launched back in January of 2003, so I was curious as to what I would find. I had long-since moved all published services from my home office, and had a single high-speed cable connection for access. Given Slammer's "random" target IP generation, I thought it would be a good environment to test in, and to be honest, I was a bit dubious on what the results would be as I tend to hope that gaps of over 5 years in a vulnerability will result in eventual patching.

Unfortunately, that was obviously not the case.

In a little under 4 days, I received over 30 unique Slammer attacks which were verified by packet inspection and confirmed not to be simple probes to UDP 1434. I was surprised, but not by the number of attacks. Upon further investigation, I found that every single attack -- all 30 of them -- were sourced from China, which really piqued my interest. I've seen many, many different reports of different attacks from different worms as well as reports of generalized Internet traffic by protocol and port, but this was the first time I had seen firsthand where a worm attack was sourced from a single country.

The result got me thinking: What was special about China in regards to Slammer? Was it true what others have said about China being a festering cesspool of malicious network activity? Were the boxes just all unpatched? Was China just being used as a launching pad for attacks sourced elsewhere? More importantly, what other malicious traffic was sourced there? And what other countries exhibited similar behavior with other protocols?

Before I could answer any of these questions, I had to first figure out how to reliably -- or reliably enough -- determine what country the source IPs were from. This required building a database of IP ranges that I could use to report from. There are a number of sources around the Internet once can use for such purposes: some are for pay, some are free. Depending on how much work you want to do, how much you are willing to pay, and how often you need updates, you'll have to make your own choice. Of course, I've already got the sets for ISA made and available for download for free, or you can compile your own database from sources on the Internet.

The list I ended up with contained over 230 countries comprised of over 100,000 individual records of IP ranges. Obviously, I had a lot more work to do in order to make traffic reporting easy enough to work with on a continuing basis. Enter Microsoft ISA Server. ISA Server is an enterprise firewall and caching product that supports extensive reporting, scripting, and customization options. You can use any product you wish, of course, as long as it provides you the tools you need. I'll be using ISA in all following examples.

The goal required several steps: I had to load all the records into SQL Server, which was easy enough. Then, I had to set up several test ISA Servers configured to log all traffic to SQL, and combine those servers' logs into one huge database I could use for report. Then, ultimately, I would need to create country-by-country "sets" in ISA comprised of each countries multiple lists of IP ranges so that I could create firewall rules to act upon the traffic by country.

Compiling the Data

BegIP (nvarchar 16), EndIP (nvarchar 16), BegIPLong (bigint), EndIPLong (bigint), Cntry (nvarchar 50), and the FullCntry (nvarchar 75)