Improving the Readability of your Audit data

2009-12-09T16:57:00.054-05:00

This article describes how to use two of advanced features of ApexSQL Audit - watches and lookups. These features help improve the readability of your audit data by:

a) Adding fields to your audit data that can be easily recognized by the users viewing your audit reports. For example, you can show the employee name of an audited row even if the employee name wasn't the actual column that was changed
b) Showing a more readable supporting value to an audited field. For example, if you're auditing a store id, showing "Ajax Health Store" (the store name) is better than showing 5568 (the store id itself).

What is a Watch?

A Watch is a field selected to be added to the audit log when another field in a table is changed (inserted, updated, deleted) even if the Watched field, itself, isn't. Multiple Watch fields can be set up for an audited table. Several different Watch fields can be added to a single audited field. And a single Watch field can be associated with multiple audited fields in the same column.

Why use Watches?

Watches allow you to add readily recognizable fields to any audit change which is helpful for people looking at reports. For example, no matter the change is in Sales you can see the Store Name for example, even if the Store itself was not changed. This makes audit reports more readable.

How to Create a Watch?

Watches are added at the special Watch panel. The Watch panel can be accessed from the View Menu and it is in the same panel group as Field Grid, Row Key, and Lookups. To access it from there, just select the Panel Tab for "Watches".

Example

To explain it better we'll use one example. Let assume that we have table Sales.SpecialOffer with the following structure:

Now, let assume that we want to audit changes in Discount Percents. This can be easily done by adding this column to audited columns.

Every change on this column will be included to report:

Now we can see that change was made on column DiscountPct and that old value was 10 and now it's 15. But we don't have additional information. We only have key - 2.

To add more information, we can use watch. So, every time when change happens, we want to beside these information have another one - description to make it more readable. This must be added every time when DiscoundPct is changed.

We should set Audit Field Name to DiscountPct and Watch Field Name to Description.

To make this working, we must recreate triggers.

Now, if DiscountPct is changed, we will see that Cup Sale, DVD Sale and Door Handle Sale discount is changed.

Lookup is another very useful possibility. This makes reports even more readable.

What is a Lookup?

Lookups are a powerful feature of ApexSQL Audit that allows you to show the Literal, from a related table, along with the Value of a field that was audited. This way a more human readable value can be seen in the audit reports. For example "Ajax Health Store" (the store name) is much better than 5568 (the store id).

Lookups are associated with the audited field so they will be triggered any time a row is inserted or deleted. They will be triggered if the specific column is changed in an update.

Using lookups, you can define an ad hoc relationship between columns from different tables. As with watch columns, if no audited columns are affected by the data change, then none of the lookup values are captured either.

Why use Lookups?

Lookups provide Human Readable Audit data for reporting purposes. For example, there may be a numeric ID field in a table that was changed but would be meaningless to someone viewing the audit report. By adding a Lookup though, the literal, from the related code table, can be shown in addition to the ID field.

How to Create a Lookup?

Lookups are added at the special Lookups panel. The Lookups panel can be accessed from the View Menu and it is in the same panel group as Field Grid, Row Key, and Watches. To access it from there, just select the Panel Tab for "Lookups".

Example

To explain it better, let's see another simple example.
Let assume that we have two tables Person.Person and Person.EmailAddress and they are connected via foreign key:

Now, let say we want to audit all newly added Email Addresses. This is easy to achieve. We only need to set insert trigger to Person.EmailAddress table.

If we add new email address, report will contain:

Also, we want to know real user name which is bound this email. We will achieve this by using lookups:

And, after recreating triggers, every new mail will be shown like this:

But, there is more space for improvements. You can combine field into custom expression, so if you want to see full name with First and Last name, you can do this as follows:

And report will contain full name like this:

Power User Blog

Improving the Readability of your Audit data