I had reason to need to to be able to audit changes to a Reporting services site. Whilst there is an audit trail for tracking execution or reports there isn’t one for changes to the site. The sorts of changes I was interested in were moving reports or changing visibility settings.
These settings are predominantly held in the Catalog table in the ReportServer database so I decided to add an Audit trail and a trigger to the catalog to track the changes.
The explanation below is focused around this scenario but should be easily adaptable for any table where you want to audit changes.
First I created a table for my audit data. CatalogHistory with, in this instance, the following columns
- ItemID uniqueidentifier,
- ModifiedBy uniqueidentifier,
- ModifiedDate DateTime,
- [Name] Varchar(100), –This is the name of the object that has been changed
- ColsUpdated VarBinary(100), –This is used to store the binary data returned by Columns_Updated() function
- ColNames — This is the XML with the names of the columns where data has changed
The only way to find out which columns have had data in them changed is to use the SQL function Columns_Updated(). This however returns a binary number where the 1s and 0 identify which column has been changed which is not very useful.
So I then created a user defined function that will return an XML string listing the column names that have had data in them changed.
It comes out in the format
<C updated_column=”Description” />< <C updated_column=”ModifiedByID” /><C updated_column=”ModifiedDate” />
I am also storing the binary data as a secondary check of the XML, not that I am paranoid or anything!
The code for the Function is:
Having got the function I then created a trigger on the Catalog Table
I must give credit to the following articles that gave me the pointers – and code to achieve this
Without them the whole process would have taken much longer.