Usage Reporting in SharePoint (WSS or MOSS)

 

SharePoint (WSS or MOSS) have built-in usage reporting feature. Usage reporting is a service that enables site administrators, site collection administrators, and Shared Services Provider (SSP) administrators to monitor statistics about the use of their sites. Usage reporting also includes usage reporting for search queries that can be viewed by SSP administrators for search and site collection administrators.

Usage reporting is very useful for managing complex site hierarchies with many sites, a large number of page hits, and a large number of search queries, and it is recommended that the service be enabled for deployments of complex site hierarchies.

To configure usage reporting, a farm administrator must first enable Windows SharePoint Services usage logging for the farm that hosts the Web application containing the SSP. The SSP administrator enables and configures the usage reporting service. Then, site collection administrators can activate the 'reporting feature' to enable usage reports on the site collection.

After usage reporting is enabled, site administrators and site collection administrators can view site usage summary pages that have the following information for their sites and site collections (SpUsageSite.aspx):

  • Requests and queries in the last day and the last 30 days.
  • Average number of requests per day over the last 30 days.
  • A chart of requests per day over the last 30 days.
  • A list of the top page requests over the last 30 days.
  • A list of top users over the last 30 days.
  • A chart of top referring hosts over the last 30 days.
  • A chart of top referring pages over the last 30 days.
  • A list of top destination pages over the last 30 days.
  • Top queries for the last 30 days (if search usage reporting is enabled).
  • Search results top destination pages (if search usage reporting is enabled).

SSP administrators for the search service can view a search usage reports page that tracks the following information (SpUsageSiteSearchQueries.aspx)

  • Number of queries per day over the previous 30 days.
  • Number of queries per month over the previous 12 months.
  • Top queries over the previous 30 days.
  • Top site collections originating queries over the previous 30 days.
  • Queries per search scope over the previous 30 days.

Site collection administrators for the SSP site can view a usage summary page that tracks the following information (usage.aspx)

  • Total amount of storage used by the site collection.
  • Percent of storage space used by Web Discussions.
  • Maximum storage space allowed.
  • Number of users for all sites in the hierarchy.
  • Total hits and recent bandwidth usage across all sites.

Windows SharePoint Services (WSS) 3.0 provides the most basic of reports which are primarily text-based. Site collection administrators can also view a site usage report that includes monthly and daily page hit totals filtered by the following criteria

   - Page
   - User
  -  Operating system
  -  Browser
  -  Referrer URL

Enable Windows SharePoint Services Usage Logging

Enable Windows SharePoint Services usage logging for the farm hosting the Web application. Please refer the following steps to enable usage logging for the farm:

  • On the Central Administration home page, click Operations.
  • On the Operations page, in the ‘Logging and Reporting’ section, click ‘Usage analysis processing’.
  • On the ‘Usage Analysis Processing’ page, in the ‘Logging Settings’ section, select ‘Enable logging’.
  • Type a log file location (by default it is set to ' <%Program Files%\Common Files\Microsoft Shared\Web Server Extensions\12\logs> ' folder) and number of log files to create.
  • In the ‘Processing Settings’ section, select ‘Enable usage analysis processing’, and then select a time to run usage processing.
  • Click OK.

Enable Office SharePoint Usage Reporting

After Windows SharePoint Services usage logging is enabled in the server farm, SSP administrators must enable the Office SharePoint Usage Reporting service. SSP administrators can control the complexity of usage analysis processing, and select whether or not reporting is enabled for search queries. Please refer the following steps to enable portal usage reporting:

  • On Central Administration home page, click the Shared Service Provider listed under Shared Services Administration in the Quick Launch bar.
  • On the SSP home page, in the Office SharePoint Usage Reporting section, click Usage reporting.
  • On the Configure Advanced Usage Analysis Processing page, in the Processing Settings section, click Enable advanced usage analysis processing.
  • In the Search Query Logging section, select Enable search query logging.
  • Click OK.

Note:  If advanced usage analysis processing is not selected, usage reporting statistics will be minimal.

Reset Internet Information Server

  • Go to the Start button and click Run
  • Type IISReset and click OK.

Activate Office SharePoint Usage Reporting

After Office SharePoint Usage Reporting is enabled for the SSP, site collection administrators must activate the reporting feature. Until the reporting feature is activated on a site collection, usage reports are not available.

Please refer the following steps to activate the reporting feature:

  • On the Site Actions menu, click Site Settings.
  • On the Site Settings page, in the Site Collection Administration section, click Site collection features.
  • On the Site Collection Features page, click the Activate button for the Reporting feature.

Note: You must enable Office SharePoint Server Standard Site Collection Features for reporting to work.

How this data makes it back to DB, differs for WSS and MOSS

  • In WSS, a timer job called Usage Analysis, runs on each WFE and is then responsible for parsing the usage files and updating the appropriate information for each site’s content DB.
  • In MOSS, a timer job called Office SharePoint Usage Analytics Log Import, runs on each WFE and is responsible for parsing the usage files and uploading this data in the SSP DB

