Alpha Video Training
Results 1 to 19 of 19

Thread: f_Audit() - Add auditing to any table

  1. #1
    "Certified" Alphaholic
    Real Name
    Andrew Schone
    Join Date
    Dec 2005
    Location
    Kansas
    Posts
    1,047

    Default 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.

    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.

  2. #2
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: f_Audit() - Add auditing to any table

    Andrew,

    Thanks for sharing this. I will make good use of it.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  3. #3
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default 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.

  4. #4
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: f_Audit() - Add auditing to any table

    Quote Originally Posted by aschone View Post
    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. :)

  5. #5
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default Re: f_Audit() - Add auditing to any table

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

    Bill.

  6. #6
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: f_Audit() - Add auditing to any table

    Quote Originally Posted by Bill Parker View Post
    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.

  7. #7
    Member
    Real Name
    robert adler
    Join Date
    Apr 2000
    Location
    Boynton Beach, BL
    Posts
    591

    Default 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

  8. #8
    Member
    Real Name
    Jose Manuel Atienza
    Join Date
    Mar 2006
    Location
    Philippines
    Posts
    278

    Default Re: f_Audit() - Add auditing to any table

    @Andrew:

    Great function!

    Quote Originally Posted by aschone View Post
    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!
    Jim

  9. #9
    "Certified" Alphaholic
    Real Name
    Andrew Schone
    Join Date
    Dec 2005
    Location
    Kansas
    Posts
    1,047

    Default 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.

  10. #10
    Member
    Real Name
    Leonard Taylor
    Join Date
    Jul 2010
    Posts
    6

    Default 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!?!

  11. #11
    "Certified" Alphaholic
    Real Name
    Andrew Schone
    Join Date
    Dec 2005
    Location
    Kansas
    Posts
    1,047

    Default 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?

  12. #12
    Member
    Real Name
    Leonard Taylor
    Join Date
    Jul 2010
    Posts
    6

    Default Re: f_Audit() - Add auditing to any table

    Quote Originally Posted by aschone View Post
    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!

  13. #13
    "Certified" Alphaholic
    Real Name
    Andrew Schone
    Join Date
    Dec 2005
    Location
    Kansas
    Posts
    1,047

    Default Re: f_Audit() - Add auditing to any table

    Quote Originally Posted by leonard.taylor View Post
    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.

  14. #14
    Member
    Real Name
    Leonard Taylor
    Join Date
    Jul 2010
    Posts
    6

    Default 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?

  15. #15
    "Certified" Alphaholic
    Real Name
    Andrew Schone
    Join Date
    Dec 2005
    Location
    Kansas
    Posts
    1,047

    Default 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.

  16. #16
    "Certified" Alphaholic
    Real Name
    Jetson Lilibeth
    Join Date
    Dec 2011
    Posts
    1,093

    Default Re: f_Audit() - Add auditing to any table

    Is this still applicable in v11? i tried the steps and right click on a table and I clicked table auditing but nothing happens, and how do I include the text code given as a modification to get the login name instead of using the built in windows log in name?

  17. #17
    "Certified" Alphaholic
    Real Name
    Andrew Schone
    Join Date
    Dec 2005
    Location
    Kansas
    Posts
    1,047

    Default Re: f_Audit() - Add auditing to any table

    I do not know as I have only used it in version 8
    Andrew

  18. #18
    Member
    Real Name
    Jo Hulsen
    Join Date
    Mar 2006
    Location
    The Netherlands
    Posts
    331

    Default Re: f_Audit() - Add auditing to any table

    It works in V10.5.
    Jo Hulsen
    Dommel Valley Solutions
    The Netherlands

  19. #19
    "Certified" Alphaholic
    Real Name
    Jetson Lilibeth
    Join Date
    Dec 2011
    Posts
    1,093

    Default Re: f_Audit() - Add auditing to any table

    Quote Originally Posted by aschone View Post
    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.
    Hi Andrew, How do we replace the old code to the new one above? BTW,The original zip file is working in Version 11.

Similar Threads

  1. Can't add table..
    By Frank Calandro in forum Alpha Five Version 7
    Replies: 4
    Last Post: 12-04-2005, 10:02 AM
  2. Add New Table
    By Keith Hubert in forum Alpha Five Version 6
    Replies: 5
    Last Post: 11-14-2005, 04:57 PM
  3. How to add in a second table
    By WesOlfert in forum Alpha Five Version 5
    Replies: 7
    Last Post: 01-16-2005, 10:31 AM
  4. Security Auditing Question
    By BWilliams in forum Alpha Five Version 6
    Replies: 3
    Last Post: 09-20-2004, 11:31 AM
  5. Add table/set - table not there.
    By CALocklin in forum Alpha Five Version 5
    Replies: 6
    Last Post: 10-24-2002, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •