Jump to content

Audit Log Tips wanted for Ray Cologon's UltraLog


This topic is 4741 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I've spent a while playing with Ray Cologan's Ultralog demo file. The file contains the building blocks for an audit log. It all seems nice and cosy when you're working on a flat file solution: a) autocalc field records changes to records in the same table and b] Looping script on FMServer can be used to push changes to an archive table.

I'm trying to list the features I need to build into my solution and I'm feeling a bit uneasy about the extent of work involved. I've decided that the best way to show the modification log is to use a portal to display the archive records from the archive table.

a) ARCHIVE PORTAL INCLUDES RELATED INFO: In the invoice record I'll use a portal onto the archive table to show all the changes made to that record. All records related to the invoice will also have their change data displayed in the invoice's archive portal e.g. invoice line items.

b] SPECIAL FIELDS: Some info is stored as a key so this needs special attention. For example a dropdown of staff names stores a key that referencesthe staff record. The user won't be able to make sense of a log entry that says "StaffKey_kt changed from 'STA00035' to 'STA00058' on 1/1/2011 at 17:44". The best way to track changes to this info is to have a calc field called "StaffNameCalc". The audit log will track that field instead eg "StaffNameCalc changed from 'Robin Good' to 'Gorbin Rudd' on 1/1/2011 at 17:44"

c) ADDING FIELDS: When I add fields to the Invoice table I must remember to also adjust the calculation that watches out for field edits and adds any changes to the modification log.

I just feel I'm scraping the surface. I fear that there is a lot more work necessary to ensure that I display an audit log that will be meaningful to users. I also worry that it's going to add a whole layer of complexity to my system which will complicate future development. Am I overreacting?

Link to comment
Share on other sites

For example a dropdown of staff names stores a key that referencesthe staff record. The user won't be able to make sense of a log entry that says "StaffKey_kt changed from 'STA00035' to 'STA00058' on 1/1/2011 at 17:44". The best way to track changes to this info is to have a calc field called "StaffNameCalc". The audit log will track that field instead eg "StaffNameCalc changed from 'Robin Good' to 'Gorbin Rudd' on 1/1/2011 at 17:44

Not necessarily. The key values will never change, while the people's names will. There may even be multiple people with the same name. A better solution may be to log the change of key value and the person's name. I don't know whether this is possible using Ray's audit log.

Is there a business requirement to audit all data changes, or do you think it'd be a good idea to have?

Link to comment
Share on other sites

Thanks Vaughn and B, It's a request from the client. They want to know how much it's going to cost to have an audit log. I went to read some of the documentation on the Syncdek/FMDataguard site. There's a white paper there detailing the deficiencies of lower end solutions like the UltraLog. That was helpful to know some of the risks e.g. slow performance.

Syncdek is great for rollback, but that's not what I'm after. I need to give the users an at-a-glance summary of changes made to their document e.g. an invoice. A document will encompass the current record as well as the related data displayed in that record. From what I can tell, SynkDek's not going to be any more sophisticated than UltraLog. Both of them will just record all changes made to fields. Its up to the developer to create an "abstraction layer" that summarises changes to make them meaningful to user. I now need to think through and decide how user friendly the audit log should be.

Thanks for your help.

Link to comment
Share on other sites

I'd determine more exactly the client's requirements. The last modified date and time might be sufficient. The whole change-tracking-thing can become a vortex of death.

I worked on a system that tracked changes, only to find that the previous developers had built processes that constantly updated flag fields behind the scenes for the most trivial of reasons. All of these then got displayed in the change log. We then had to hack up a system that only recorded when selected fields in a record were changed, and only then under some circumstances.

Link to comment
Share on other sites

I'd determine more exactly the client's requirements. The last modified date and time might be sufficient. The whole change-tracking-thing can become a vortex of death.

Absolutely in total agreement, all our tables have simple created, modified, created by and modified by fields the latter contain User Id's, modifieds are filtered to only accept non script modified changes to recs. A small icon in the corner of each layout is simply a tool tip displaying these fields. On critical table instances we do not allow deletion of records, but instead additional records to reconcile transactions. This has proven to be more than enough in the vast majority of our solutions.

