Request Usage on SharePoint 2013 (Custom Reports)

I already explained in my previous article difference between user activity(user actions) and audit log. SharePoint logs all user actions such as site visits, list/libraries visits, page visits and so on. This information stored in SharePoint logging database. SharePoint has some out of box reports to view these data. Sometimes we need custom reports based on the request usage logged by SharePoint. These reports can be designed directly against SharePoint Logging Database either by creating new tables/views or OOTB views.

Let’s understand how we can achieve custom reports using SharePoint Logging Database.

Location:

You can do monitoring efficiently by using the logging database in SharePoint 2013. The logging database collects information from every server in the farm. The default name of the database is SharePoint_Logging. The logging database provides the option to view and customize various monitoring information in one place.

How:

The logging database uses a separate partition table for the daily data for each category. E.g. SharePoint writes the 1st day of data in dbo.TimerJobUsage_Partition0 table and for 32nd day in dbo.TimerJobUsageUsage_Partition32 table. This means, each partition table stores one particular day’s logs.

The mapping between one partition table and the exact date depends on the logging database retention period and the starting date to write logs into the logging database. You can get the mapping by observing time information inside each table. For example, if the retention period is 5 days and today’s logs are written to partition table 1, tomorrow’s logs will be written into partition table 2 and so on. After 5 days, all logs were gets deleted and new logs are written to the partition tables from first.

Access:

SharePoint Logging database has default views which can be used to access these data which is scattered between these partition tables. One of the default view is RequestUsage. This view provide the combined user activity data from all partition tables.

Actual Implementation: 

     SharePoint Logging database has retention period of 3 days by default. This can be changed using power-shell command. changing these retention period may add more load on database since SharePoint Logs millions of rows per days. I am sure changing this to more than 30 days may add lot of rows and increase the size of logging database too.

       I would recommend the intermediate table which can be used to store this data for more than specified limit days. Follow below steps. (Logging database is the only SharePoint 2013 database for which you can customize reports by directly changing the database).

1. Create new table in SharePoint Logging Database. 
2. Create new custom view which can filter these data by previous day using the above view “RequestUsage”. Be sure to filter the records since “RequestUsage” will give you millions of rows since SharePoint logs each and every minute activity of user. E.g. if you visit one page in SharePoint then it logs each and every entity used on that page as separate rows.
3. Create the SSIS package or executable which will Import the custom view output into the new custom table.
4. Schedule the SSIS Package or executable daily.
5. Create reports based on the new custom table.