Audit Trail

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?

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.


Regards
Al

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.

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

Regards
Al

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.


Regards
Al



Edited 1 time(s). Last edit at 04/27/2023 03:14PM by Al.

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.

Gavin

Hi MediOffice,

Some of the things I did before:

HDescribeTrigger

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

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.

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.