l e n s b o x s t u d i o s

Anyone with exposure to the full Microsoft Office suite has more than likely seen Access and may have opened it, only to close it shortly thereafter. Most people don’t understand databases, even simple ones such as Access.

Those that do, may scoff at Access, their tools of choice being MySQL or Microsoft SQL or even Oracle and beyond… but for those few brave souls that stick it out with Access, it seems Microsoft has finally realized that there is a need for more than just basic functionality.

When I attended the Microsoft launch of Office 2007 and Vista earlier this year, they showed us a few of the new ‘toys’ available to avid Office users. To be honest, Powerpoint and Word were nice, but the real eye openers for me were Excel and ultimately Access.

Now I’ve done a lot of Access work in the last few years… I’ve built a system for my old company, comprising of a stock management system, customer relationship manager (CRM) system, sales ticketing system and reporting. I’ve helped friends, like Rozz, with some of their freelance and college work in Access. I’ve built the typical CD and DVD library type systems for folks like my Dad. And probably wanted to tear my beard out in working on a majority of them.

Lately, however, I see a light at the end of the tunnel… and for a change it’s not an oncoming freight-train…

Part of my daily job requires sifting through firewall logs files, looking for anomalous traffic, patterns or trends… When you deal with some of the bigger firewalls out there, you can expect to be sifting through 500,000+ lines of data… daunting to anyone except perhaps the most rabid of forensic analysts…

Sure, I could use the ‘fwlogsum’ or ‘CPRules’ utilities available for dealing with Firewall-1 logfiles, but for me, the quickest and easiest way is to export the logs to a .csv file, clean up some of the trash data I don’t require in Excel and then import it into Access. A quick query here and there usually shows me what I need within a minute or two… usually something like this:

SELECT Count([firewalllog.table].Source) AS CountOfSource, [firewalllog.table].Source, [firewalllog.table].Service, [firewalllog.table].Destination
FROM [firewalllog.table]
GROUP BY [firewalllog.table].Source, [firewalllog.table].Service, [firewalllog.table].Destination
ORDER BY Count([firewalllog.table].Source) DESC;

This is after I’ve filtered for dropped traffic in the firewall monitor GUI for instance, otherwise the query would be a bit more cumbersome…

The other thing I’ve used it for is literally eyeball trend analysis with the conditional formatting options for reports. Yesterday I took 60000 lines of logs from a monitoring system, filtered the CPU Load data then applied green, yellow or red cell fill colours to the appropriate cells… one print preview later, showing 9 pages at a time and I began to see the trend quite clearly…

(read top down, left to right - this represents about 48 hours of logs)

A clumsy method perhaps, but a quick and effective one as from that we were able to respond to a situation at a client where we were struggling to get much in the way of any feedback from them.

Access has become, for me at least, the quickest and most reliable method (known to me) to break down a large mass of data into a manageable amount to comprehend. And I can certainly recommend it to anyone now who feels that they would like to get some experience in the lower end of DBA work.

It seems that the love/hate relationship I’ve had with Access these last few years may become tempered with some grudging respect.