Welcome! Log In Create A New Profile


Audit Trail

Posted by MediOffice 
Audit Trail
April 27, 2023 11:48AM
My WebDev application has an audit trail. It's very basic. I serialise the record into a buffer on load and after the user presses SAVE. If the buffers are different I know they changed something and then I write to the audit trail that the record was updated with a datetime stamp and which user.

Of course what it doesn't reveal is WHAT changed. I could with lots of programming of course do this - but I have LOTS of tables and pages - so this would be a LOT of work.

I could compare the two buffers and list which values are different - before and after - but many are ID fields used to lookup in other tables and the IDs would be meaningless to the user.

I wonder if there is a way to automatically parse the values of the controls on the screen - edits, combos (displayed value), checkboxes and save those in a buffer/array. Do this on load and on save and compare the two and report on the differences.

Question is how to write a generic procedure that would do this on any page of controls. Ideas?
Re: Audit Trail
April 27, 2023 02:04PM
Hello MediOffice,

At the save point, you can get the current record value by reading the record using an alias file. This gives you a reference set of current data which would include data from other users which may be different to when the current user started their edit process. If this doesn't concern your processing, you could gather the reference data earlier.

Imediately prior to saving the current record, all the data that is going to be saved is in the current file record buffer.

Extract the file fields with HListItem() and compare the data field by field in the alias file to the current record buffer. You can show changed data to the user. I build a memo note of the the changes and save it in the log record.

The third element of the data from HListItem is a number that tells you the data type. You can use that to filter out binary memo and other fields you can't do much about.

I have strict field naming conventions so I can easily identify my primary key values.

I also funnel all record handling through my own global save routines - RecordModify, RecordSave and RecordDelete so my logging is contained in those three process and is automatically applied to every modify, save and delete.

Re: Audit Trail
April 27, 2023 02:16PM
Thank you. Some of that is definitely helpful.

Problem is some of the data in the record is an ID which refers to another table. So just reporting in an audit trail that the ID has changed is meaningless to the user - unless I lookup to that table in the audit trail text and show the new looked up value.

Hence why I'm thinking of trying to grab all the screen fields instead.
Re: Audit Trail
April 27, 2023 02:28PM
Hello MediOffice,

Have a look at the help for HListItem() When you use it to extract the complete profile of a file, it identifies auto ID fields as type 1. I don't need that because my field name rules identify all my primary and secondary key fields

Re: Audit Trail
April 27, 2023 03:01PM
Hello MediOffice,

I have read your posts again and now understand better. The process I described works well as an "after the event" audit trail but not so good for a real time display to the user because of the changes in child records which is a complication I did not realise well enough in my answers.

Maybe filetomemory() using a structure for each file and then compare the structures. On save, use the alias file to fill the reference structure and then compare it to a structure filled from the current structure. This would work because filetomemory() doesn't read the file, just the memory buffer. You could do this for the main file and the child records to give you every field change. The structures could be created using HlistItem() as the source. This is just theory as I haven't worked with structures.


Edited 1 time(s). Last edit at 04/27/2023 03:14PM by Al.
Re: Audit Trail
April 27, 2023 05:34PM
Thanks Al

I have found a solution! I will demonstrate it at the DevCon next week! I will post it here later when I've finished it.

Re: Audit Trail
April 28, 2023 08:31AM
Hi MediOffice,

Some of the things I did before:


IF NOT HDescribeTrigger("product_main_groups","HADD,HMODIFY,HDELETE,HCROSS,HWRITE","PDG_MakeNumName",hTriggerBefore) THEN
	Error("Database error!!")

So everytime the HFSQL makes a insert, update or delete this trigger will fired.

But unfortunatly, the read is not on the list. So this approach applies only to changing something, reading is another topic.

I did some research years ago, found out, the best is to store the differences (only that, because every change, all data would explode the database) in json, yep, yery dense, very small and I can easily manipulate the data. So logging can be made.
renko allard
Re: Audit Trail
May 13, 2023 02:01AM
run a query for the page . fill the screen fromthe query fill anarry of structure as well make changes save query . save to arry as well compare arrays.

Your Email:


Spam prevention:
Please, enter the code that you see below in the input field. This is for blocking bots that try to post this form automatically. If the code is hard to read, then just try to guess it right. If you enter the wrong code, a new image is created and you get another chance to enter it right.