How to add auditing

Here we will be adding auditing to the Store table. If you need to create a new table, see chapter How to create/edit a database table. The process of adding auditing is very similar for every database table so this is a good example of the whole process.

Using the Audit Script Generator tool

The Audit Script Generator is a simple tool that generates all the SQL code required to add auditing to a database table. It also gives you some control over columns that you wish to use as a filter when viewing the audit logs in the Site Manager.

The tool is included with the development package under Tools > Audit script generator. Run the AuditScriptGenerator.exe. The first dialog you see is the Connect to database dialog. Select the database server that your database belongs to and enter the appropriate credentials to load the database list.

After you successfully log on you will see a list of all databases on that database server. In this case the database containing the Store table is called LSR_Dev.

Loading the table into the Audit Script Generator

After you locate your database, expand it by pressing the + button to get a full list of tables within that database. Locate the Store table in the list of tables. In this case our Store table is called RBOSTORETABLE.

After you find the table, press the Load table button to load the list of columns from the table.

Preparing the audit view log

In order to view the data in the audit table in the Site Manager you must have an underlying view log. This step is optional in order to implement auditing for a table but in order to be able to view the auditing information in the Site Manager this step is necessary.

After you have loaded your table you will see the following information filled out:

Here you must choose to create a view log. Check the Create view log option and fill out the information as it is displayed below.

Here we have chosen to create a view log with the name Store. The singular use of the table name is intentional since this view log will show a specific store record. If we were to create a view log to display records for multiple stores we would have named it Stores in plural. The other options need more explanation:

• View log filter: Here we select the columns that we will use to identify a single record. Here you usually select the primary key fields since that is the most logical choice for a single record filter. Note that the primary key fields are identified with (PK).
• View log columns: Here we select which columns we want to display in the Site Manager.

Generating the SQL scripts

To generate the SQL scripts needed to add auditing to the table, you only have to press the Generate script button.

This will open a new dialog with four tabs (Generate table, Delete statement, Audit trigger, View log), each containing a piece of SQL code:

Using the generated SQL scripts

You will need to go through each tab and perform the following steps for each script:

Generate table script

USE LSPOSNET_Audit
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('[DBO].[RBOSTORETABLELog]') AND TYPE IN ('U'))
BEGIN
CREATE TABLE [dbo].[RBOSTORETABLELog](
	AuditID int NOT NULL IDENTITY (1, 1),
	AuditUserGUID uniqueidentifier NOT NULL,
	AuditUserLogin nvarchar(32) NOT NULL,
	AuditDate datetime NOT NULL,
	[STOREID] [nvarchar] (20) NOT NULL,
	[NAME] [nvarchar] (60) NULL,
	[ADDRESS] [nvarchar] (250) NULL,
	[STREET] [nvarchar] (250) NULL,
	[ZIPCODE] [nvarchar] (10) NULL,
	[CITY] [nvarchar] (60) NULL,
	[COUNTY] [nvarchar] (10) NULL,
	Deleted bit NULL)
	
	alter table dbo.RBOSTORETABLELog add constraint PK_RBOSTORETABLELog
	primary key clustered (AuditID) on [PRIMARY]
	
	create nonclustered index IX_RBOSTORETABLELog_AuditUserGUID ON dbo.RBOSTORETABLELog (AuditUserGUID) ON [PRIMARY]
END
GO
		

This is the script that creates your audit table. To make this script run you need to follow steps that are very similar to the ones covered in chapter How to create/edit a database table.

Adding the Create Table script to DatabaseUtil

Navigate to the DatabaseUtil project which is included in the development package at Core > DataLayer > DatabaseUtil. The DatabaseUtil contains a number of different update scripts but we are interested in the scripts located at SQL Scripts > Update Database Audit. Expand the folder and locate the highest numbered script. In this case it will be 00136-00.

To trigger a database update through the DatabaseUtil our script will need to have a higher number than the current highest script. In this case we should increment the partner version which gives us the script number 00136-01.

We are now ready to add our new script. Right-click on the folder and select Add > New item. When the new item dialog appears, select a file type of Code > Code File name your file 00136-01.sql and press the Add button.

Now select your newly added file and look at the properties for the file. By default the Build Action property will be set to Content, but in order for the DatabaseUtil to run your script it is very important that you change the Build Action property to Embedded Resource. Make this change now if it is set to Content, the properties should look like this:

You have now successfully added a new empty script to the DatabaseUtil project.

Now copy paste code from the Generate table tab into the script and save it.

The delete statement

Delete from RBOSTORETABLELog where AuditDate < @toDate and DATAAREAID = @dataareaID

Now you need to add your delete statement to the main audit logic script. This is a very large sql script that is located in the DatabaseUtil project at SQL Scripts > Logic Scripts Audit and is called 00001-SC Audit Logic.sql.

Open the script and do a search for Delete from, this should locate the first delete statement in the script. There are a lot of delete statements but our new delete statement needs to go to the bottom of the list. Add the delete statement below the last Delete from line.

Adding the Audit Trigger

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Update_RBOSTORETABLE]'))
begin
	drop trigger dbo.Update_RBOSTORETABLE
end

GO

create trigger Update_RBOSTORETABLE
on RBOSTORETABLE after insert,update, delete as

declare @connectionUser uniqueidentifier
declare @sessionUser nvarchar(32)
declare @writeAudit int
Select @writeAudit = Value from SYSTEMSETTINGS where GUID = '17e851c0-3037-11df-9aae-0800200c9a66'

