none
SharePoint auditing tables

    Question

  • Hi All,

        I am SQL Server DBA and as per our client requirements our sharepoint administrator had enabled auditing on sharepoint collection and from the audit logs in sharepoint we can see that events are getting captures. My question are

    1. Whether these events are captured in respective content databases in sql server or wss_content database only

    2. Also do we need to enable auditing for each site collection. Cant we enable auditing at a single location.

    My client requires me to pull the information from sql server...


    Chandoo

    Wednesday, October 30, 2013 6:44 AM

Answers

  • You need to be leveraging the SPAudit API to get data from the audit logs.  WSS_Usage database is not going to contain audit information, all of that will be stored in the Content Database where the Site Collection resides.

    There are 3rd party products, such as Metalogix ControlPoint which will help manage and archive the audit log (one of it's functions we use is to migrate the audit data from the content database to its own database, where it can be queried directly or via ControlPoint's UI).


    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Sunday, November 03, 2013 3:09 AM
    Moderator

All replies

  • Firstly you shouldn't be looking in the databases. Querying the Databases is not recommended (direct modifications are unsupported) and can lead to performance and stability issues. In some cases i've heard reports it can corrupt the content. The analytics tables are probabaly the least-worst part but even so it's a dangerous path to tread.

    1) I believe that the audit data is stored in the relevant Content DB for the site collection.

    2) It is per site collection, you can use PowerShell to enable auditing for all site collections at once but there isn't a single place that can do that in the GUI.

    Wednesday, October 30, 2013 8:52 AM
  • Hello chandramohan

    There's a variety of different monitoring available, as you would surmise.  Some stored in files and other types in the wss_logging database: usage and health data at the site and collection levels; audit logging is configured at the site collection level. Diagnostic logging at the farm level.  Just look through Site Settings > Site Collection Administration or Central Administration > Monitoring for details.

    Diagnostic logging data is stored in the wss_logging database.  You can configure the farm to store other logging data in this DB too.

    The logging database is the one area of SharePoint administration in which directly accessing a SharePoint database is supported and encouraged.  As a DBA, this reference will be most interesting for you: Understanding the Logging Database (SharePoint Server 2010).

    The references below will help for background.

    Best,

    Steve

    Sunday, November 03, 2013 1:50 AM
  • You need to be leveraging the SPAudit API to get data from the audit logs.  WSS_Usage database is not going to contain audit information, all of that will be stored in the Content Database where the Site Collection resides.

    There are 3rd party products, such as Metalogix ControlPoint which will help manage and archive the audit log (one of it's functions we use is to migrate the audit data from the content database to its own database, where it can be queried directly or via ControlPoint's UI).


    Trevor Seward, MCC

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Sunday, November 03, 2013 3:09 AM
    Moderator
  • 1) Audit Log data for site collection stored into respective content DB.

    2)you should enable for each site collection.

    As Alex mentioned query directly to sharepoint database is not recommended and sometime you will be out of MSFT support.

    here is useful information which you I believe looking for.http://sharepoint-bunny.blogspot.com/2013/01/sharepoint-2010-export-and-archive.html

    If you audit table grows, you should think about trimming via powershell not directly from Sql.http://blog.bugrapostaci.com/2012/09/05/how-to-trim-audit-logs-in-sharepoint-20072010/


    Please remember to mark your question as answered &Vote helpful,if this solves/helps your problem. ****************************************************************************************** Thanks -WS MCITP(SharePoint 2010, 2013) Blog: http://wscheema.com/blog

    Sunday, November 03, 2013 4:41 AM