Getting SQL server’s data into sys logs:   Leave a comment


One of our clients had enabled C2 auditing on SQL server and as a result the logs are generated in trace file format at the specified location. As per our client’s requirement the output of the trace file should be stored in the sys logs (in the Event logs) or SQL server error logs. So we used the following method to get the SQL server data in to the sys logs:

1. Created a table(audit table) to store the output of a trace file


CREATE TABLE c2audit (Complete varchar(8000))


2. Created an insert trigger which fires for every row that has been inserted in to the audit table:


CREATE TRIGGER [c2audit_Insert_Trigger] ON c2audit

FOR INSERT

AS

declare @count int, @maxcount int, @ErrorNumber int

set @ErrorNumber=50001

create table #temp (ID int identity(1,1), complete nvarchar(4000))

insert into #temp (complete) (select complete from INSERTED)

select @maxcount=max(id) from #temp

set @count =1

while @count<=@maxcount

Begin

declare @complete nvarchar(4000), @length int, @lencount int, @innercount int, @substring int

select @length=len(complete) from #temp where ID=@count

set @lencount =@length/250

set @innercount =0

set @substring=1

print @lencount

print @count

WHILE   @INNERCOUNT<=@LENCOUNT

BEGIN

select @complete=SUBSTRING(complete, @substring, 250) from #temp where ID=@count

Exec master..xp_logevent @ErrorNumber, @complete, informational

set @innercount=@innercount+1

set @substring=@substring+250

print @innercount

print @substring

End

set @count=@count+1

set @ErrorNumber=@ErrorNumber+1

End

GO

  • The insert script will split the entire message that has greater than 255 characters into different parts and then it prints one by one in to the application log.
  • As we know the maximum length of characters that could be printed to Application log from the SQL server is 255. So we have followed the below logic to split the string (whose length is greater than 255) in to different parts and then get it printed on the application log.
  • In our below example the length of the message is 761 characters, so we had split the message into 4 parts (761/250 +1=4). They all have error number as a common factor among them. In our example it is Error: 50003. For the next consecutive messages the error number would be incremented by 1 (Eg: 50004)

Actual Message:

TextData-INSERT INTO LOGTABLE (SourceIP, ClientTimestamp, ClientMS, ServerTimestamp, SessionID, Component, EventID, Severity, Grouping, Originator, OriginatorType, Target, TargetType, SubTarget, Text1, Text2, Text3, Value1, Value2, Value3, MIMEType,DataSize, Data, Signature) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 , @P18 ,;DatabaseID-8;TransactionID-;NTUserName-;NTDomainName-;HostName-HOST123;ApplicationName-APP;LoginName-audituser;SPID-136;StartTime-Sep 13 2008 11:42PM;EndTime-;Permissions-8;EventSubClass-1;Success-1;ServerName- SERVER123;EventClass-114;ObjectType-17;ObjectName-nauditlog;DatabaseName-AuditDB;OwnerName-dbo;DBUserName-dbo;ColumnPermissions-0;

Messages on the application log:

  • Error: 50003, Severity: 10, State: 1

TextData-INSERT INTO LOGTABLE (SourceIP, ClientTimestamp, ClientMS, ServerTimestamp, SessionID, Component, EventID, Severity, Grouping, Originator, OriginatorType, Target, TargetType, SubTarget, Text1, Text2, Text3, Value1, Value2, Value3, MIMEType,

  • Error: 50003, Severity: 10, State: 1

DataSize, Data, Signature) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 , @P18 ,;DatabaseID-8;TransactionID-;NTUserName-;NTDomainName-;HostName-HOST123;ApplicationNam

  • Error: 50003, Severity: 10, State: 1

e-APP;LoginName-audituser;SPID-136;StartTime-Sep 13 2008 11:42PM;EndTime-;Permissions-8;EventSubClass-1;Success-1;ServerName-SERVER123;EventClass-114;ObjectType-17;ObjectName-nauditlog;DatabaseName-AuditDB;OwnerName-dbo;DBUserName-dbo;ColumnPe

  • Error: 50003, Severity: 10, State: 1

rmissions-0;

3.  The below script should be fired whenever we would like to log the trace file output in to the sys logs.


INSERT INTO c2audit  (Complete)

(SELECT  'TextData-'+ isnull(convert (varchar(400),TextData),'') +';'+

'DatabaseID-'+ isnull(convert (varchar,DatabaseID),'') +';'+

'TransactionID-'+ isnull(convert (varchar,TransactionID),'')+';'+

'NTUserName-'+ isnull(NTUserName,'')+';'+

'NTDomainName-'+ isnull(NTDomainName,'')+';'+

'HostName-'+ isnull(HostName,'')+';'+

'ApplicationName-'+ isnull(ApplicationName,'')+';'+

'LoginName-'+ isnull(LoginName,'')+';'+

'SPID-'+ isnull(convert (varchar,SPID),'')+';'+

'StartTime-'+ isnull(convert (varchar,StartTime),'') +';'+

'EndTime-'+ isnull(convert (varchar,EndTime),'')+';'+

'Permissions-'+ isnull(convert (varchar,Permissions),'')+';'+

'EventSubClass-'+ isnull(convert (varchar,EventSubClass),'')+';'+

'Success-'+ isnull(convert (varchar,Success),'')+';'+

'ServerName-'+ isnull(ServerName,'')+';'+

'EventClass-'+ isnull(convert (varchar,EventClass),'')+';'+

'ObjectType-'+ isnull(convert (varchar,ObjectType),'')+';'+

'ObjectName-'+ isnull(ObjectName,'')+';'+

'DatabaseName-'+ isnull(DatabaseName,'')+';'+

'OwnerName-'+ isnull(OwnerName,'')+';'+

'DBUserName-'+ isnull(DBUserName,'')+';'+

--convert (varbinary,LoginSid),'')+';'+

'ColumnPermissions-'+ isnull(convert (varchar,ColumnPermissions),'')+';'

FROM ::fn_trace_gettable(‘Trace File Name’, default))

Posted June 7, 2012 by CK in T-SQL

Tagged with , , , ,

Leave a Comment

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

%d bloggers like this: