This video shows how to set advanced auditing feature in ApexSQL Trigger that allows showing additional values in the auditing report. Besides the actual columns that are included in the auditing process, a change in one audited column can be bound to a value from any other column (or multiple columns) and all values will be shown in the auditing report when a change occurs. All configuration changes made in the Watches feature require auditing triggers to be re-created, in order for the new configuration to take effect.
In addition to this, operations that are included in the auditing process are the ones affected by the Watches feature. This means if, for example, the Delete operation is not audited, additional values configured in the Watches feature will not be shown if the user performs the Delete operation against an audited column.
Transcript
Hello and welcome to this ApexSQL Trigger video. In this video, we’ll show the Watches feature.
For the purpose of this video, we’ll start with a sample database and with the default auditing architecture installed in a sample database. An auditing plan is not configured nor any of the advanced auditing mechanisms. Installing an auditing architecture and setting up an auditing plan is not in the scope of this video as it is already covered in another video, so we’ll focus on presenting the Watches feature.
Let’s see what the Watches feature is designed for and how can we use it to improve the auditing process. Essentially, the Watches feature means that while auditing one particular column, called the Audit column, we want to have the appropriate value from another column that belongs to the same table called the Watch column, being shown in the auditing report each time a change occurs.
Let’s assume we want to audit changes in the AddressType table, specifically, we want to look for changes made in the rowguid column, and therefore we’ll select it under the Columns tab:
Let’s assume it is a requirement to audit the rowguid column, although values from the rowguid table are not readable. However, using the Watches feature we can bind any change that occurs in the rowguid column to the specific value in any other column that is easier to understand. In this case, let’s assume it is the Name column that has values like Billing, Home, Main Office, etc.
In order to show a value for the Name column whenever a change occurs in the rowguid column, we’ll have to configure this under the Watches tab. Let’s switch to the Watches tab:
Note that we’ll need to have the AddressType table selected in the main grid. Also, any configuration in the panels on the right side affects only selected operations from the main grid. This means that if we set to audit only INSERT and UPDATE operations on a particular table, the Watches feature configured for the specific column will be applied only for the selected operations. In other words, if someone deletes any of the existing values in the rowguid column, we won’t have that change detected by the auditing triggers and at the same time, there will be no additional values for the Name column in a report:
Let’s leave the DELETE operation unchecked for the AddressType table. In order to configure the Watches feature, we’ll choose a column which changes will be tracked. In this case, it is the rowguid column and we’ll pick it from the Audit column drop-down list:
Since we have selected only the rowguid column in the Columns tab, it is the only one being shown in the Watches tab, under the Audit column drop-down list. This list shows only columns that are already selected to be audited:
From the Watch column drop-down list, we need to select any other column from the same table which value we want to bind to a specific change in the Audit column when it occurs. We want to bind the Name column:
Once we have both columns selected, we’ll click the Add button in order to confirm these settings:
This will place the column pair in the list, under the Watches tab:
Similar to this, we can set a combination of any columns where the column on the right side must be selected for auditing in the Columns tab and the value from the Watch column drop-down list will be shown in the auditing report along with a change in the audited column, when a change occurs.
When the configuration steps in the Watches tab are finished, we’ll need to re-create the auditing triggers, in order for these settings to be effective. Anything configured in the Watches tab will not be effective until triggers are re-created. In order to do so, we’ll click the Create button in the main ribbon, under the Triggers group:
Now that we have the auditing architecture set, we can test this by making a change in the AddressType table, specifically in the rowguid column. We’ll do that with the following script:
USE AdventureWorks2014 GO UPDATE Person.AddressType SET rowguid = 'B84F78B1-4EFE-4A0E-8CB7-70E9F112F886' WHERE rowguid = 'C95F78B1-4EFE-4A0E-8CB7-70E9F112F886' GO
Let’s review the auditing report, to see what is shown if we changed just a single value in the rowguid column. There are two records in the report, the first one is for the actual change that occurred in the rowguid column, and the second one is due to a configuration of the Watches feature and it shows a value for the Name column related to the changed value in the rowguid column:
Querying the AddressType table, we can easily confirm this, using the following SQL script:
SELECT [Name],[rowguid] FROM Person.AddressType
Looking at the result of the executed query, we can confirm that the changed value in the rowguid column is the first one on the list, and that it belongs to the same row where the value of the Name column is Billing and that is exactly what is shown in the report:
This is a simple example to show how to turn almost useless auditing report into something that can be easier to review. Translated to a real-life example, any changes that do not have a value to a person who is reviewing a report could be bound to another value from the same table that is easier to understand.
What we did previously is that we have bound the appropriate value from one column to a change that occurred in another column of the same table. We can configure the Watches feature in a way to include values from multiple columns to be shown, when a change occurs in a single column. This means that we can choose the rowguid column again, from the Audit column drop-down list, and to pick any other column as its pair, from the Watch column drop-down list. Let’s pick the ModifiedDate column and created another pair, by clicking the Add button in the Watches tab:
When we made a change in the Watches feature configuration, auditing triggers must be re-created in order for the new changes to take effect. We’ll click the Create button again:
Let’s make another change in the rowguid column using the following SQL script:
USE AdventureWorks2014 GO UPDATE Person.AddressType SET rowguid = '25CB3088-4345-47C4-86C5-17B535133D1E' WHERE rowguid = '24CB3088-4345-47C4-86C5-17B535133D1E' GO
Now, let’s see what the auditing report will show:
The auditing report shows three records, the first one is for the actual change we made in the rowguid column, the second one is for the first column pair set in the Watches tab where we wanted to show the value of the Name column, and the third one is for another column pair from the Watches tab where we wanted to add a value from the ModifiedDate column in the report once the value from the rowguid column is changed.
This is an example of setting up the Watches feature on a single table. Similar to this, auditing of any other table can be customized in a way additional values are shown in the report when a change occurs in the specific columns.
Any configuration can be changed during the auditing process. A pair of columns can be deleted from the Watches tab, using the Delete button:
Similar to adding column pairs, once a pair of columns is removed, auditing triggers must be re-created in order for the changes to take effect.
Since we have selected INSERT and UPDATE operations to be audited in this case, let’s verify that deleting any value from the rowguid column will not be detected and shown in the report. We’ll execute the following query that deletes a complete record in the AddressType table including a value in the rowguid column:
DELETE FROM Person.AddressType WHERE [Name] like ‘Home’
Looking at the report, we can confirm that there is no additional information being shown. This is a confirmation that operations that are not previously selected to be audited will not be detected as well as additional values will not be shown regardless of the Watches feature configuration:
To summarize, we have shown how to add values from non-audited columns to be shown in the auditing report whenever a change occurs in an audited column. If there is a need, values from more than one column can be added to the auditing report while auditing only a single column. It is a requirement that the Audit column is checked under the Columns tab so it can be used in the Watches tab as well. Re-creating auditing triggers is mandatory whenever configuration changes are made in the Watches tab, in order for these changes to take effect.
Thanks for watching. For more information please visit apexsql.com
