If you ever work with customers with a high demand on compliance and
audit reporting, you will think about using SharePoint Audit Reporting. I want
to share some of my experiences about this topic.
Recently one of my customers was searching for an easy configurable and
maintainable solution to monitor events in some specific site collections
including sensitive data. I implemented the standard SharePoint audit logging. By
default you can export and view the audit data then as Excel pivot table. I
implemented the following configuration parameters:
- Capture all document and item events: all flags
- Capture all lists, libraries, sites: all flags
With this initial configuration I wanted to make sure, that the audit
logs are not deleted automatically. But in fact 30 days later all my audit logs
were deleted by SharePoint. I did some research and detected, that a time job
called "SITECOLLECTION Audit Log Trimming" deleted all the audit
logs.
Even if you configured no audit log trimming, this job will delete your
logs after 30 days. If you disable the job, then logs won’t be deleted anymore.
Ok, now I thought I’m cool. But then the next issue bumped up. The
content database of the audit enabled site collection grew heavily. MS SQL
table size report detected, that the audit data table contains 12mio records
and uses about 5 GB of disk space and this were just the audit logs of 1.5
months. You can minimize the logged data amount when you don’t log all actions.
For example it is mostly not necessary to log the view and file open events because this is logged in web
analytics also and there is no relevant document change in this case.
These are the attributes which are logged with each audit log record:
Based on compliance regulations, my customer needs to keep all data for
10 years. If the database grows that fast, it will grow too large and impact
the performance of SharePoint and later will become unusable one day, because
of it’s size. So i decided to offload the audit log table from time to time
with SSIS to another database server and archive it there.
Based on the above table structure, you can see that there’s no primary
key logged in this table. So there’s no unique identifier to match the
production and archive data from time to time without data loss.
There were two options to solve the issue. First option was a custom
development to synchronize production and archive database, based on a web
service or a sql trigger. The web service option was time and cost intensive
and a sql trigger should not be inserted in the SharePoint database, because
you‘ll lose Microsoft product support.
Finally I started search for a 3rd party solution to cover this issue
and discovered Idera Audit Management which provides the following features:
·
Easily identifies and
alerts on security issues
·
Helps with SharePoint
governance and regulatory compliance
·
Adds new events not
available in SharePoint’s native auditing
·
Safeguards SharePoint
performance with an external reporting database
·
Find out what’s going on in
detail with out-of-the-box reports
This was exactly was I was searching for. I installed a trial version
and configured the necessary things. After some days of production use the
customer licensed the product and was happy with the solution provided. I'm not
paid to write this article, I'm just happy to have the ability to provide real
audit logging including offloading and archiving of audit databases to my
customers.
Some from the ISV about Idera Audit Management:
Idera SharePoint audit is a comprehensive SharePoint auditing solution that captures permission
changes as well as log on events, views, inserts, updates, deletes, and
changes, all the way to the field level. SharePoint audit gives you a complete
view of who is doing what and where in your SharePoint environment. It enables
you to automatically turn on auditing for new SharePoint sites, configure
specialized auditing at the web application or site collection level, and
monitor the level of auditing across your farm. It collects the data you need
in order to comply with regulations and data security requirements, such as
Sarbanes Oxley (SOX) and HIPAA.
