Database Audit Logs, Joel on Software, Code Handouts, and More

Scott Mitchell


Audit Database Changes
Blogs of Note
Print and Export Your Code
The Bookshelf

Audit Database Changes

Database systems like Microsoft® SQL Server® have long supported triggers—developer-created scripts that automatically execute after or in place of INSERT, UPDATE, and DELETE statements. Because triggers can access the data modified by INSERT, UPDATE, or DELETE, they are an excellent tool for recording data changes to an audit log. Audit logs are essential for applications working with sensitive or critical data, as they keep track of the date and time a data change was made, along with the user who made it. In fact, many financial and healthcare applications must by law employ audit logs. Plus, audit logs are helpful for debugging because they provide a window into the database state at the time that an exception occurs.

While you can certainly design, build, and maintain a custom audit log, doing so requires a substantial amount of time and effort. It is usually more cost-effective to use a third-party product, such as OmniAudit version 1.11, by Krell Software.

OmniAudit makes implementing an audit log as simple as point and click. From the manager interface, select the database to work with and then choose the tables and columns to audit. For each audited column, you can specify that the column value be recorded in the audit log either when that value changes or when any other column value in the associated row changes. What's more, you can configure OmniAudit to log data from related tables as well.

After configuring the audit log, OmniAudit builds the creation scripts for the necessary log tables and triggers. Once these tables and triggers have been created on the database, any modifications to the specified tables or columns are automatically and transparently recorded to the audit log! Each audit log entry includes all of the essential information, including the date and time of the modification along with the names of the user, workstation, and application that made the modification.

Because the audit log can quickly grow to an unwieldy size when there are frequent changes, OmniAudit can be configured to periodically purge audit log entries older than a certain number of days or weeks. To view the log, use the OmniAudit Log Viewer, which provides a variety of filtering mechanisms. You can narrow the displayed log entries by a particular table or column, a specific user, a date range, or the type of operation performed. The filtering choices are a bit limiting—you can't filter on more than one table, column, or user, for instance—but they will probably be quite sufficient for most users' needs. Best of all, the Log Viewer requires no knowledge of SQL, so managers and other non-technical resources can review the audit log.

Price: $479 for a single-user license.


Viewing a Log in OmniAudit Log Viewer

Viewing a Log in OmniAudit Log Viewer  (Click the image for a larger view)

Blogs of Note

Why do so many software projects fail? What 12 factors can you use to quickly rate the likelihood of success for a team of developers? What are the best ways to educate and train people in the art of software development, and how does that compare to what is being taught in university programs today? These are the sorts of topics addressed by Joel Spolsky on his blog, Joel on Software. Joel has programmed, architected, and managed in a variety of settings; he began his software career as a program manager for the Excel® team at Microsoft. In 2000, he cofounded FogCreek Software, which makes FogBugz, a popular project management application.

As Joel explains it, "Management's first responsibility is to create an abstraction layer for developers: to create the infrastructure so that programmers really just have to program." Developers work best when they can focus on programming and are given a quiet, well-lit and well-equipped work environment. Management's task is to find smart and motivated developers, keep distractions to a minimum, and place the team in an environment to succeed.

Of course, a team of happy and focused programmers does not guarantee a successful software project, but it is an important ingredient. There are other operational and personnel factors that contribute to the ultimate success or failure of a project, which Joel has distilled to 12 checkpoints. How does your company's policies and practices compare to those outlined in The Joel Test: 12 Steps to Better Code?

While much of Joel on Software is a high-level look at the software industry at large, there is plenty of content for developers, too. Joel has lots to say about user interface design, testing, and deployment. And in addition to the regularly updated blog, which is translated into more than 30 languages, there are discussion boards on topics ranging from the business of software to Microsoft .NET Framework development.


Joel Spolsky's Blog

Joel Spolsky's Blog**  **(Click the image for a larger view)

Print and Export Your Code

I recently gave a talk to a user group on an open-source ASP.NET application and wanted to provide the application's source code as a handout to the attendees. Visual Studio® makes it easy enough to print the contents of a single file—just open the file and select the Print option from the File menu. However, printing one file at a time is a tedious process when there are dozens of files to print. Furthermore, Visual Studio offers few print-related options.

A better option is to use StarPrint's VSNETcodePrint. VSNETcodePrint is a Visual Studio add-in that can print Visual Basic®, C#, J#, and ASP.NET code and markup. Once installed, it is accessible through a menu item in the Visual Studio IDE. In addition to printing, VSNETcodePrint can export code to an RTF or PDF document, or to a Web page.

With the click of a button you can print or export the entire contents of a solution. There's no need to open each file. Similarly, one click is all it takes to print or export the contents of an entire project, namespace, class, or selected text.

VSNETCodePrint offers a variety of print-related options, including: portrait and landscape orientations, multiple columns, custom page headers and footers, line numbering, syntax formatting, connection lines, and line spacing.

Connection lines clearly illustrate what lines of code belong to the same statement block. And line numbering can be configured to reset at certain points, such as the start of each new procedure or the start of each new page. There are also options to add a cover sheet, to watermark the printed or exported content, and to include screen shots or other image files in the printout.

In addition to VSNETCodePrint, StarPrint also offers similar products for printing and exporting database objects from Microsoft SQL Server.

Price: $59.


The Bookshelf

The Microsoft ASP.NET AJAX framework simplifies the process of building AJAX-enabled Web applications by providing a rich and powerful, high-level API over the multitude of low-level interactions that must seamlessly occur between the client and server. Any book about the ASP.NET AJAX framework must balance the low- and high-level details. The space devoted to client-side issues like the XMLHttpRequest object and JSON must be weighed against coverage of the framework's Web controls and client- and server-side APIs. One book that does an outstanding job in presenting the framework in a balanced and accessible context is ASP.NET AJAX In Action by Alessandro Gallo, David Barkol, and Rama Krishna Vavilala.

ASP.NET AJAX In Action starts from the low-level details and works its way up. The book is divided into four sections. The first covers AJAX fundamentals, the ASP.NET AJAX framework, and the UpdatePanel control. There are plenty of simple yet interesting examples using the framework's client- and server-side capabilities. There's also an excellent chapter in this section that covers JavaScript concepts, syntax, and functionality that are paramount for AJAX developers.

The second section delves into some more advanced AJAX scenarios, exploring the UpdatePanel and client-side functionality in much greater depth. This part of the book also introduces the AJAX Control Toolkit, which is an additional set of Web controls for accomplishing many common AJAX scenarios.

The third section looks at ASP.NET AJAX Futures, which is a preview of upcoming AJAX and client-side functionality. And the final section looks at how to implement common AJAX patterns.

ASP.NET AJAX In Action is not intended for beginning Web developers, as it assumes a certain level of familiarity with how to build ASP.NET applications, but this book is accessible regardless of past client-side or AJAX development experience.

Price: $44.99.


  (Click the image for a larger view)

Send your questions and comments for Scott to toolsmm@microsoft.com.

Scott Mitchell, author of numerous books and founder of 4GuysFromRolla.com, is an MVP who has been working with Microsoft Web technologies since 1998. Scott is an independent consultant, trainer, and writer. Reach him at Mitchell@4guysfromrolla.com or via his blog at ScottOnWriting.NET.