[AccessD] Audit Trail Suggestions

[James Button]

>From the description it has some advantages over many other techniques in that:

The log is not dependent on the table definitions remaining unchanged
Indeed it would probably record changes to the tables recording table and stored
processes details.
It would also (I suspect) keep details of the changes made within the entire
database in the order that they were made.
That would save manually having to work out the co-ordination that happened ( or
didn't) between updates to different tables.

Re decoding the log -
You may have some trouble with the sheer volume of recorded data - file size and
number of lines.
I suspect that it would be fairly simple to create a script to parse the log and
post details from a set period into an excel spreadsheet for examination - excel
being a fairly good vehicle for doing data analysis, while Access is a far
better facility for managing the storage and updating of data.

JimB
 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
McGillivray, Don
Sent: Monday, March 16, 2015 8:41 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Audit Trail Suggestions

Well somewhere in the bowels of the application layer is a function or some such
that parses the nasty string so the application can display the history, but
it's available for use only within the context of the application.  For querying
directly against the database, I built a function in Oracle that parses the
string and optionally allows me to specify a target column, old value, and/or
new value.  That way if I'm looking for audit records where the value of column
x from table y changed from a to z, or from a to any other value, etc., I can
pass the string to the function with the appropriate parameters and select those
rows where a value is returned by the function.  It's crazy expensive, and I
hate using it.  Thankfully, we don't have many requests for data that require me
to interrogate that string.

Not my design, and like I said - a total PITA, but possibly useful in some
contexts.  

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W.
Colby
Sent: Monday, March 16, 2015 12:46 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Audit Trail Suggestions

 >>>Of course, you'd need to build the secret decoder ring to translate the
arcane delimited string into something human readable, and there's processing
overhead associated with that, and I'll tell you from experience that writing
useful queries against that delimited string is a total PITA.

Uhhh yea.  So if you don't anticipate ever needing to query the change log then
this might work.  The issue becomes if you want to find a change "sometime this
last year in field xyz of table K". How do you do that?

The strategy I used was to build a table of table names, a child table of field
names and then a log of FldID (contained
TblID)/RecID/UserID/DteTime/OldVal/NewVal/DataType.  That strategy allowed easy
querying of any of the various major IDs, plus the values etc.

The system used a cache of the table/field info and added new tables / fields as
new ones were encountered.  Thus is a field was never edited, then it never
cluttered up the system.  In the systems that implemented this, specific fields
were "hot" and edited a lot.  Most fields were cold and rarely / never edited.

But yea, actually trying to get data back out of a denormalized "string" 
system would be pretty darned difficult.

John W. Colby

On 3/16/2015 2:47 PM, David Emerson wrote:
> Thanks Don for the suggestion.  In my case I am not expecting too many 
> changes so a single record per field will suffice.
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of McGillivray, Don
> Sent: Tuesday, 17 March 2015 4:51 a.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Audit Trail Suggestions
>
> A little late to the party, but thought I'd chime in with an approach 
> that I've encountered.
>
> At my current job we use an off-the-shelf product based on an Oracle 
> backend.  The application layer implements optional audit tracking, 
> selectable at a data entity level.  But instead of writing a record to 
> the audit table for every single column value change, it summarizes 
> all the changes that occurred in each update event, concatenating the 
> results into a specially delimited string.  Separate fields capture 
> the ID of the affected record, the user ID, and date, but all the 
> change information is embedded in a single (admittedly hideous) value.  
> In the case of our application, each value in the string is preceded 
> by an asterisk followed by a value indicating the length of the value 
> to follow, followed by the value itself, with column names, old 
> values, and new values presented in pairs in a predictable sequence.
Something like (air code here):
>
> *#ColumnName1*#ColumnName2*#OldValue1*#OldValue2*#NewValue1*#NewValue2
>
> There may be some other data at the head of the string, such as column 
> count or some such, but you get the idea.
>
> This strategy represents a shift from a one column/one record approach 
> that was originally used with the product, and presumably was 
> implemented to reduce the number of records required to house audit 
> data in large scale implementations.  If you anticipate a large volume 
> of audit data as the result of your audit tracking, it may be worth 
> considering a similar strategy.  Of course, you'd need to build the 
> secret decoder ring to translate the arcane delimited string into 
> something human readable, and there's processing overhead associated 
> with that, and I'll tell you from experience that writing useful 
> queries against that delimited string is a total PITA.  Still, folks a 
> lot smarter than me came up with the idea, so something like it may be worth
considering, depending on the circumstances.
>
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of David Emerson
> Sent: Friday, March 13, 2015 9:22 PM
> To: AccessD
> Subject: [AccessD] Audit Trail Suggestions
>
> Hi Team,
>
> Looking for ideas for implementation.
>
> Access 2010 FE, SQL 2008 R2
>
> A client wants to keep track of some fields in some tables when they 
> are changed.  There are over 50 tables involved and anywhere from 3 to 
> 30 fields per table.  The tables and fields are predefined.  Most of 
> the data changes will be done via Access screens but there are some 
> fields that are changed through code
>
> When a field value is changed they want to store in a log table date, 
> person making change, old value, new value.
>
> Looking for ideas of how others might have tackled this type of 
> problem before.
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com