Implementing audit trail

6 posts, 1 answers
  1. Tomaz
    Tomaz avatar
    5 posts
    Member since:
    Jan 2012

    Posted 01 Aug 2012 Link to this post

    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
  2. Ady
    Admin
    Ady avatar
    589 posts

    Posted 06 Aug 2012 Link to this post

    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
    OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
  3. DevCraft banner
  4. Tomaz
    Tomaz avatar
    5 posts
    Member since:
    Jan 2012

    Posted 07 Aug 2012 Link to this post

    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);
            }


  5. Answer
    Ady
    Admin
    Ady avatar
    589 posts

    Posted 08 Aug 2012 Link to this post

    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
    OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
  6. Mikael
    Mikael avatar
    1 posts
    Member since:
    Jul 2012

    Posted 13 Mar 2014 Link to this post

    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:
  7. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 14 Mar 2014 Link to this post

    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
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner