Creating EventLog Entries

[stanlyn]

Hi,

Has anyone created a generic method that logs changes by the user into a EventLog table that records only the changes with both the before and after values? Maybe something built on oldval() and curval that iterates the table values and building the EventLog entry string as it iterates. The string would be something like "SB-2145 - Stanley made changes to TableName on 01/01/2015 at 11:25 PM. The changes are:

TableName.Amount's before value was 125.00, now its 137.12
TableName.Size's before value was Large, now its Small
........

When finished iterating, stuff it into the EventLog's memo field named details. The EventLog table has other fields like timestamps, userID, type (such as change, new, delete) and others that also gets updated.

The TableName.fieldname component could be grabbed using afields()...

Just wondering if someone has built such a beast before I attempt it.

Thanks,
Stanley

 

[Tore Bleken]

I suggest that you skip the long text, that's something that you can add in your event log viewer program. You will (at least) need the following fields:
1. Pk, unique key (every table should have one, without exception, according to my rules. I prefer Guids)
2. Datetime
3. TableName
4. Fieldname
5. OldValue
6. NewValue
7. UserId, foreign key into user table

 

[stanlyn]

Hi tbleken,

Yes the additional info you mentioned is covered by the others part of my "(such as change, new, delete) and others " statement above.

It seems to me that when a change is made we should
1. get a list of all the fields in the table,
2. iterate thru each of them testing oldval() against curval,
3. if values are different, then append the difference details to the details string that will be written to the details memo field at the end of the iteration.
4. If the cur and old values are the same, then ignore,

Remember, I'm asking whether anyone has built a generic version of this that could literally be dropped in with minimal changes. In the past, I've used a more custom where I spelled out all the table and field names in the logging routine. I'm asking for a routine that looks at the active table's structure and iterate thru all its fields and build a details message containing only entries with differences, without defining the table or field names. I can be used on a table with 10 fields or 100, as the routine would not change.

I also know that testing memo values can be an issue if they are big.

I also agree with you that creating separate EventLog fields named table name, field name, old value and new value could help automate the usage of the data in the future. Currently, they just need to answer the who, what, when and where questions.

Thanks,
Stanley

 

[Mike Lewis]

Stanley,

For what it's worth, here's how I do it.

First, here is my audit table:

ID             int           Autoinc ID for this record
Updatetype     char(1)       I = insert, U = update, D = delete
Trx_ID         char(10)      Transaction ID
User_Name      char(16)      Login name of user who made the change
Updatetime     datetime      Datetime of the update
Tablename      char(16)      Table being updated
RecordID       int           ID of record being updated
FieldName      char(16)      Name of field being updated
Oldvalue       char(48)      Value before the update 
Newvalue       char(48)      Value after the update

The transaction ID is simply a 10-character unique string (generated by SYS(2015)0 which links individual updates in the same transaction. The old and new values are always stored as character strings; if this exceeds 48 characters, it is truncated (you could alternatively use a memo field for these).

The updating of the audit table is done within my global data manager class. In my applications, all table updates are done by this class, so it is easy to channel the maintenance of the audit table there. The Oldvalue and Newvalue fields are obtained from OLDVAL() and CURVAL() respectively.

Another option would be to use triggers to update the audit table. That would have the advantage of being easy to slot into existing code. But it would only work if the underlying tables were in a DBC.

The final component is the reporting of the audit table. I do that by displaying the table in a grid. It can optionally be sorted on datetime within tablename (to get all updates for a given table together), or on transaction ID with datetime (to see all related updates together). In addition, the user can filter on user name and date range. I also have some code for printing the grid.

You will probably also need a routine to truncate and pack the audit table. It is likely to grow very large over time, so you need to be able to clear out old records periodically, say, every six months.

So, that's how I handle the situation. You might prefer a different approach, but I hope the above will give you some good ideas.


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Hi Mike,

This is OK, but I believe you're missing my point. I'm asking about a solution that can be easily plumbed in with NO field name definitions or assignments. The field list comes from afields() and types can be figured out automatically. That way we don't care how many they are or their types. Its just too much work to create a logging table with every table having its own set of routines. I'm looking for a one size fits all...

Thanks,
Stanley

 

[Mike Lewis]

Stanley, no I don't think I missed your point. In my solution, each table doesn't need its own set of routines. There is a single audit table, and a single routine to update it. It doesn't matter which table is actually being logged: it is just one piece of code. That code can determine the table and field names for itself, and insert these into the audit table.

I understand that you want a solution that can be plumbed into existing code. How well that will work will depend on how you handle your data management. If you have a standard data management class, then it's just a question of adding a couple of methods to it (in fact, I sub-classed my data management class to produce a "data management with audit" class, but that's not the main point). If you don't have a standard data management class, then I agree it will be more difficult.

