Posted by: Charles Maitland | Friday 7 March, 2008

Audit Logging changes to data in SQL 2005 and Reporting services

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:

 
 
-- Inputs 
-- @Tablename the name to get the changed columns for
-- @ColsUpdated the varBinary data that is produced by the Columns_Updated() function.
--    This needs to be passed in as the Columns_Updated() function wont return the right result in this scope
-- Outputs
-- An XML in the form <C updated_column="Property" /><C updated_column="Description" /> 
--    If the reult shows no records changed then the XML is <root></root>
 
 
Create Function ChangedColumns (@Tablename Varchar(255), @ColsUpdated VarBinary(100)) Returns XML
As
Begin
DECLARE @i AS int, @num_cols AS int
DECLARE @UpdCols TABLE(ordinal_position int NOT NULL PRIMARY KEY) --Decalre a table variable
Declare @FieldsXML XMl
 
--First get the number of cols in the table. This is needed because if there are more than 8 we need to
--do the binary changed comparison on each Byte at a time
SET @num_cols =
 (SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = @Tablename)
 
SET @i = 1
WHILE @i <= @num_cols -- Loop through the columns and do the binary comparison
BEGIN
 
  IF (SUBSTRING(@ColsUpdated,(@i - 1) / 8 + 1, 1))
       & POWER(2, (@i - 1) % 8 ) = POWER(2, (@i - 1) % 8 )
    INSERT INTO @UpdCols VALUES(@i) -- Insert into the Table Variable the column numbers if it they have changed
 
  SET @i = @i + 1
 
END
 
--Populate the xml variable with the resulting names based on a join between our table variable and INFORMATION_SCHEMA.COLUMNS
 
Set @FieldsXML = SELECT COLUMN_NAME AS updated_column
FROM INFORMATION_SCHEMA.COLUMNS AS C JOIN @UpdCols AS U
  ON C.ORDINAL_POSITION = U.ordinal_position
WHERE TABLE_SCHEMA = 'dbo'
  AND TABLE_NAME = @Tablename
ORDER BY C.ORDINAL_POSITION
FOR XML AUTO)
 
--Return the resulting XML
return isnull(@FieldsXML, '<root></root>')
End

Having got the function I then created a trigger on the Catalog Table

Create  Trigger tg_ChangeHistory
on [Catalog]
After Update, Insert
as
 
Insert CatalogHistory (
ItemID, --GUID
ModifiedBy, --GUID
ModifiedDate, --DateTime
[Name], -- Varchar(100)
ColsUpdated, --VarBinary
ColNames --XML
)
Select 
ItemID, -- From the source table
ModifiedByID, -- From the source table
ModifiedDate, -- From the source table
[Name], -- From the source table
-- Call the SQL Function for the Binary data 
-- this is not strictly needed but provides an additional checkpoint in case of attempted edits of the XML
Columns_Updated(), 
--Call the User Defined function passing the table name and the binary change result
--NB... NOTE THE DBO in front of the Function name - this is needed
dbo.ChangedColumns('Catalog',Columns_Updated() )
 from Inserted

 

I must give credit to the following articles that gave me the pointers – and code to achieve this

http://www.sqlmag.com/Article/ArticleID/26629/sql_server_26629.html

http://articles.techrepublic.com.com/5100-9592-6175865.html#

Without them the whole process would have taken much longer.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: