Matt has a video behind his paywall that has a nice example of an audit logging system using a custom function that he modified from somewhere else. It uses a field in the same table to list all the changes, as do most of the ones I've seen described over the years. I implemented a use of Matt's technique that added a call to a script creating a new record in a logging table. Lotta work. Aside from just going the FMDataGuard route, is there any new way (i.e. more "elegant") to do this? Perhaps utilizing some of 11's new functionality?
It uses 1 field in each table where you want changes tracked and a single custom function. It is very handy and easy to implement. The main benefit is that it is free, and that it will capture your changes, even those made to related records so long as you have the field setup in the related table to capture the changes to.
If you are concerned about your tables getting bloated by a lot of data from tracking all of the changes and your solution is hosted on a FileMaker server, you can schedule a script to run on the server that will capture the updates from these fields and push them into an audit trail table and then empty them. The only scripting I use with this technique on the client side is to capture record deletions.
Matt's and Ray's solutions are very similar. I have used a version of that technique. I even extended Matt' custom function it to trigger the creation of records in a related log table, which has the advantage of persisting even after the original record is deleted.
I was just wondering if there was anything new that people were using.
I used a script trigger plug-in called EventScript to call a script in the custom function that Matt provides with his audit logging technique. It's a "function script" that takes a bunch of parameters (generated in the custom function) and builds the record in the log table.
The ideal thing is probably to build systems which apply transactions.
Then simply store each one as you apply it.
This makes rollback much easier. It might make re-application of transactions possible when you recover one of the files in your system due to corruption.
--
At very least, timestamps are your friend; having created/changed user and time on most tables is an enormous help, but also has its limits (imports, replace found set may not always update.)
So, you're recommending something like FMDataGuard as the proper way to do an audit log? I have done that, as well as a few other techniques, as mentioned before. I was just wondering if there was a consensus of late as to how to implement an audit log. Sort of a recommendation for a FileMaker Standard, or something like that.
10 Comments
Anonymous
Matt has a video behind his paywall that has a nice example of an audit logging system using a custom function that he modified from somewhere else. It uses a field in the same table to list all the changes, as do most of the ones I've seen described over the years. I implemented a use of Matt's technique that added a call to a script creating a new record in a logging table. Lotta work. Aside from just going the FMDataGuard route, is there any new way (i.e. more "elegant") to do this? Perhaps utilizing some of 11's new functionality?
Jonathan Fletcher
Anonymous
Well?
Jonathan Fletcher
Anonymous
Ray Cologon has a technique that he has had out for some time that may be similar to what you have already described.
http://www.nightwing.com.au/FileMaker/demos8/demo809.html
It uses 1 field in each table where you want changes tracked and a single custom function. It is very handy and easy to implement. The main benefit is that it is free, and that it will capture your changes, even those made to related records so long as you have the field setup in the related table to capture the changes to.
If you are concerned about your tables getting bloated by a lot of data from tracking all of the changes and your solution is hosted on a FileMaker server, you can schedule a script to run on the server that will capture the updates from these fields and push them into an audit trail table and then empty them. The only scripting I use with this technique on the client side is to capture record deletions.
Brian Currell
http://twitter.com/filemakergeek
Anonymous
I posted an old link. Here is the more up to date link for Ultra Log:
http://www.nightwing.com.au/FileMaker/demosX/demoX01.html
Anonymous
Matt's and Ray's solutions are very similar. I have used a version of that technique. I even extended Matt' custom function it to trigger the creation of records in a related log table, which has the advantage of persisting even after the original record is deleted.
I was just wondering if there was anything new that people were using.
Anonymous
I am trying to do exactly that. But how are you having the the creation of records in the related log table created?
Anonymous
I used a script trigger plug-in called EventScript to call a script in the custom function that Matt provides with his audit logging technique. It's a "function script" that takes a bunch of parameters (generated in the custom function) and builds the record in the log table.
Here is my modified CF:
http://www.fletcherdata.com/AuditLog2_cf.pdf
Here is the script:
http://www.fletcherdata.com/LogEvents_script.pdf
Jonathan Fletcher
Anonymous
Thanks! I've been stuck here for a while thinking there'd be an 'elegant' way of doing that. I guess not! Thanks again.
Anonymous
The ideal thing is probably to build systems which apply transactions.
Then simply store each one as you apply it.
This makes rollback much easier. It might make re-application of transactions possible when you recover one of the files in your system due to corruption.
--
At very least, timestamps are your friend; having created/changed user and time on most tables is an enormous help, but also has its limits (imports, replace found set may not always update.)
Anonymous
So, you're recommending something like FMDataGuard as the proper way to do an audit log? I have done that, as well as a few other techniques, as mentioned before. I was just wondering if there was a consensus of late as to how to implement an audit log. Sort of a recommendation for a FileMaker Standard, or something like that.
--Jonathan Fletcher