More Information

Whenever Usage Analysis is enabled, the Web Application Servers begin creating usage analysis logs in the ' <Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Logs> ' path. There will be a separate folder named with a GUID that represents the web application. Within each of these folders will be a subfolder for each days logs which in turn contains usage logs in the format 01.log, 02.log etc. The usage analysis job runs against the data collected from the previous day(s) logs. For this reason running the Usage Analysis job more than once per day will not update usage data.

-  On a high level, this is how the usage reports are generated

  • IIS keeps all the SharePoint usage records in memory and will only dump it into physical file (the usage log) when memory is full or IISRESET
  • By default, MOSS has daily timer job (Office SharePoint Usage Analytics Log Import ) to process these physical files into database's temp usage tables
  • Be default, MOSS has another hourly or minutely timer job (Office SharePoint Usage Analytics Processing) that process data in those temp/shadow tables into the real usage tables.
  • Once this is completed, usage report would then be available for viewing.

WSS 3.0 inserts an ampersand ( & ) between the top-level site URL and the sub site URL when it processes the log files. This marks the log file as "processed" and prevents data from being counted twice if the usage processing job is accidentally run again on the same day. If the connection is located in this file and has the ampersand ( & ), you will see it reflected in the usage data.

In MOSS, the two important timer jobs which are responsible for parsing, processing and updating the SSP DB (the ANL tables) are

Office SharePoint Usage Analytics Log Import’ and 'SharePoint Usage Analytics Log processing'.

These jobs run on each WFE in the farm on a daily basis to process the data in the shadow tables and write the final usage data into another set of tables like ANLHit.

Office SharePoint Usage Analytics Log Import’ job is responsible for parsing and populating the usage report data in the SSP DB’s analytics tables (that use the ANL prefix) and runs daily to pick up yesterday and only yesterday’s usage log files and parse them into the SSP table like 'ANLShadowHit' while 'SharePoint Usage Analytics Log Processing' job runs on hourly or minutely basis to process the data in the above shadow tables and write the final usage data into another set of tables like ANLHit.

The following categories are the most relevant for usage reports

  • Office SharePoint Usage Analytics Processing
  • Office SharePoint Usage Analytics Log Import
  • Microsoft.SharePoint.Administration.SPUsageAnalysisJobDefinition
  • Microsoft.SharePoint.Portal.Analytics.UsageProcessingJobDefinition
  • Microsoft.SharePoint.Portal.Analytics.LogImportJobDefinition

Report pages

The https://_layouts/usageDetails.aspx even shows you the total hits for the documents which are very useful information.

Names of the application pages in the '_layouts' directory which show usage analysis data

  • usage.aspx
  • usageDetails.aspx
  • SpUsageSite.aspx
  • SpUsageWeb.aspx
  • SpUsageSiteQueries.aspx
  • SpUsageSiteResults.aspx

All of these pages reside in the ‘_layouts’ folder of the site. Now, the information in the "Advanced" reports (SPUsageSite.aspx) isn't exactly the same as that in the basic WSS reports (usageDetails.aspx).

5u0agfed

MOSS Usage Reports explained provides a very good insight into the kind the data for each parameter in the Site Collection Usage Summary page.

Mark Arend has written an excellent post with detailed descriptions of parameters displayed in MOSS 2007’s usage reports on the pages like SpUsageWeb.aspx (Site), SpUsageSite.aspx (Site Collection), SpUsageWebTopPages.aspx (Site), SpUsageSiteTopPages.aspx (Site Collection) and so on.

There are two report pages that are extremely useful, particularly for slightly smaller sites, that cannot be reached through the GUI interface in MOSS 2007. They are actually from the basic WSS system, and MOSS inexplicably misses out any direct reference though the administration pages.

  • /layouts/usage.aspx                  This page brings data from the content database, which is the total hit for the page from ALL locations
  • /_layouts/usageDetails.aspx-   This URL brings data from ‘ SharedServices_DB ‘ , which is processed thru multiple SQL table views and stored. It results only the hits to the page FROM A specific site collection.

In short, the data on the ‘usagedetails.aspx’ page is calculated for any hit (success or failure) to the location whereas the data in the ‘spUsageSite.aspx’ page shows the page which was accessed (and the number of times it was access in the Pie chart) FROM the site collection.

These are the definitions used by WSS and MOSS in summary usage reports (which are stored in the web metainfo):

  1. Visit:         a “total hit” that does not come from within the same server; that is, it either has no Referrer header, or it has a Referrer header from   
    another server
  2. Total hit: any hit that gets logged in the WSS http logs (we don’t log hits that result in error http results, or hits to the _layouts directory)
  3. Hit:           anything in (2) except hits on files with these extensions: "gif","jpg","png","bmp","css","mid","wav","ico","xml","au","js","class"
  4. Request:  Requests always measures Page Views, not all HTTP requests for individual items like images, style sheets, etc.

