Audit Trails and Logging Part I

Question

Audit Trails and Logging Part I

Viewing 6 posts - 16 through 20 (of 20 total)

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

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 1

I guess I'm not sure what you mean by trace files. Are you talking about having some piece of the database/application write data to a separate file and use that for logging?

I was referring to the SQL Server trace files created by sp_trace_create, sp_trace_setevent, etc which is the method SQL Profiler uses.

David

Thomas Keller Ten Centuries Points: 1246 More actions · Answer 2

I've been using trigger-based Audit Trails since SQL6, and every customer I've set them up for has loved them. Although there have been some use cases for workflow reporting and undo, the primary purpose has been tracking who-changed-what-when. Although you have classified this as Blamethrowing, it actually serves many other purposes. For instance, a user asks why their scheduled search did not return an expected result when expected, and I can show them that a) they added a criteria after the matching event had happened, and b) how to figure that out without asking me next time. It also answers questions about how a record got into a certain strange state, when there can be many users using multiple applications... and it can thereby turn up cases that need to be handled better in code.

I agree that the audit trail tables can be tampered with by anyone with permission, but so far I haven't had to satisfy a legal requirement... if I did, I would just keep the log files as a backup in case there was any question.

Also, although there seems to be a consensus that triggers need to be hard-coded to the table structure for speed, and thus rebuilt when the schema changes, I have worked out a way to quickly loop through existing columns, and only save changes, eliminating updates to the same value. I will be interested to see where your next article stands on this.

fmuntean Grasshopper Points: 21 More actions · Answer 3

Hi,

I just want to point you to a free tool for monitoring the SQL Log files.

It is Log Parser currently in v2.2 form Microsoft.

It is using a query engine and can read all kinds of log files and event logs.

It can output to the SQL database so you can build your own reporting on top.

SQL server uses the event log too for sending info so it is not enough to look into the logs but also into the Event log (I ussually start from there).

It would be interesting if you investigate the tool against those $1000 bucks tools.

Florin

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 4

Good tip! Thanks!

--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)

GSquared SSC Guru Points: 260824 More actions · Answer 5

fmuntean (6/9/2008)
Hi,
I just want to point you to a free tool for monitoring the SQL Log files.
It is Log Parser currently in v2.2 form Microsoft.
It is using a query engine and can read all kinds of log files and event logs.
It can output to the SQL database so you can build your own reporting on top.
SQL server uses the event log too for sending info so it is not enough to look into the logs but also into the Event log (I ussually start from there).
It would be interesting if you investigate the tool against those $1000 bucks tools.
Florin

I was actually looking at that product a little while ago. From what I could tell, it could parse the event file, error log, etc., but I can't tell from the web page whether it will read an SQL Server transaction log. Have you tried that? Does it work well for it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Good one Gus, well presented. Looking forward to reading the second article.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden