none
Queries related to logs generated for sharepoint server 2007 RRS feed

  • Question

  •  

    I am doing analysis for the audit logs generated in sharepoint server 2007. In the sharepoint database where the logs are getting stored the events are displayed in numerical form (in the Sharepoint_AdminContent database,dbo.AuditData table).  I guess these event numbers are getting mapped to some event names.

     

    1.) Can you please let me know whether this event number-event name mapping is done in any table in the database? if yes, then in which table?

    2.) Also, if  such a mapping is not done in the database, how can I do it myself?

    3.) In the dbo.AuditData table  fields called SitID,ItemID and UserID are also given.Can you please let me know how are the databases and tables interacting with each other and where are these ID's mapped to their respective names?

    Tuesday, December 4, 2007 6:56 AM

All replies

  • I have done the mapping by myself.

    - CheckOut = 1
    - CheckIn = 2
    - View = 3
    - Delete = 4
    - Update = 5
    - Undelete = 10
    - Copy = 12
    - Move = 13

    Database Table -

    -- AuditData - Table that holds information about all the auditing related data
    -  AuditData - Important Columns -
    -- SiteId - Site Collection Id
    -- ItemId - Id for item on which auditing is recorded
    -- UserId - UserId for the user performing the auditing action
    -- DocLocation - Location of Auditing Document
    -- Occurred - Date and Time for the auditing event
    -- Event - Event Id based on enumeration

    Here are some common queries that we can run against the content databases for auditing records -

    -- Query for getting auditing data for a particular document with user details

    SELECT TP_TITLE,TP_LOGIN,DOCLOCATION,OCCURRED,EVENT
    FROM AUDITDATA INNER JOIN USERINFO
    ON AUDITDATA.USERID = USERINFO.TP_ID AND AUDITDATA.SITEID
    = USERINFO.TP_SITEID
    WHERE AUDITDATA.DOCLOCATION LIKE @SearchDocument AND USERINFO.TP_ISACTIVE =1


    -- Query for getting auditing data for a particular site collection

    SELECT AUDITDATA.DOCLOCATION,AUDITDATA.OCCURRED,
    AUDITDATA.EVENT,SITES.ID
    FROM AUDITDATA INNER JOIN SITES
    ON AUDITDATA.SITEID = SITES.ID
    WHERE SITES.ID = @SiteID

    NOTE: Never update any SharePoint database directly. Always use the SharePoint API (Object Model) for any updates.

    Wednesday, August 13, 2008 8:47 PM
  • Gracias me sirvió la respuesta.

    Thanks, your answer is good for my case.

    Thursday, November 15, 2012 10:19 PM