Audit Trails

[gwboolean]

My question pertains to anyone with experience with CFR 21, Part 11. This deals with audit trails, their scope and how to make them effective and efficient.

I am currently adding audit trail capability to an application. Past experience, and current, tell me that an audit trail requires a hell of a lot of memory and a hell of a lot of processing. I have created a audit trail table (no relationships set) that does nothing but record who in the database, what they are doing in the database (changes made to records) and when they are there.

Scope and memory is the first issue. How much detail and what should be included in the audit trail (This is where you need to know CFR 21, Part 11)? For instance, one of the applications is for document handling (kind of like Documentum, but much better than that POS). A document going through the change process would generally have 4 tables that contain what would be considered critical data. In the data transactions from creating a change request record and document record to the point where the records are complete there could be well over a thousand transactions that would need to be recorded to the audit trail. Each one of those transactions equates to an additional record to the audit trail table. Of course the table would have to be frequently archived, which will relieve some of the memory strain. But I am wondering what anyone's thoughts about the scope and detail that should be included in these records (if you have any) such that memory usage could be minimized?

The second issue is processing. As noted above, there are a hell of a lot of transactions occurring with the audit trail table and it would be my guess that a lot of processing time will be/is used for this activity. My current approach is that I have a function that opens the table, creates a new record then drops in whatever data is required to fill out the record then saves the record. The function is invoked at any point in the application that I determine that it is required, like anytime anyone logs in. Is this the only approach available or is there a better way to look at how to more effectively maintain an audit trail?

[techgnome]

I don't know if it helps or not, but here's how it's done in our system. For each table that can be audited (just because it can, doesn't mean it will) there's a shadow table with a suffix "AUDIT" ... so if there's a table CUSTOMERS, the audit table is CUSTOMERSAUDIT .... the structure is nearly identical to the main table with some fields added (for auditing) and some removed (like computed lookup fields) ... the fields added include AUDITKEY RECORDID AUDITACTION and a couple others that escape me at the moment. RECORDID is actually the ID from the main record, AUDITKEY keeps related autid records together (more on that in a moment) and the AUDITACTION tells us what happened - Deleted, Before Update, After Update ... Back to the AUDITKEY, that is what keeps related Before/After Update records together. So before a record is updated, it's copied to the audit table with an AUDITKEY, then after the update, the record is copied again, with the same AUDITKEY.

Now, getting everything into the audit table is part of the trick... it's done through triggers. So every table has an update and delete trigger on it that looks up in the AUDIT table to see if that table has auditing turned on. If it does, it then write out the appropriate audit record depending if it is an update or a delete (we don't bother tracking inserts, but I suppose we could if we needed to).

-tg

[gwboolean]

Whoa, that really sounds like a lot of memory and processing where ever you choose to use it. I think I like the way I have set up better than that. What I finally decided on is this.

I have an Audit table, but it consists of only 7 fields (there are more, but I am not going to use them and will rid myself of them soon). The first is nothing more than an auto-increment number field PK. By the way, the table is not related to any other table. There is no need, but it does easily let anyone who is checking know if there are any missing records (a regulatory apocalypse if occurs). The second field just holds the id of what ever record is subject to the audit trail. The third the change request ID (if the record has one), fourth the login name of whomever is messing around in there, fifth their full name, sixth a time stamp and the last is the big boy that has all of the changes made in a record.

So what I set up is nothing more than a routine. Where ever I want to invoke it I just place the call in the code. If someone gets into that record it will take it's recordings before the person has access, then record again after they leave. It would also be used to record every login onto the system. One thing I find attractive with the description of your system is the idea of having it where ever it could be used and then just add the capability of either turning it on/off.

The only thing that is done with the data is make it visible to whomever has to go through the drudgery of reviewing it and an archive capability, since you end up with a hell of a lot of useless crap when you are done.

Right now, the biggest problem I am facing is how to put the data in there in such a way that it is meaningful to whomever has the misfortune to review it. I think what I am doing keeps the processing down to a minimum, but I see no way around all of the data that accumulates. It would be my guess that this would have to be archived on a weekly basis.

Let me know of any other strategies you might know of.

[techgnome]

Whoa, that really sounds like a lot of memory and processing where ever you choose to use it.

Not really... it's actually quite simple and easy and since the logging is done in a trigger, the data is already there in the Inserted and Deleted meta tables. We don't care what's changed either... and it captures changes regardless of where the change happened - we do have some clients that have IT departments, and sometimes they get in the data and make changes... so with a trigger, it doesn't matter if the change is in the app or in management studio... we capture it. It sounds like a lot, but in reality, it's pretty streamlined:
Check the audit setting for the target table
Get the user based on the connection
If being deleted, select from the Deleted table into the audit table.
If being edited, select from the Deleted table as the Before Edit into the audit table
If being edited, select from the Inserted table as the After Edit into the audit table
done.

The nice thing about it is that it keeps the native datataypes for everything, it's easy to see what's what. The only time... ONLY time in 6 years I've been using this where I've seen a problem with this setup (I should also note, there is a means to purge the audit tables from time to time of needed) is when a client stored high resolution images in a table with auditing turned on. That's the only time I've ever seen a problem with it. Most of the time we leave auditing off on most tables, opting to turn it on only when needed on certain tables... like those dealing with financials.

-tg

[gwboolean]

I like the way that works. However, in most regulated industries data integrity (that is an oxymoron) is sacrosanct, i.e. deletion is NOT a option. That would be a good way to get your business shut down. I hope that when you say purge you mean archive.

Your mention of high resolution drawings brings me to the part of this I have yet to address. All of the information that is not part of the table records, i.e. documents, drawings, reference material, attachments, etc. (associated materials). Because I am currently working with Access, I do not trust their memo field to actually store associated materials in a table field. What I am doing with that now is I have a routine that places all associate material into a directory specific for a record and have not addressed this in terms of audit trail. How would you address an audit trail for the associated material and securing those directories?

I am soon to make the switch to SQL and am wondering if you know whether there is a field type that would be able to handle holding all of the associated material and how that would be done.

[techgnome]

there's two schools of thought... 1) the method that we used in the case of the photos - varbinary field... however, that would not be the way we'd go about it we had our choice... instead look into SQL Server FILESTREAM - https://www.google.com/search?q=sql+server+filestream - it allows you to store documents, related to records, but it stores them on the server's file system rather than in-line inside the database. Instead, it creates a pointer that is stored in the table that then points to the location of the file's bits ... accessing the file takes a little more effort, but it really isn't too terribly difficult. I've never had the chance to use it, but I've seen a lot of examples, and other than just simply changing how you access the field, it's not that bad and should be painless.

(btw: I don't blame you for not trusting memo fields... I wouldn't either.)

-tg

[gwboolean]

Thanks man. I will be looking at that very closely...... as soon as I can get to the change over to SQL. But first, I have to finish this prototype (I don't know why, I am retired and don't have to do a damn thing).