f_Audit() - Add auditing to any table

[aschone]

f_Audit() will add auditing fields to a table, auditing based field rules, and a simple audit review form and report.

The auditing field rules where copied from Peter Wayne's "Xbasic for Everyone" with a few minor differences. Other snippets of code where copied from the Alpha5 forum. I would like to thank Cal Locklin, Tom Cone Jr, & Peter Wayne for continual posting of advanced concepts and how to's. Without which this function would not have been finished.

f_Audit will modify the table structure and field rules of tables it is ran against.

Added fields
Created_By_f, character, length of 30
Created_On_f, Time
Changed_By_f, character, length of 30
Changed_On_f, Time
Unique_ID_f, character, length of 36

Both Created_By and Changed_By are populated via API_GETUSERNAME()
Changed_On and Changed_By are populated via NOW()
Unique_ID is populated by API_UUIDCREATE()

Modified Record Events
CanSaveRecord
OnSaveRecord
OnEnterRecord
OnChangeRecord
CanDeleteRecord
OnDeleteRecord

Added tables - will be added in the same folder as the database
Audit.dbf
Changes.dbf

Added forms
Audit_frm

Added reports
Changes_Rpt

How to use
In the zipped file is a folder called "Tools_dB". The "Tools_DB" folder needs to be copied to the root of the c: drive.

Audit.table needs to be copied to C:\Program Files\A5V8\Control_Panel_Addins

Audit.aex can be copied to C:\Program Files\A5V8\Addins_installed.

Open a test database, right click on a table, select Addins, Add Auditing. This will add the needed fields and the auditing record events to your table. It will also add the Audit.dbf to the database and the Audit_frm. Modify/Add/delete a record in the newly audited table. Open the audit_frm to see a simple view of what was modified. Click Report Differences for a hardcopy of the changes. "Report Differences" will add the change.dbf table and the changes_rpt report to the database if they are not currently there.

[Tim Kiebert]

Re: f_Audit() - Add auditing to any table

Andrew,

Thanks for sharing this. I will make good use of it.

[Bill Parker]

Re: f_Audit() - Add auditing to any table

Andrew,

You need to be careful in case the table already has field rule event scripts that have an END statement. Since your code is added at the bottom of the script, it might never get executed.

You could convert the OnSaveRecrod and OnDeleteRecord scripts to an inline function, and put a call to the function at the top of the script, after any header.

Similarly, the other events might not execute if there is an existing END statement. Looks like these scripts could go at the top of any existing script if you remove the END statement from your code.

Alternatively you could leave your code placement, but display a warning dialog to the user if existing code is found - the user should review the scripts to insure that flow through the script is proper.

Bill.

[Melvin Davidson]

Re: f_Audit() - Add auditing to any table

f_Audit() will add auditing fields to a table, auditing based field rules, and a simple audit review form and report.

Thanks for this, but I suggest you also post this to the code archive. That forum is dedicated to the sharing of tricks and solutions. :)

[Bill Parker]

Re: f_Audit() - Add auditing to any table

This is the code archive. :-) Although we did not get all the code. :-(

Bill.

[Melvin Davidson]

Re: f_Audit() - Add auditing to any table

This is the code archive. :-) Although we did not get all the code. :-(

Bill.

DOH! That's what I get for using the New Posts quick tab.

Thanks Bill. Next time I'll look at the forum name.

[Robert Adler]

Re: f_Audit() - Add auditing to any table

Hi:

Just started to play with this code. worked fine in development and in a shadowed database when the development software is on the PC.

I could not use the software when RUNTME was the only software. It appears that the development software must also be on the PC.

bob adler

[jmatienza]

Re: f_Audit() - Add auditing to any table

@Andrew:

Great function!

Both Created_By and Changed_By are populated via API_GETUSERNAME()

Would you be so kind as to include an option to populate the Created_By and Changed_By from a global variable containing the name of the user who logged into the application specifically, instead of using the Windows user name automatically?

Thanks!

[aschone]

Re: f_Audit() - Add auditing to any table

Here is the code. Simply put most of it is not mine hence why I initially didn't publish it. I simply combined pieces of other peoples code to make this work.

[leonard.taylor]

Re: f_Audit() - Add auditing to any table

Greetings all. I'm quite a newbie to Alpha 5 and am attempting to use it to solve a need. I need to create a comprehensive audit trail of changes made by users (I'm looking at having about 80) and this tool seems ideal. However, I've tried every which way I can think of to get this to work on V10, but have had no luck. Help!?!

[aschone]

Re: f_Audit() - Add auditing to any table

This was originally designed and tested in version 8. I have yet to try it in v10 so can not attest to it working or not working in version 10.

Are you getting an error message? What operating system are you using? Have you looked at the source code to see if you were able to identify the issue?

