Audit Transactions using MongoDB, Logging, Metrics

Blog Post created by banty01 Employee on Dec 18, 2015

In this post, we will look at the features provided for auditing or user transactions, logging, and metrics.  We will also look at how to use MongoDB to store our audit records.

I often am asked about our logging features and then find out that our customer wanted an audit transaction service instead.  The loggers are a record of what happened for a specific transaction (both internal system functions and user defined logging).  Live API Creator has a wide range of information that can be logged and various loggers can be turned on/off or set to different levels of granularity.


The Audit transaction service is a more specific in that it will record who made a change to system data, when the change was made, the entity object(s) involved, the values  before and after and any related transaction summary (that is, did this transaction impact other objects).


SQL Metrics are a record of each SQL query, both user and system, and the response time required in the execution of the request.  This can help with system performance tuning as well as identify any bottlenecks in REST calls.



The loggers are tied to each REST request, GET, POST, PUT, DELETE and a specific Auth Token.  The loggers are based on the common log4J pattern (Off, Error, Warning, Info, Debug, Fine, Finer, Finest).  An Auth Token can be created by user authentication or by creating a predefined role based value used for specific access to internal API objects.  The predefined Auth Token can be used to set different internal logging values (Administration, Business Logic, Dependency Analysis, General, Database Access, System ,  Security).


The API Creator Log is shown below for a specific Auth Token GET REST call.




Audit Transactions

The audit user transaction service is enabled for each API project by checking the API Properties/Details check box (Audit User Transactions) for all POST, PUT, DELETE.  If a named Resource is created named _USER_TX_AUDIT_ with a server, database, and collection name, then all future audit records will be appended to MongoDB instead of SQL  The action_type (I,U,D, @) correspond to the REST types (insert, update, delete, @ – authentication or function)



Using MongoDB Log to store Audit records
The design of the Audit feature will allow users to enable the Audit service for each API project.  Additionally, the service can append audit records to MongoDB by adding a custom resource to the project  (resource name _USER_TX_AUDIT_) with the server, database, and collection name.

Screen Shot 2015-12-18 at 3.31.13 PM.png

View the Audit Transactions in REST Lab using the Resource

SQL Metrics

API Creator provides an internal service that tracks all SQL requests (both user requests and internal system) showing the actual SQL Query and the statistics for each query.


Total# execsAvg time
select ident ,ts ,level_stored ,description fromdblocal_sample.valid_customerlevel el$thiswhere (ident = ?)order by ident limit 2102.9 msec721.4 msec
First execution:33230 days ago
Last execution:33230 days ago
Shortest execution:466 µsec
Longest execution:7.9 msec




Live API Creator provides these features for logging internal system and logic changes, audits of user change transactions and SQL Metrics to measure system performance. Combined with MongoDB to store and view changes gives the enterprise developer a 360 view of the user and system processes.