Audit Log...

[Neil Toulouse]

Hi guys!

We have a very basic audit log function within our system that simply records who/when/what screen and whether they Added/Deleted/Viewed etc a record, and what record (stores table and the primary key field).

What it doesn't do (and what is now being requested) is log any data that has been changed, as in from what value to what value.

To me this may end up being a major re-write of our data handling procedures, and a big system trawl to ensure all queries fall within the scope of the routines.

To avoid this I was thinking that maybe, we could simple capture and store the result of the initial query at the point of entry into the form (or search or whatever) as a string, and the SQL update string at the point of saving the record. Then create a screen that parses those two strings and shows what has changed.

Anyway, is this a way to do this or are there any better methods? Just after some hints and tips on how to handle this.

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Mike Lewis]

Neil,

I have a data-logging routine that roughly does what you want, although it might be difficult for you to wire it into your application retrospectively. Essentially, it relies on the fact that all table updates are handled by a common data manager object.

If you think it might be of interest, check my article, "Add Data Logging to Microsoft Visual FoxPro Applications" in the March 2006 FoxPro Advisor (or see

http://my.advisor.com/doc/17115).

If you no longer have access to FPA, contact me privately and I'll see if I can find anything to send you. But don't rely on it. It might not be suitable for your application, so if anyone has a better idea ....

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

If your tables live in a database, you may be able to get what you need using triggers.

Tamar

 

[DSummZZZ]

Here is a log routine I use which keeps track of adds and changes.

What it does is for 'adds', it just adds a record to the audit log.
For 'changes', it first adds the old record to the audit log, then compares the fields for any changes. If there are changes, it adds another record and compares each field in the table with memvars which were created earlier either with SCATTER or STORE. It then adds those field names to a notes field.

There is some record bloat, but it shows every record a user even thought of changing, and if they did, a good before and after snapshot of the changes.

'ADD', 'CHANGE' and 'SAVE' are passed to the routine depending on which button was clicked in the app.

*... udaudit.prg
*... Call:  DO udaudit with 'tablename', 'udtype'

PARAMETERS ctable, cudtype

STORE cudtype TO m.notes
STORE .F. TO lChanged
DO CASE
   CASE UPPER(cudtype) = 'ADD'         &&... add record
      INSERT INTO (ctable) FROM MEMVAR
      USE IN (ctable)

   CASE UPPER(cudtype) = 'CHANGE'      &&... old record
      INSERT INTO (ctable) FROM MEMVAR

   CASE UPPER(cudtype) = 'SAVE'        &&... changed record
      SELECT (ctable)
      STORE AFIELDS(aFlds) TO nAUHowMany
      FOR nFlds = 1 TO nAUHowMany
         IF aFlds[nFlds, 1] = 'UDDATE' .OR. ;
               aFlds[nFlds, 1] = 'UDTIME' .OR. ;
               aFlds[nFlds, 1] = 'NOTES'
            LOOP
         ENDIF
         IF EVAL('m.' + aFlds[nFlds, 1]) # EVAL(ctable + '.' + aFlds[nFlds, 1])
            m.notes = ALLTRIM(m.notes) + ':' + aFlds[nFlds, 1]
            lChanged = .T.
         ENDIF
      NEXT
      IF lChanged
         INSERT INTO (ctable) FROM MEMVAR  &&... updated record
      ELSE
         m.notes = ALLTRIM(m.notes) + ' - no change'
      ENDIF
      USE IN (ctable)

ENDCASE

Of course, you may need to tweak it to suit your needs, but you get the general idea.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Neil Toulouse]

Thanks for the responses!

I will have a play and let you know what method I come up with. (un)fortunately it's now the easter break so I won't be near a computer for a week or so - heading down to Pembrokeshire to get some much needed R&R!

Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Neil Toulouse]

Hi Guys!

Just to update this thread. We have gone down the route of using triggers against the SQL tables. Easy to setup and does what we need.

Thanks again

Neil

I like work. It fascinates me. I can sit and look at it for hours...