First impressions

[Dave Graham]

Tim Dietrich is on a tear: he's releasing innovative and useful products on a daily basis…where does he find the time? ;-)

 

I just cracked open FMEasyAudit and wanted to record my first impressions. I've used FMDataGuard and SyncDek for auditing, and attended Wim Decorte's excellent Devcon session on native audit logging in FileMaker. I'm going to implement audit logging in a new solution, so I am particularly interested in EA to see how it compares with the other solutions I've used/seen.

 

I love:

• That it's a native solution. Reducing third party dependencies simplifies things. 
• Well-commented, easy to understand code.

I'd like to see some of the following improvements:

• One long multi-purpose script accessed by script parameters should be split up. There is no time saved by copy/pasting one script vs. multiple. Debugging one long branching script is a waste of time.
• High volume audit logging can generate lots of data. I'd like to see the core EA file being distributed as a modular solution so the logging table can be easily bolted on rather than being imported.
• EA fields names should be user-definable. I already have UUID and mod fields in my solution but they're named differently. I should be able to keep my naming convention without modifying code or updating my schema for the 92 tables in my current solution.

I'll add more thoughts as I integrate EA into a production system. 

[timdietrich]

Dave -- 

 

Thanks for the great feedback regarding EasyAudit. I appreciate it.

 

Here are few responses to what you suggested:

 

"One long multi-purpose script accessed by script parameters should be split up. There is no time saved by copy/pasting one script vs. multiple. Debugging one long branching script is a waste of time."

 

To some extent, I agree. However, in distributing these types of solutions, I've found that it's often easier to have a developer work with and refer to a single script. In this case, it is a rather long script. I've done my best to document the script so that locating a functional block is easy to identify.

 

 

"High volume audit logging can generate lots of data. I'd like to see the core EA file being distributed as a modular solution so the logging table can be easily bolted on rather than being imported."

 

With some work, you might be able to customize EasyAudit so that the log is in a separate file. I'd have to think about how that would work, because I suspect that there are some "gotchas" in doing so.

 

 

"EA fields names should be user-definable. I already have UUID and mod fields in my solution but they're named differently. I should be able to keep my naming convention without modifying code or updating my schema for the 92 tables in my current solution."

 

I like this suggestion, and I'll add it to the Wish List. In the meantime, I think that you can easily customize EasyAudit to use your existing fields, and the change is isolated to a single script step. It's located at around line 155 (just under the comment that reads, "Add this table to the SELECT statement.")  There you will see a Set Variable script step, with this value: If ( not IsEmpty ( $select ); $select & " UNION ALL "; "" ) & 

"SELECT '" & $table_name & "', EA_UUID, EA_Mod_Count FROM " & $table_name & " WHERE ( NUMVAL ( EA_Mod_Timestamp ) >= " & GetAsNumber ( $TS_Pre_Commit ) & " ) AND ( EA_Modifier = '" & $Account_Name & "' )"  You would just need to change the references to the "EA_" fields to use your own field names.

 

 

Thanks again for the feedback and suggestions.

 

-- Tim

[Dave Graham]

1. There are many more script steps that have hard-coded EA field names. I abstracted them into variables and converted the lengthy parameters to use the List() function for improved readability. Almost all changes have breakpoints so you can see what's changed (see attached). Feel free to use any of these changes if you like them.
2. I believe I have run into a show-stopper for me: the Commit Server routine appears to be querying every table for records which have been modified. I haven't actually tested the performance of this approach, but most of my solutions have 90+ tables with some of the heavy tables approaching a million rows. I suspect that any moderate to large scale solution will have an unacceptable performance penalty. Wim's approach (see his Devcon presentation for more info) captures all fields that have changed on the current layout so there's no need to query every table for changes.
3. While I was debugging my changes, having to step over each block of code in the one long script had me nearly pulling my hair out. It might be easier to point someone to a single script, but I implore you to reconsider and change to a modular design as that small benefit is not worth the associated cost.

[timdietrich]

Dave --

 

Thanks for the additional feedback, and for sharing your experience with EasyAudit.

 

Here's what's coming in Beta 2...

 

1. User-Definable Field Names. You'll be able to re-use your own fields, if you'd prefer, instead of having to create EasyAudit's standard "EA" fields.

2. A more modular, EasySync-like approach, to the scripts. See below for a screenshot which shows the new modular script design.

 

I don't have an ETA on Beta 2 just yet. I need to do some additional testing myself first, and also update the documentation, etc.

 

Also, you wrote, "the Commit Server routine appears to be querying every table for records which have been modified. I haven't actually tested the performance of this approach, but most of my solutions have 90+ tables with some of the heavy tables approaching a million rows." That's correct, and that's how EasyAudit is able to determine the full scope of changes that a user has made, including the parent and any child records that were updated. For the large scale solutions that you described, this EasyAudit might not be a good fit.

 

Thanks again for the feedback, and please keep it coming.

 

-- Tim

 

[Dave Graham]

Sweet!

 

I just realized that the forum doesn't allow attachments. You may have already worked this in, but I've made some tweaks that you might want to reuse. Look for the script steps that have breakpoints, and see my comments about what was changed.

 

Grab it here if you're interested: 

 

https://www.dropbox.com/s/szttjy3e01pkjik/Easy_Audit%201.02b.fmp12.zip?dl=0

 

FYI…I ran into an endless loop with the baseline routine when an expected field wasn't found in a table. Probably need to work in a little extra error capture.

[alecgregory]

I'd like to add a few general thoughts to this thread. I'm working in Beta 2.

 

1. For the bulk operations (such as delete all, import and replace field contents) the framework takes the approach of looping through all records in the found set. This is simple and effective, but can have performance and logging implications.

 

Performance-wise, if you have a fairly heavy layout or are accessing over WAN with a large found set then it could take ages to loop through all the records and build the list of uuids. Obviously we can try to avoid heavy layouts, but they are fairly common. And WAN access is sometimes unavoidable.

 

Logging-wise, and more relevant to the framework, if you have a view log set up on a layout you would get a lot of spurious view log records when performing bulk operations. This problem could be solved by using Suppressible Triggered Scripts.

 

An alternative approach would be to use other methods of getting all uuids in the found set either via a custom function or the new ListOf summary field. I intend to do some testing to find the optimal approach to bulk operations.

 

2. As far as I can tell there's no "Record Created" log type. There are various ways this could be implemented. The most generic would involve looking for a "New Value" log type in the EA_UUID field in a table.

 

3. In some cases (such as a massive sync transaction with thousands of records across many tables), a commit can take way longer than your time out value for normal circumstances. You can usually work this out in advance, for example by counting the number of open records before commit, and so revise the timeout value upwards temporarily.