Link to comment
Share on other sites

Audit logs are mainly directed at database administrators. If you want them to be "user-friendly", you will have an awful lot of work to do, IMHO. In addition, any change you make in your solution will likely require a corresponding modification of the log system.

For example, when selecting a CustomerID on an invoice, you must decide which value/s from the Customers table are required to make this change comprehensible to the user. This is a semantic decision that cannot be automated - and it may be different for each modifiable field.

If I had to this, I would look for a way to import the log into another file and use relationships to the "real file" tables in order to decode the info.

---

BTW, you mentioned invoices - but it's a common practice (often mandated by law) to prohibit modification of posted invoices.

Edited by comment
Link to comment
Share on other sites

"Vortex of Death", yes it certainly sounds like it. Thanks to all of you for telling me about your experiences and encouraging me to scale down my expectations. Here's what I'm now thinking:

1. Don't obsess too much about having it look pretty for the client. If they are so concerned to have an audit log, let them have it in all its naked & confusing glory.

2. Keep it simple by automatically having all fields included in log.

3. Log entries for child tables must have their parent key stored with them. This will ensure that they display in the log for the parent record.

4. All log entries have a calc field that summarise/name the record they're from. This will help the user understand where the field comes from especially if it's from a related table.

5. If user complains about having too many cryptic or irrelevant fields in log then I'll add in a checkbox next to every log entry. Once an entry is ticked/checked then it and all entries like it (for same field name) get put in grey text.

Thanks.

Link to comment
Share on other sites

Clients often ask for things without through for the consequences. I inherited a database (developed by others) where the clients had requested that records be locked against modification once their data entry was complete. This request was provided, at enormous expense. The clients then complained that records could not be corrected or ammended after they had been entered.

Link to comment
Share on other sites

  • 2 weeks later...

You may want to know how things have gone since I asked for advice about setting up an audit log.

I gave the client an estimate for a fairly extensive audit log. She was not prepared to pay for that and as a result has settled on having a audit log for two fields only: "invoice line item amount" and "job name".

Link to comment
Share on other sites

Hi Kevin,

We use FM DataGuard and it meets all of your requirements. The primary relationships can easily be joined (in the audit log file) to show modified child records along with modified parents or it can be related directly into your UI for display with other tables.

As for determining which fields to track ... that is simply a matter of selecting the fields within a specific calculation FMDG uses. You can 'exclude' certain fields, so it would automatically log all others or you can specify just logging certain fields.

The result FMDG produces is a table just like any other table which can be displayed for users in any fashion you wish. I suggest that you download the file and try it out. You will be shocked on how easy it is to implement. The only thing that takes time is the original priming of the functionality - bringing the audit log up to speed through all existing tables/records.

If you ever need to recover mid-day, I suggest you seriously consider FMDG. You can grab a backup of your data, use FMDG to roll the data forward with existing changes and have your solution back up and running in less than an hour (when normally same recovery would take 4-5 hours).

It is one of the best investments we have made. :)

UPDATE: For some reason, I didn't see Kevin's last post. But regardless, he (and everyone) should know that the limitations mentioned are overcome using FMDG.

Link to comment
Share on other sites

We use a modified version of Ray's UltraLog to track changes to a classified ad publishing system. Having the ability to look back and see when changes were made is invaluable from a customer service perspective.

We have a separate table that holds all historic logs (basically anything that's not in the current session). As the user completes a session with a customer it logs all the changes made in a single text field...then we archive it to another table at the end of the transaction. It allows an overview the change history, and we can still roll-back if necessary.

Changes to the log is as simple as adding/removing fields in the parameters for the Custom Function that Ray uses. For us, it's easy and works great. I haven't really encountered to many drawback with using different audit trail techniques. Our concern fell more into the overhead and performance hit...UltraLog and FMDataGuard both did exactly what we need without a big performance hit. And I had it setup and implemented in about a day and a half...which included feeding multiple tables and change logs into a single table, in a multi-user environment.

Link to comment
Share on other sites

This topic is 4741 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.