Implementing audit trail

[Tomaz]

Hello,

one of the requirements for a project we are currently developing is to audit changes of persistened objects. There are several different approaches to achieve this and writing database triggers is my final resolve if some sort of relatively simple automatic audit mechanism in OpenAccess cannot be implemented.

The most simple approach would be to either add custom audit logic to the DBContext.SaveChanges() or to catch modification events (Adding/Added etc.) and then audit changes there. The biggest problem is probably getting the old and the new values of the object and the entity type.

Basically the idea is to have all the audit logic in one place and when the object changes all the neccessary information about entity type and old object data are also available and used for writing an audit trail into database table.

Audit table currently looks like this:
AuditLogID
UserID 
ObjectID
AuditTypeID
ObjectEntityName
OldData (XML)
NewData (XML)
AuditDate

The point is to save inserted/updated/deleted object into OldData and NewData columns which are XML. UserID is the ID of current user who is making the changes to the object(s). ObjectEntityName holds the name of the entity (basically table name). AuditTypeID is used for identifying the type of change ie. select/insert/update/delete.

There is already one solution available on the forums which includes the use of lifecycle events (PostLoad(), PreRemove() and PreStore() using IInstanceCallbacks) but the problem with it is that every entity class would need to have these methods and this can be quite time consuming. Also the old objects property values are not available and information about the type of modification action in case of inserting/updating is also unavailable (to my knowledge).

Since I dont have an indepth knowledge of OpenAccess (v. 2012.1.301.2) to be able to create such a mechanism on the fly and would probably waste way to much time any kind of pointers and suggestions are most welcome.

Thanks

[Ady]

Hi Tomaz,

 I have attached an audit example based on your requirements. Do have a look at it and get back in case you have further queries.

Greetings,
Ady
the Telerik team

[Tomaz]

Thank you for the sample project it was very helpful. However I'm not using fluent API but standard domain model. Based on the sample project I have managed to implement custom audit trail logic which works perfectly for inserts/deletes. The only problem I'm facing now is when persistent object properties are updated.

When using [database context].Events.Changed the event is triggered for each property that has changed. I need to capture an event which aggregates all changes made to a persistent object. How can this be accomplished?

public EntitiesModel()
            : base(connectionStringName, backend, metadataSource)
        {
            this.Events.Adding += AddingEvent;
            this.Events.Added += AddedEvent;
            this.Events.Changing += ChangingEvent;
            this.Events.Changed += ChangedEvent;
            this.Events.Removing += RemovingEvent;
            this.Events.Removed += RemovedEvent;
            this.Events.Refreshing += RefreshingEvent;
            this.Events.Refreshed += RefreshedEvent;
        }

void ChangedEvent(object sender, ChangeEventArgs e)

        {

            //EXECUTES FOR EVERY CHANGED PROPERTY
                //ONE TIME EXECUTION REQUIRED 


            if (e.PersistentObject.GetType() == typeof(RevisionLog) ||

                e.PersistentObject.GetType() == typeof(ErrorLog) ||

                e.PersistentObject.GetType() == typeof(LoginLog) ||

                e.PersistentObject.GetType() == typeof(MessageLog))

                return;

 

            RevisionLog rl = ContextAudit.LogAuditTrail((int)AuditTrailActionEnum.GenericUpdate, e.PersistentObject);

            if (rl != null)

                this.Add(rl);

        }

[Ady]

Hi Tomaz,

 Yes, at the moment there is no event that is called with all the changes for a persistent object. To be able to achieve that you will need to use a combination of the 'Events.Changed' event and 'IInstanceCallbacks' interface.
I have modified the example so that we now have a single audit object for each persistent object with all the changes collected in the 'OldValue', 'NewValue' properties. The point here is that you will need to implement the 'IInstanceCallbacks' interface on each persistent type.
I have also provided an alternative solution where you can have all the auditing in the IInstanceCallbacks method itself and do not need to use the 'Events.Changed' event. This approach will require some more generic code to get the original values for all properties.

Do get back in case you need further assistance.

All the best,
Ady
the Telerik team

[Mikael]

Hello,

In the sample solution provided by Ady, there seems to be an issue I can't seem to resolve.

If the Product.cs class contains a persistent datetime property, the PreStore() method breaks when the context.CreateObjectKey(this) is called.

The error is:



​Telerik.OpenAccess.Exceptions.DataStoreException was unhandled by user code
  HResult=-2146233088
  Message=Insert of '806919771-' failed: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute(Nullable`1 commandTimeout)
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes, BatchControlInfo batchControl)
declare @generated_ids table([ID] int)
insert [Products] ([DatePriceValid], [Price], [ProductName])
output inserted.[ID] into @generated_ids
 VALUES (?, ?, ?)
select t.[ID]
from @generated_ids as g join [Products] as t on g.[ID] = t.[ID]
where @@ROWCOUNT > 0
(set event logging to all to see parameter values) System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute(Nullable`1 commandTimeout)
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes, BatchControlInfo batchControl)
  Source=Telerik.OpenAccess
  CanRetry=false
  StackTrace:
       at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
       at OpenAccessRuntime.ExceptionWrapper.Throw()
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x, Boolean needsRollback)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.GetObjectKey(Object entity, Boolean withVersion)
       at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.GetObjectKey(Object pc, Boolean withVersion)
       at OpenAccessRuntime.ObjectScope.GetObjectKey(Object entity, Boolean withVersion)
       at Telerik.OpenAccess.OpenAccessContextBase.CreateObjectKey(Object entity)
       at AuditTrailExample.AuditTrailExampleContext.AddedEvent(Object sender, AddEventArgs e) in D:\Documents\Downloads\35ad6b0e-98b5-4ba8-be7d-1f0655bbe256_AuditTrailExample\AuditTrailExample\AuditTrailExample\FluentContext.cs:line 119
       at Telerik.OpenAccess.SPI.TrackingImpl.FireAdded(AddEventArgs ev)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.fireAdded(AddEventArgs ev)
       at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.makePersistent(Object o)
  InnerException: System.Data.SqlTypes.SqlTypeException
       HResult=-2146232016
       Message=SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
       Source=Telerik.OpenAccess.Runtime
       StackTrace:
            at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute(Nullable`1 commandTimeout)
            at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute(Nullable`1 commandTimeout)
            at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes, BatchControlInfo batchControl)
       InnerException:

[Ralph Waldenmaier]

Hi Mikael,
It looks like that your object has a DateTime property that is not initialized. By setting an appropriate value to it, the insert would succeed.
The reason for the insert in this case is because of the GetObjectKey call. Depending on the type of the primary key, we need to persist the object to the database in order to obtain the generated primary key for the object.

Hope this helps to resolve this issue.
Do come back in case you have any other question.

Regards,
Ralph Waldenmaier
Telerik