none
WSS_Logging Database

    Question

  • Hi,

    In SharePoint 2010, Microsoft provides the WSS_Logging DB. This DB is allowed to query directly (unlike the WSS_Content DB).

    Is anyone here familiar with this DB?

    Where can I find documentation about it? (Not general information about the DB, but detailed explanations about the tables & columns).

    And a few specific questions:

    -           What's the difference between the tables RequestUsage, ImportUsage and ExportUsage?

    -           What kinds of requests are audited in RequestUsage table? How can I distinguish between them?

    -           What's the meaning of the column RequestCount in RequestUsage table? If every row is one request (as it teems from the datetime field LogTime), how can one row represent several requests?

    -           How can it be that there're requests with the exact time (in both LogTime and RowCreatedTime)?

    -           How can I get information about documents usage? Like who opened/edited/saved documents, and what documents?

    -           How come this DB stores information about the past 14 days only, while the Sharepoint built-in web analytic reports present earlier data?

     

    Just to clarify – I'm only interested in querying the WSS_Logging directy (via SQL-Server Management Studio). I don't want to use the uilt-in web analytic reports.

     

    ANY HELP WOULD BE APPRICIATE… J

     

    Thanks a lot!

    Mauro

    Thursday, July 21, 2011 2:16 PM

Answers

All replies

  • Hi Mauro,

    Here you find some information about this database and what you can do with it:

    http://sharepoint.microsoft.com/Blogs/fromthefield/Lists/Posts/Post.aspx?ID=124

     


    Regards

    Henrik A. Halmstrand
    http:/www.sharepointrevealed.com
    http://getspconfig.codeplex.com
    Thursday, July 21, 2011 3:06 PM
  • Hi Henrik,

    Thank you for the link. However it was too general for what I needed.

    Anyway I found what I was looking for and I'm now sharing it for whoever needs it.

    I wanted to get information about documents usage. It seems that the best way to do this is to use the WebAnalyticsServiceApplication_StagingDB database, using this query:

     

    SELECT

    clicks.ClickId,

    assets.AssetId,

    clicks.UserId,

    clicks.DateId,

    clicks.ClickTime

       FROM [WebAnalyticsServiceApplication_StagingDB_283db7db-e263-4183-84f3-49066f9b9e57].[dbo].[WAClickFact] clicks

      inner join [WebAnalyticsServiceApplication_StagingDB_283db7db-e263-4183-84f3-49066f9b9e57].[dbo].[WAAssetMetadata] assets

      on clicks.ClickedAssetIdHash = assets.AssetIdHash

                                                                           

    The query result is a list of all the clicks that was made, including document openings. Since regular pages (ASPX pages) are also included, you need to add a filter that will keep only the real documents, like "AssetId  like %.doc% or AssetId  like %.xls% or AssetId  like %.pdf%" etc.

     

    What's good is that even if the user opens a document directly from a client like MS-Word, without using the browser, the action will still be saved in that database

     

    Note that the table keeps data from the last few days only, so if you want to save more history, you'll need to periodically export the data to a separate file so that old data won’t be lost.

     

    Mauro

    Thursday, August 04, 2011 10:26 AM
  • Thank you Mauros , the select statment worked with me ,

    then a conclusion and please correct me if im wrong, Wss_logging , contains the audit data , if audit is enabled.

    and when i generate an audit report , it gets  the data from this database and show it to me in a excel file.


    Karim El Zarka, MCTS Sharepoint 2010 , MCITP SharePoint Administrator 2010

    Tuesday, October 02, 2012 2:30 PM