Approach to storing an audit trail of document updates

[DaveC]

I do something similar and tail the oplog to handle auditing by adding any contextual audit information into the document so that I can capture that information in the same manner as all the other fields recorded in the change. Documents could have a few extra fields like LastModified, ModifiedBy, ModifiedEvent.

Would you be fine with storing your audit information in the document?

[Raxit Sheth <Mobile 4 Mumbai>]

Hi


1. With Oplog i can only find operations, not document diff (correct
me if i am wrong!, like multi update will record query, but not record
which all doc will get updated!)

2. When thinking of audit trail report, put that in different
database. [So while writing audit-trail, it will not block your
regular operations as currently mongodb is having write lock per
database write operation]

3. consider of sharding and overall architecture.

4. I would again go back why one wants to do audit trail? a) to catch
bad user b) to roll back the dirty changes c) just to comply with
audit policy

5. a) and c) are fine, but if you really wants to achieve b) it may
require some hack into mongodb and may be some performance penalty
<it really depends on how many write operations are performed +
document diff size and other factors>

6. Trade-off will be to take daily/hourly backup, and store only
queries causing document changes (instead of document diff. for each
operations!)

PS: you may want to search audit trail in archive of this google group
+ jira + stackoverflow

Thanks
Raxit Sheth


On Dec 24, 5:36 pm, Anuj Shah <anuj.pradeep.s...@gmail.com> wrote:
> Hi,
>
> I am trying to model an application specific audit trail and am looking for
> a good approach to do so.
>
> Requirement is to store the following info for audit in a shadow collection:
>
>    1. docId:  ObjectId of the changed document
>    2. on:  date-time stamp of change
>    3. by:  Application's user's ObjectId
>    4. event:  Application specific event / action that caused this change
>    5. changes:  an object hash of all changes to the document.  For
>    example: document has properties { Style: "Men's Bottoms / Work /
>    Carpenter", Color: "Khaki" }, Color is changed to Blue, then this hash will
>    look like:  changes: { Color: { inserted: "Blue", retracted: "Khaki" } }
>
> To achieve the following I can think of needing the ability to identify a
> change event and a document diff.  A change event can be identified at the
> app layer, but it would really help to have an efficient document diff
> facility.  Any pointers?
>
> Note that I thought of using the oplog, but the issue would be to tie the
> change with an application user / event.
>
> If someone has a better approach, please recommend.
>
> Thanks!

[DaveC]

On Monday, December 24, 2012 8:42:57 PM UTC-5, Raxit Sheth <Mobile 4 Mumbai> wrote:

 

1. With Oplog i can only find operations, not document diff (correct
me if i am wrong!, like multi update will record query, but not record
which all doc will get updated!)

You will get an oplog entry for each document updated, including both the document _id field and the update performed.  So if you do a multi update with a query that matches 40 documents, you'll get 40 oplog entries - 1 for each document.  Also, the oplog doesn't store queries at all - just inserts, updates, and deletes.

[Daniel Doubrovkine]

You might find https://github.com/aq1018/mongoid-history useful, at least in terms of all the things to store to be able to undo/redo changes.

--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

--

dB. | Moscow - Geneva - Seattle - New York
dblock.org - @dblockdotorg