Question

Count of documents created for a month doesn't match between audit and master database

Audit Trail

Updated September 8, 2014

asked on September 3, 2014

I am working on repository metrics. I get different totals of documents created from master/main database and from audit database. For august I get about 9000 entries from master database (tocID) and only 4000 from audit. I looked up few entry IDs and they did come up in repository. We want to make sure data is correct. I checked in admin console, the audit setting is same for all users belonging to a group. It has usual events selected. Can something block entries from being logged in audit?

Thanks.

0 0

Answer

SELECTED ANSWER

replied on September 3, 2014

Also note that the toc table contains all entries and not just documents. If you want to filter for documents, then make sure to include

etype = -2

in your where clause. Another cause of discrepancy is that if you copy an entry and paste it somewhere else in the repository, querying the toc table will return two documents, but if you run an audit report for only created documents, it will return one, since the second document was copied.

0 0

View 2 previous replies

replied on September 4, 2014 • Show version history

Thanks. Tried with etype = -2. Didn't make much difference in the result set. But good to know about it.

Will check if users are copying and pasting the documents. But shouldn't audit consider it as separate entry since it's a new document?

0 0

replied on September 4, 2014

In your audit report, are you looking at the "Create Document" and "Copy Entry" events?

0 0

replied on September 4, 2014 • Show version history

I added copy entry event and that returned 17000 unique entries. So now I am over the master database count. The beginning and ending entry IDs match in both database for month of august, but count differs a lot.

When I exclude recycled entries in audit (event type 99), I get 11500 entries.

So in master database total is 9300 excluding entries in recycle bin and in audit they are 11500.

0 0

replied on September 4, 2014

Have you also accounted for documents that have been completely purged from the repository?

If you have a complete list of entry IDs from your query of the database as well as the audit report, then just cross reference the lists and for any entry IDs that are in the Audit Report list, but not in the database query list, confirm if those entries have been permanently deleted/purged from the repository.

0 0

replied on September 8, 2014

Copy entry helped and using destination id to filter from deleted entries was the trick. Thanks.

0 0

Replies

replied on September 3, 2014 • Show version history

Are you basing your 9000 entries on the increase in tocid over a month? That's not a reliable method, since an insertion in a transaction that has been rolled back or failed to commit can increase an auto-increment value without creating a row. The most common cause of this is name collisions on import. You'll want to do a `select count(*) from toc where created between X and Y` to get a better estimate. Also, you'll want to make sure all of your audit data for that time is loaded up for reporting by checking on the configuration page in Audit Trail.

If you still think that there's a discrepancy, your next step would be to find a few documents that were created but not audited and see what they have in common. Maybe it's a user that doesn't belong to that group.

0 0

replied on September 4, 2014

Thanks. I am using query to get the count. Data was loaded in audit. All users belong to same group and that group has basic audit events selected.

0 0

replied on September 8, 2014

Hi Apurva,

If your question has been answered, please let us know by clicking the "This answered my question" button on the appropriate response.

If you still need assistance with this matter, just update this thread. Thanks!

0 0

You are not allowed to follow up in this post.

Question

Question

Count of documents created for a month doesn't match between audit and master database

Answer

Replies

Sign in to reply to this post.