[leonard.taylor]

Re: f_Audit() - Add auditing to any table

This was originally designed and tested in version 8. I have yet to try it in v10 so can not attest to it working or not working in version 10.

Are you getting an error message? What operating system are you using? Have you looked at the source code to see if you were able to identify the issue?

Hi Andrew�thanks for responding! Sorry about this but I�m not a computer guy�just someone stuck with trying to make this work. I�m creating a web accessible database in Alpha 5 v10. My operating system is Windows XP. I�m not getting any errors�I�m simply not getting anything. When I click the Add-in (after I installed it all), nothing happens.

I found a post where someone had detailed out exactly what your script installed into each event. I�ve manually pasted them in and am sorting out what does what. I�ve got parts working, but I�ve a long way to go.

My goal (on this aspect of the project) is to create a audit trail of changes/deletions/new records�ideally saving key fields in a audit table. I�m trying to figure out how to get your bits to save the field values instead of the �property_to_strings� function, which isn�t large enough nor researchable. Might you tell me how I can modify your line of:

�audit.old_rec_f = old_rec� to something like:

�audit.old_rec_f1 = var->inst_title�

--with �var->inst_title� being a field from my table.

Thanks for anything!

[aschone]

Re: f_Audit() - Add auditing to any table

Hi Andrew�thanks for responding! Sorry about this but I�m not a computer guy�just someone stuck with trying to make this work. I�m creating a web accessible database in Alpha 5 v10. My operating system is Windows XP. I�m not getting any errors�I�m simply not getting anything. When I click the Add-in (after I installed it all), nothing happens.

I found a post where someone had detailed out exactly what your script installed into each event. I�ve manually pasted them in and am sorting out what does what. I�ve got parts working, but I�ve a long way to go.

My goal (on this aspect of the project) is to create a audit trail of changes/deletions/new records�ideally saving key fields in a audit table. I�m trying to figure out how to get your bits to save the field values instead of the �property_to_strings� function, which isn�t large enough nor researchable. Might you tell me how I can modify your line of:

�audit.old_rec_f = old_rec� to something like:

�audit.old_rec_f1 = var->inst_title�

--with �var->inst_title� being a field from my table.

Thanks for anything!

I do not do any work on the web side but I do believe that some of the table events do not fire, you will need to research if the events I am using are firing from the web side.

I do not understand what you mean by

�property_to_strings� function, which isn�t large enough

I was saving the output of the property_to_string to a memo field which should be big enough to handle it.

I do understand about the unsearchable part. A while back i wrote a companion function that would return just the changed bits between the old rec and the new rec.

Ex. f_AuditHistory(table.current(),.t.) I call it from my audit form and save it to a third memo field. Then only display the 3rd memo field. I am not sure if this function was included in my original aex file.

�audit.old_rec_f = old_rec� to something like:

�audit.old_rec_f1 = var->inst_title�

Where is this code at? It has been so long since I messed with most of it that I have forgotten all the little details.

you could try using the eval() function wrapped around your 'var->inst_title'

On the other hand I wanted the audit functions to be generic enough that it didn't need to be aware of specific fields within the table.

[leonard.taylor]

Re: f_Audit() - Add auditing to any table

Thanks again for helping Andrew�I really appreciate it and your time! I�m weeding my way through which events fire, and how the different scripts interact with each other, towards my goal (trial and error method, 1 line at a time�). I�m keeping myself sane by chanting �learning curve� mantra.

Regarding my �researchable� comment, I�d like to have a way to review a history of changes associated with a specific entry group. I�m trying to manage about 1,400 academic course and appointment entries being tweaked by several different people. I need to be able to quickly find who did what and when. As I�m too green to figure out how to create a search tool to examine the audit-trail created by the �property_to_string� function, I figured the easiest would be to make a table that collected all key fields every time someone changed/deleted/added anything. I could easily then make a tool to search this table for the history.

The best approach I�ve found to date is to use your scripts to record to an audit table, and I just figured out how. I simply needed to give my variable a location using �tc� (table.current). I�ve changed your line:

�audit.old_rec_f = old_rec� which is in the Table>Field Rules>Events Record Events>� to:
�audit.old_rec_f1 = tc.inst_title� which saves the contents of the field to the audit table.

With this, I�ve got a good direction to follow on this aspect of the project. Thanks again for the input�maybe I should take a class or something?

[aschone]

Re: f_Audit() - Add auditing to any table

Take a look at "Xbasic for Everyone" by Dr. Peter Wayne. Chapter 42. An Audit Trail. This is where I got a majority of the code. From there I tweaked bits and pieces.

The audit functions should have already been writing the changes to a audit table. From there you can use the f_AuditHistory() to find just the bits that changed. Save the changed bits to a new field, then you can write a routine that will search the changed bits.