The Sum of All Ports, coming to a SQL server near you.

Using syslog, MS SQL 2005, SQL Server Analysis Services, and MS Excel, I can build a cube with my firewall log violations and then import the cube into Excel and produce pivot tables. While this might seem more complicated than it needs to be, I could produce a daily scorecard of attacks. The only catch is that I need a firewall that logs to SQL server or a syslog to SQL server connector. The syslog => SQL connection would be tough because my router/firewall doesn't do uniform syslog notifications. I know enterprise-level firewalls do much better logging, like the Watchguard X-series which I was fond of just because I could make them do almost anything. The last time I checked, though, they still cost $1,500 for the base model plus $500 for the appropriate software.

With the Watchguard's new XML logging, I could create a SQL Server Integration Services package to import the data regularly. From there, I could get SQL Server Analysis services to process my cube each night. Then I use Microsoft Sharepoint's Scorecard or OLAP web part to display statistics. Best of all, I wouldn't have to mess with doing my own manual extract-transform-load (ETL) of my router log data.

The graph below represents a simple count of attacks by port on my router. Port 0 corresponds to ICMP. (I don't respond to ping requests.) The rest of the ports are closed, except for port 80, which you're using now. I ban a few IPs on port 80 because they won't stop posting junk trackbacks onto my blog. The ports are in alphabetical order rather than numerical order because I must store them in text fields rather than numerical fields in the database. If the port numbers aren't text then SSAS will OLAP them and I'll end up with the sum of all ports, which is nonsense but nevertheless might make a good statistic for MBA-types. While the graphic may not be all that impressive, the scalability is. Using SQL and SSAS, I could track probes and attacks on hundreds of firewalls at a time, track trends over time, and even predict the level of future probes.