Alternative Transaction Logging Technique for Faster Legal/Exacting Searches of Custom Application Databases

Due to developments over the last several years in compliance (due to the 2007 financial fallout) and internal auditing (due to the increase in data breaches), it is becoming much more challenging for organizations to address the time and cost of discovery and documentation.  These searches may require exact information of exactly what was done, by who, when, on which server and from what station and in what sequence.  Given that searches may require historical queries going back as long as 12 or more years (due to compliance requirements), and the tendency for enterprise systems to evolve, this may require thousands of man-hours to reload the appropriate system backups (if they are available) and roll through transaction logs, all required to done by certifiably competent specialists, with lawyers and auditors looking over their shoulders reviewing everything to ensure that all requirements have been met.


As far back as 2001 I had been bothered by this issue and have been mulling over practical solutions but kept putting the problem back on the shelf as other projects took priority.  Finally, a year or so ago I  had to address this specific issue again while providing consulting to a startup here in Chicago.  Due to the nature of the business, it was imperative that they have the ability to meet every possibly auditing and search need with the minimum cost.  The solution had to be universal to every line of business application we employed, and work across database and server platforms.  It also had to have a structure that was flexible enough to survive unaltered into the foreseeable future.


While the startup never launched, and we never got any farther than basic workflows for the applications, I finally found a solution to this conundrum when I started writing a ITILv3 support package last year.


To make it work required:

• Implementation in a relational database environment.
• No routine customization (common code for the every use for the entire implementation).
• Searchable variable metadata content.
• Needed to work seamlessly for complex transactions.
• Needed to integrate with the RAD tools I was using.

I began by selecting a suitable RAD tool which employed a customizable coding template in a language with which I was comfortable.  For me this was IronSpeed Designer.  This RAD tool permits the user to build fairly complex Web applications from MS SQL, Oracle and other databases in either the VB or C# language.  As it is template driven, using boilerplate code for all components (presentation, data view, business rules, etc.), and can address complex views, it was the perfect tool for me.


I started by determining the audit log structure.  This is short synopsis of what I came up with as the base requirements:

• User Identification
• User ID
• User SID
• User Name
• Workstation ID
• HostName
• IP
• Server Information
• IP
• HostName
• Time/Date of Transaction
• Package Name
• Originating Program
• Name
• Version
• Last Update Date
• Database Platform
• Database Name
• Transaction Information
• Table Name
• Transaction Type
• Key Field Names
• Key Field Data
• Changed Field Names
• Changed Field Data

In order to address the variable number of elements and their contents in the structure, in addition to the fact that I might want to add to it in the future, I decided to fold the polymorphic data into an XML datagram.  This datagram was then treated as a single large binary field within the relational record.


On the programming side there were several challenges:

• Writing the necessary code only once (all transactions employ the same code).
• Integrating it into the support libraries for IronSpeed
• Ensuring that proper error processing was not compromised (critical!).Writing the code in VB (IronSpeed source code libraries were provided in VB only).

I was able to solve the first challenge rather elegantly, thanks to the excellent engineering of the IronSpeed team, by employing reflection at the core data access library routine level.  This permitted a data-structure invariant solution that worked for both simple and complex (multi-table) transactions.


Integrating this custom module was very straight forward once I waded through the reference material on how to do this with IronSpeed.


Ensuring that the proper error processing was not compromised was simply a matter of ensuring that all possible errors were addressed, even if it was with an unknown error code.  No direct exits to the debugger permitted.


The most challenging aspect turned out writing the new support library in VB which is a language that I despise.




By assigning relational table fields to  the relevant search fields (database ,table name, user, date/time, server, package, IP, etc), one can now do a fast search across entire platforms.  This search capability could even be extended to the XML datagram for an appropriate string search (say a person's name).  Once the desired dataset has been selected, a reporting program can decode the XML to display the relevant fields in a single comprehensive multi-tiered report/spreadsheet or dataset as appropriate.




One element my specific implementation lacked was recording the key element data for new records.  As I was doing this in VB as opposed to TSQL, there would have been significant overhead in moving the data between the database server, webserver and back again to accomplish this.  If it were done in the core stored procedures for the tables, than it would be practical to add those fields to the audit log.




If you are interested in researching this further, you can find the relevant libraries and support documentation on the IronSpeed site at:




http://sjc.ironspeed.com/search?searchid=33017161&showas=post