Auditing

Question

Post reply

Auditing

DBA_Learner

SSCarpal Tunnel

Points: 4228
More actions
April 16, 2015 at 12:49 pm

#317524

Hi,
I am running a trace file to capture logins and would like to get that audited in a table.
I am able to get that all audited in a table in one shot. But, i would like to get the table updated only with RECENT RECORDS in the audit file.
below is my procedure:
insert into #testauditdata
SELECT I.ServerName, I.HostName,I.DatabaseName,I.ApplicationName,I.LoginName,I.StartTime,I.EndTime
,I.RowCounts,I.SessionLoginName,i.TextData
FROM ::fn_trace_gettable('G:\TestAudit\Testaudit.trc', default) I
This inserts complete records from the audit file into table. By this i am getting duplicate data.
But, what I need is if there is any new change in the audit file, how the alone record updates in the table [Most Recent Record]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DBA_Learner SSCarpal Tunnel Points: 4228 More actions · Answer 1

Nevermind.. i just figured it out..

insert into auditorstable

SELECT I.StartTime,I.ServerName, I.HostName,I.DatabaseName,I.ApplicationName,I.LoginName,I.EndTime

,I.RowCounts,I.SessionLoginName,i.TextData

FROM ::fn_trace_gettable('G:\TestAudit\Testaudit.trc', default) I

where I.StartTime in

(SELECT MAX( I.StartTime) FROM ::fn_trace_gettable('G:\TestAudit\Testaudit.trc', default) I

)

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

Very cool. Thanks for posting what you found out. Not too many folks do that. I, for one, appreciate it.:-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DBA_Learner SSCarpal Tunnel Points: 4228 More actions · Answer 3

Thanks Jeff. The only thing which still i am thinking is how frequently I need to run this query as a job.

With that query it provides me max start time info. But, if i schedule the job for every 15 mins, I may get logs of last minute and I will miss the logs of 14 minutes...

I am not sure if there is a way in auditing where it directly updates one of the SQL Table with new changes?