How Usage Analysis works

All WFEs behave in the same way as long as the Windows SharePoint Web Services service is running on each WFE. HTTP data from each WFE is collected and stored locally on disk. The method and process by which this data is persisted on disk is described in Usage Event Logging in Windows SharePoint Services 3.0. This behavior is the same for all WFEs.

How this data makes it back to DB, differs for WSS and MOSS

   In WSS, a timer job called Usage Analysis, runs on each WFE and is responsible for parsing the usage log files and updating the information in the site’s content DB. 
    In MOSS, a timer job called Office SharePoint Usage Analytics Log Import, runs on each WFE and is responsible for parsing the usage log files and uploading this data in the SSP DB (for SSP’s that have usage turned on, as per the instructions that you quote below)

  • The Office SharePoint Usage Analytics Log Processing job is responsible for parsing and populating the usage report data in the SSP DB’s analytics tables (that use the ANL prefix)
  • It runs every 15 mins, to check is there’s new data imported (from the Office SharePoint Usage Analytics Log Import job) so that the reports are updated
  • It also expires detailed data (kept only for 30 days) and report data (kept for 365 days)
  • Windows SharePoint Services 3.0 generates usage event logs daily for each Web application when 'Enable logging' is selected on the Usage Analysis Processing page in SharePoint Central Administration
  • When logging is enabled, Windows SharePoint Services by default creates log files in the 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Logs' path on the front-end Web server, although you can specify an alternative location
  • The Logs directory contains a folder for each Web application on the Web server, each named with a GUID that identifies the respective Web application
  • Windows SharePoint Services 3.0 inserts an ampersand (&) between the top-level site URL and the sub site URL when it processes the log files
  • This marks the log file as "processed" and prevents data from being counted twice if the usage processing job is accidentally run again on the same day

If the connection is located in this file and has the ampersand (&) you will see it reflected in the usage data.

  • By default log files will be created under C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Logs. In side this folder, there would be a folder for each web application named as GUID of respective web application and inside it, folder for each day.
  • The log file location can be customized through Central Administration, Operations, Usage Analysis Processing. However its mandatory that WSS_WPG user group Read, Write, and Update permissions to the directory. Without it, log files will not be processed.

The default page for usage reporting has the most reporting parts; each part executes at least one stored procedure to retrieve its data. Each part is also found on other reporting pages.

  • Site summary
  • Top Pages (Average requests per day over past 30 days)        https://<WebUrl>/_layouts/SpUsageWebTopPages.aspx
  • Top Users (Average requests per day over past 30 days)         https://<WebUrl>/_layouts/SpUsageWebUsers.aspx
  • Top referring pages (past 30 days)                                              https://<WebUrl>/_layouts/SpUsageWebReferrers.aspx
  • Top referring hosts (past 30 days)                                               https://<WebUrl>/_layouts/SpUsageWebReferrers.aspx
  • Requests per day (past 30 days)                                                   https://<WebUrl>/_layouts/SpUsageWebTopPages.aspx

Who can view these Reports

Site Collection Admins or users will Full Control permissions can view Site Collection and Site Usage Reports.  So if you want other users to access these reports you can do the following:

  • Create a custom site permission level and select the “View Usage Data” check box
    Note: this will also select the “View Pages” permission
  • Create a new Group called something like “Usage Report Viewers” and assign the permission level you created above to the group
  • Add the people you want to view the reports
  • Provide the users with links to the report pages.

You can also use a web part to display the links and above and audience targeting so only the users in the SharePoint group “Usage Report Viewers” see the web part.

FAQs (A few common questions regarding usage reports)

  • Is it possible to report on the Usage Analysis data for more than 30 days?
    - Yes
  • After 30 days, where does the data go? Does it get purged?

- The data is kept in two spots. One is on the file system where you specified the logs to be kept. The second is in the database.
- The data is not discarded, it is still kept in the database and on the file system unless you attempt to remove it.

  • If the data still exists after 30 days, is there a way to report on this?
    - Out of Box there is no option for this; however it is possible to pull up Usage Analysis reports past 30 days with SharePoint Designer.
    - The reason it is limited to 30 days is because any query that pulls the data for a period of more than 30 days through SharePoint, can cause a
       performance hit as it will task the SQL Server while attempting to pull this information.
  • If there any documentation available that could be provided to our developers.
    - If the developers are looking for the specific tables being used for this process, it would be referenced in the SharePoint Software Development Kit.

Here are the direct links to the SharePoint SDKs:

- WSS 3.0 :       https://msdn.microsoft.com/en-us/library/ms441339.aspx
- MOSS 2007:  https://msdn.microsoft.com/en-us/library/ms550992.aspx

**References**