Audit log issue on insert?

Jun 21, 2012 at 8:03 PM

Hello,

Very impressed with this tool. Thank you. 

When inserting records, the Audit only logs only the insert of the PrimaryKey and no other values that were inserted within the same transaction. This can be replicated by the following:

1) Create test table:

 

CREATE TABLE [dbo].[AuditTest](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[Surname] [nvarchar](50) NULL,
 CONSTRAINT [PK_AuditTestV] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

 

2) Initiate AutoAudit on the table:

 

EXEC pAutoAudit @SchemaName = dbo, @TableName = [AuditTest]

 

 

3) Insert a record into the table:

 

INSERT INTO AuditTest (FirstName, Surname)SELECT 'Joe', 'Bloggs'

 

 

Only the PrimaryKey field is logged in the audit table, where I would have expected entries also for FirstName and Surname

AuditID 164614
AuditDate 41:28.0
HostName WIN-XXXXXXXXXX
SysUser sa
Application Microsoft SQL Server Management Studio - Query
TableName dbo.AuditTest
Operation i
SQLStatement INSERT INTO AuditTest (FirstName, Surname)  SELECT 'Joe', 'Bloggs'  SELECT 'John', 'Smith'
PrimaryKey 1
RowDescription  
SecondaryRow  
ColumnName [ID]
OldValue  
NewValue 1
RowVersion 1


Is this expected behavior or a bug?

Many thanks.

Tom

Coordinator
Jun 22, 2012 at 2:11 AM

This is exactly by design. The insert event plus any updates is enough information to audit the table. The newest version of AutoAudit has a paramenter switch to change the insert logging to verbose, which logs every column.

-Paul Nielsen

Founder, CEO | Ministry Weaver, Inc

8916 Tutt Blvd. | Colorado Springs, CO 80924 | (719) 660-5821 | pauln@sqlserverbible.com

This email transmission contains information belonging to the sender that may be CONFIDENTIAL AND LEGALLY PRIVILEGED. This information is intended only for the person to whom it is directed as indicated above. If you are not the intended recipient, any disclosure, copying, distribution or use of the information contained in or with this transmittal is strictly prohibited. If you have received this transmission in error, please delete immediately. Thank you.

From: tompat123 [email removed]
Sent: Thursday, June 21, 2012 1:03 PM
To: pauln@sqlserverbible.com
Subject: Audit log issue on insert? [AutoAudit:360527]

From: tompat123

Hello,

Very impressed with this tool. Thank you.

When inserting records, the Audit only logs only the insert of the PrimaryKey and no other values that were inserted within the same transaction. This can be replicated by the following:

1) Create test table:

CREATE TABLE [dbo].[AuditTest](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [FirstName] [nvarchar](50) NULL,
        [Surname] [nvarchar](50) NULL,
 CONSTRAINT [PK_AuditTestV] PRIMARY KEY CLUSTERED 
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2) Initiate AutoAudit on the table:

EXEC pAutoAudit @SchemaName = dbo, @TableName = [AuditTest]

3) Insert a record into the table:

INSERT INTO AuditTest (FirstName, Surname)SELECT 'Joe', 'Bloggs'

Only the PrimaryKey field is logged in the audit table, where I would have expected entries also for FirstName and Surname

AuditID

164614

AuditDate

41:28.0

HostName

WIN-XXXXXXXXXX

SysUser

sa

Application

Microsoft SQL Server Management Studio - Query

TableName

dbo.AuditTest

Operation

i

SQLStatement

INSERT INTO AuditTest (FirstName, Surname) SELECT 'Joe', 'Bloggs' SELECT 'John', 'Smith'

PrimaryKey

1

RowDescription

SecondaryRow

ColumnName

[ID]

OldValue

NewValue

1

RowVersion

1


Is this expected behavior or a bug?

Many thanks.

Tom

Jun 22, 2012 at 12:36 PM

Thanks for your quick reply. 

I cannot see the parameter in the current version. Is this a forthcoming version?

Best regards,

Tom

Coordinator
Jun 22, 2012 at 2:21 PM

http://autoaudit.codeplex.com/releases/view/42619

version 2.00h

-Paul Nielsen

Founder, CEO | Ministry Weaver, Inc

8916 Tutt Blvd. | Colorado Springs, CO 80924 | (719) 660-5821 | pauln@sqlserverbible.com

This email transmission contains information belonging to the sender that may be CONFIDENTIAL AND LEGALLY PRIVILEGED. This information is intended only for the person to whom it is directed as indicated above. If you are not the intended recipient, any disclosure, copying, distribution or use of the information contained in or with this transmittal is strictly prohibited. If you have received this transmission in error, please delete immediately. Thank you.

From: tompat123 [email removed]
Sent: Friday, June 22, 2012 5:36 AM
To: pauln@sqlserverbible.com
Subject: Re: Audit log issue on insert? [AutoAudit:360527]

From: tompat123

Thanks for your quick reply.

I cannot see the parameter in the current version. Is this a forthcoming version?

Best regards,

Tom

Coordinator
Oct 31, 2013 at 6:11 PM
The logging level setting parameters are in the pAutoAudit stored procedure.

....
@LogInsert tinyint = 2, -- 0 = nothing, 1 = header only, 2 = header and detail
@LogUpdate tinyint = 2, -- 0 = nothing, 1 = header only, 2 = header and detail
@LogDelete tinyint = 2 -- 0 = nothing, 1 = header only, 2 = header and detail

If you want to see the values for all columns logged, make sure you set the values to 2 (default) for insert, update and delete.

You may download the latest release (3.20e) for all the latest features.

John
Marked as answer by JohnSigouin on 10/31/2013 at 10:11 AM