OnWindowTransaction and MongoDB

[MonkeybreadSoftware]

Since FileMaker 2023 we got the feature named OnWindowTransaction trigger. You may have read about it on the Claris Engineering Blog: Creating audit logs with the OnWindowTransaction script trigger.

 

Now where do you store your AuditLogs?

In the same table, the same database, on the same server? 

 

Maybe better take advantage from the fact, that you get JSON data passed to your trigger and pass it to MongoDB. Since MongoDB stores JSON very efficiently, you could just store all your audit data there. Whether you store them all on one MongoDB server (cluster) or have each FileMaker server from a client have their own MongoDB server, depends on how your organize your data.

 

We have an older blog post showing a script to store copies of all records in a MongoDB for all changes and do the audit there. That has the advantage, that you could restore from your copy in MongoDB as well as access these records from e.g. a website. See older blog post: FileMaker and MongoDB

 

Having your audit data outside the FileMaker system has some advantages:

 

• Separate server
  MongoDB is a separate server running on a different server hardware, so you have more redundancy for malfunction of the hardware or a malware attack. If your FileMaker server dies, you may look up on the MongoDB what changes have been made since the FileMaker Server made backups the last time.
• Separate user accounts
  The account used to log to MongoDB may be only allowed to insert to the audit table, not to delete or view data. Someone may be able to alter entries in the FileMaker database, but they can't mess up the audit logs. 

 

Now let's show you how to setup this. First you need the OnWindowTransaction script to take the JSON and pass it to MongoDB:

# On Window Transaction Script
 
# get JSON with changes from script parameter
Set Variable [ $json ; Value: Get(ScriptParameter) ] 
# 
# now pass that JSON to MongoDB as a new record
Set Variable [ $r ; Value: MBS( "MongoDB.InsertOne"; $$MongoDB; $json) ] 
If [ MBS("IsError") ] 
	Show Custom Dialog [ "Failed to log transaction" ; $r ] 
End If

As you see we have a global variable $$MongoDB with the connection reference. Of course this needs to be setup once and you may do this from the start script like this:

# Connect MongoDB
 
Set Variable [ $Mongo ; Value: MBS( "MongoDB.New" ) ] 
If [ MBS("IsError") ] 
	Show Custom Dialog [ "Failed to create new MongoDB object" ; $Mongo ] 
	Exit Script [ Text Result:    ] 
End If
# 
Set Variable [ $r ; Value: MBS( "MongoDB.SetURI"; $Mongo; "mongodb://mongo.yourcompany.com/audit" ) ] 
If [ MBS("IsError") ] 
	Set Variable [ $x ; Value: MBS( "MongoDB.Release"; $Mongo ) ] 
	Show Custom Dialog [ "Failed to parse connection string." ; $r ] 
	Exit Script [ Text Result:    ] 
End If
# 
Set Variable [ $r ; Value: MBS( "MongoDB.Connect"; $Mongo) ] 
If [ MBS("IsError") ] 
	Set Variable [ $x ; Value: MBS( "MongoDB.Release"; $Mongo ) ] 
	Show Custom Dialog [ "Failed to connect to MongoDB." ; $r ] 
	Exit Script [ Text Result:    ] 
End If
# 
Set Variable [ $r ; Value: MBS( "MongoDB.OpenCollection"; $Mongo; "AuditLog") ] 
If [ MBS("IsError") ] 
	Set Variable [ $x ; Value: MBS( "MongoDB.Release"; $Mongo ) ] 
	Show Custom Dialog [ "Failed to open collection." ; $r ] 
	Exit Script [ Text Result:    ] 
End If
# 
# success, keep connection
Set Variable [ $$MongoDB ; Value: $Mongo ] 

You may customize the connection here. First change the URL to point to your server. Your MongoDB database is on some domain with a name of the database (here "audit") and the URL may include the account name and password, maybe even some options for which SSL certificate to use. We explicitly open the collection (=table) on the server to specify where the data should be inserted.

 

Once connection is made, we keep it open as a global connection and store the reference number in the $$MongoDB variable. Audit may then run as needed in the trigger script above. You can simultaneously have a second connection open to mirror changes with the other script here: FileMaker and MongoDB.

 

Let us know if you have questions or comments.