Or, did you consider my other idea: of using triggers to update the audit table? As far as I can see, that wouldn't involve any changes whatever to your existing code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

I use business classes for each table. In my table superclass I have the code to create the event log table if it doesn't already exist, and the code to generate the records. This is controlled by a lAudit property of this class, if .F. the event log isn't created or updated. This means that all the code is in one place, but unfortunately it's heavily dependent on other parts of my framework, so I can't share any meaningful code.

And by the way, I forgot to include the primary key (pk) for the table in question. I never delete records, and for inserts I see that oldval is empty for every field.

 

[stanlyn]

OK guys, what is the 'data management' classes you are all taking about? Where did it come from? Or, is it a solution you home brewed and are now calling it a 'data management' class? Or, is it a component of a framework?

Just confused, as I've seen it in native vfp, but then again, I've never looked...

Thanks,
Stanley

 

[Mike Lewis]

A data management class is just what its name suggests: a class that manages data. It doesn't "come" from anywhere. You have to write it (or use someone else's). What you put in it is entirely up to you, but, like many other classes, the aim is to encapsulate a number of common functions that you use many times in your applications.

For example, I have a class that I can drop on any form, to turn the form into a data-aware form. So, it has methods to put the form into and out of edit mode, to save or revert the current record, to delete the current record while performing referential integrity checks, to validate the data, and to print the data.

More recently, I have written a different type of data manager - one that is instantiated as a global object, and which can be called from anywhere in the application. It is designed mainly to handle remote data (from a back-end database) although it works with native VFP tables as well. It too knows all about saving and reverting data, validating, printing, etc.

You don't have to have a data manager, but it makes life so much easier if you do. And, above all, it makes it easier to add new features that then immediately become available across the application. With both the data managers described above, it was very easy to add some code to write a transaction log to an audit table.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Thanks Mike,

You defined what I envisioned. Code that we build... So, are you doing in you data class what I originally asked? Like the use of afields() and iterating thru it looking for the changes and applying other logic. Just wondering if I'm going down the right road...

Thanks,
Stanley

 

[Mike Lewis]

Using AFIELDS() could be a good way to go. As it happens, I do it slightly differently, but there are probably several different approaches. The point is that you need to know which fields have changed, and what they have changed to and from.

Basically, my approach is as follows:

- I call my audit log routine for each table, immediately before the updates to the table are committed.

- Within the routine, I call GETNEXTMODIFIED() to find each modified record in turn.

- For each modified record, I call GETFLDSTATE() to find if it's an insert, update or delete

- I then loop through the fields in the modified record (I use a FOR loop from 1 to FCOUNT() but you could also use AFIELDS() here). For each field, I compare the OLDVAL() with the actual value. If it has changed, I write a record to the audit table.

The above is a simplification, but it should give you the general idea. I can't post the actual code, partly because it would be very long, and partly because it wouldn't make sense outside of the context of my overall framework.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Thanks Mike for sharing your approach, as this was what I was trying to get to with my original question. I just learned a new function from your post: GETNEXTMODIFIED() I had not looked far enough ahead to realize the impact of dealing with buffered tables and that function will help deal with that.

I'd like to hear about any other different approachs.

Thanks, Stanley