if @writeAudit = 1
begin

	set @connectionUser = CAST(CONTEXT_INFO() as uniqueidentifier)

	if @connectionUser IS null
		begin
			set @sessionUser = SYSTEM_USER
			set @connectionUser = NewID()
		end
	else
		set @sessionUser = ''
	
	declare @DeletedCount int
	declare @InsertedCount int
	
	select @DeletedCount = COUNT(*) FROM DELETED
	select @InsertedCount = COUNT(*) FROM inserted
	
	begin try
		if @DeletedCount > 0 and @InsertedCount = 0
		begin
			insert into LSPOSNET_Audit.dbo.RBOSTORETABLELog (
				AuditUserGUID,
				AuditUserLogin,
				AuditDate,
				STOREID,
				NAME,
				ADDRESS,
				STREET,
				ZIPCODE,
				CITY,
				COUNTY,
				Deleted
				)
			Select 
				@connectionUser, @sessionUser as AuditUserLogin, 
				GETDATE() as AuditDate,
				ins.STOREID,
				ins.NAME,
				ins.ADDRESS,
				ins.STREET,
				ins.ZIPCODE,
				ins.CITY,
				ins.COUNTY,
				1 as Deleted
				From DELETED ins	
		end
		else
		begin
			-- If we got here then we are inserting new or deleting existing
			insert into LSPOSNET_Audit.dbo.RBOSTORETABLELog (
			AuditUserGUID, 
			AuditUserLogin,
			AuditDate,
			STOREID,
			NAME,
			ADDRESS,
			STREET,
			ZIPCODE,
			CITY,
			COUNTY,
			Deleted
			)
		Select 
			@connectionUser, @sessionUser as AuditUserLogin, 
			GETDATE() as AuditDate,
			ins.STOREID,
			ins.NAME,
			ins.ADDRESS,
			ins.STREET,
			ins.ZIPCODE,
			ins.CITY,
			ins.COUNTY,
			0 as Deleted
			From inserted ins
		end	
	end try
	begin catch
	
	end catch

end

GO

Just like the Delete statement we need to add the audit trigger to a specific place in the script 00001-SC Audit Logic.sql. First, locate your delete statement and do a search for the next occurrence of name of the table in the delete statement above yours. In this case you need to search for IMPORTPROFILELINESLog.

Find the next occurrence of IMPORTPROFILELINESLog, this will land you in the last audit trigger defined in the script. In this case you should see this code:

Scroll down until you see the end of the IMPORTPROFILELINESLog audit trigger code, it will end with a single GO keyword. Place a separator line like this just below the GO:

---------------------------------------------------------------------------

Now copy and paste the SQL script from the Audit trigger tab below the separator line.

Adding the View Log

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spAUDIT_ViewLog_Store]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].spAUDIT_ViewLog_Store

GO

create procedure dbo.spAUDIT_ViewLog_Store
(@dataAreaID nvarchar(10),@contextIdentifier nvarchar(255),@user nvarchar(50),@from datetime,@to datetime)
as

--AuditUserLogin = Case
--          when s.Login = '' then m.Login
--          else s.Login
--      end,

select  s.AuditID,
	s.AuditUserGUID,
	m.Login as AuditUserLogin,
	s.AuditUserLogin as AuditUserLogin2,
	s.AuditDate,
	s.STOREID,
	s.NAME,
	s.ADDRESS,
	s.STREET,
	s.ZIPCODE,
	s.CITY,
	s.Deleted
from   dbo.RBOSTORETABLELog s
	left outer join LSPOSNET.dbo.USERS m on m.GUID = s.AuditUserGUID
where  s.STOREID = @contextIdentifier and s.DATAAREAID = @dataAreaID and (s.AuditUserLogin Like @user or m.Login Like @user) and s.AuditDate Between @from and @to

GO

This is the final SQL script we need to add to 00001-SC Audit Logic.sql. Finding the correct place for this script is easy since this goes at the end of the big audit logic script. Scroll all the way to the end of the audit logic script, and paste the script from the View log tab at the end of the script.

Running the scripts

To make your scripts run on your database, simply build and run the Site Manager and you will see the update database dialog when you log on to your database:

Adding code to the Site Manager to view the audit log

In order to view the audit logs in the Site Manager you need to add code to the view that is responsible for editing a single record in the table you are auditing. In this case we want to add this code to the Store View. To add a view, see chapter How to create a simple view .

In the Store plugin navigate to Views > StoreView.cs and open the code behind the view. You will need to add the following code to the view:

public override void GetAuditDescriptors(List<AuditDescriptor> contexts)
{
	contexts.Add(new AuditDescriptor("Store", storeID, Properties.Resources.StoreText, true));

	tabSheetTabs.GetAuditContexts(contexts);
}

This overrides the base function from ViewBase and registers your view for the audit viewer. The first parameter is the name of the view log that we assigned when generating the scripts for our audit table. The next parameter is the view log filter value that in our case is the storeID and corresponds with the ID column that we defined as our filter before generating the scripts for our audit table. The third parameter is the description text we want to appear on the audit view and the last parameter tells the Site Manager if we are viewing an audit log for a single record.

If your view contains tabs, you also need to call tabSheetTabs.GetAuditContexts(contexts) to allow tabs to register their audit contexts.

After you have added this code to the StoreView.cs you can press F6 in the store view and view the audit log for a store: