Extending The SharePoint 2010 Health & Usage – Part 3: Writing Custom Reports

This is the third article of a 4 part series where I discuss the extensibility of the new Health and Usage Services built into SharePoint 2010. In the second article we created a custom Usage Provider which collected download information and stored it into the Usage Database. Now its time to take a look at the data which has been collected and report on it. We will do this by extending the Health Reports which currently only include reports for Slowest Pages and Top Active Users – we should have more and we will.. 

Other articles in this series are as follows:

  1. Feature and Capability Overview
  2. Writing a Custom Usage Provider
  3. Writing Custom Reports (this article)
  4. Writing a Custom Usage Receiver

Code Download: Microsoft.SP.Usage.zip (160 kb)

The Scenario

As a reminder, I am walking you through the building a solution to track file downloads from SharePoint Document Libraries and report on the results. It’s key that this solution be scalable so we will are creating a custom usage provider, adding a few custom usage reports in Central Administration, and we will soon create a usage receiver to help us display download information within a Web Part for each SPSite within our environment.

Overview

The object of this article is to show how to create custom usage reports and expose them into the Health Reports area within SharePoint’s Central Administration.The process starts with defining the reports we would like to create, write a bit of TSQL to query the Usage DB to generate the reports, and finally register the reports with the SharePoint Health Report Store so the reports are available to users of Central Administration.

Defining Reports

For this scenario we are going to create three reports to report on our document download usage data. Each report will have a display name or title and will offer up a hopefully interesting view or pivot on the usage data we have collected. The table below documents the three reports we will create:

Report Name Description
Top Downloaded Documents Show the top downloaded documents by the number of downloads and include the URL, file name, extension, file size and the total size of all downloads, and the number of downloads.
Top Downloaded Extensions Show the top downloaded documents by extension/document type. Include the average. minimum, maximum, and total download sizes as well as the total number of downloads for each extension.
Downloads By Day of Week For each day of the week (Monday, Tuesday, etc.) show the total number of documents downloaded and the total size of all downloaded documents for the day.

 

Building the Query

With the reports defined lets start building our queries. The Health Reports offer a few filters which we can choose to support when building our queries which include the Server Name/machine on which the usage occurred, web application, number of items, and a date range of ‘last day’, ‘last week’, or ‘last month’. We have a choice when it comes to supporting these filters, which we will see later, but for our case we will support all of them for every report.blog-usage-image5

With Health Reports you have the option of supplying TSQL or Stored Procedures which will exist in the Usage DB and will be executed each time the report is requested.  For our solution I will use a stored procedure for each of the reports. To support all the filters mentioned previously we need to use a certain pattern for the stored procedure.

CREATE PROCEDURE [dbo].[proc_{NAME}]
   @StartTime               datetime			= NULL,
   @EndTime                 datetime			= NULL,
   @WebApplicationId        uniqueIdentifier		= NULL, 
   @MachineName             nchar(128)			= NULL,
   @MaxRows                 bigint			= 100
AS
BEGIN

	/*query goes here*/

END

As you can see from the stored procedure stub above I have supplied parameters and defaults for my stored procedure which SharePoint will populate with values as the user chooses various filters. For best results I would highly recommend using this pattern as it has worked for me 100% of the time so in my mind I put that in the “proven” or “best practice” column. Now another best practice, or to put it another way, it will break if you don’t do it this way, use owner specified stored procedures and user defined functions. During the provisioning of our reports, which I will get to later, we are going to write TSQL which will provision our stored procedures and UDFs into the Usage DB. This process runs within OWSTimer and as a result runs as the SharePoint Farm Account. As you can see from the image below the default schema for this account is DOMAIN\FarmAccountName. So if you do not owner specify you will end up with a naming issue as the artifacts will be provisioned as DOMAIN\FarmAccount.StoredProcedureName which is the same issue you will run into with the UPA service which I wrote about here. DBO as the schema is not required but ensure you use the same schema name in the TSQL you use to create your stored procedures as you do when you register them with the Health Report Store.

blog-usage-image6

Using the pattern I introduced previously I have written the 3 stored procedures which will be used for 3 reports which we will define. Notice that each leverage an OOB User Defined Function (UDF), fn_PartitionIdRangeMonthly which helps with the date ranges which may be passed into the stored procedure. As mentioned in the first article of this series each usage data provider has its data partitioned into 32 tables where each table represents one day of usage data based on UTC. That is, the rollover to the next table occurs at midnight UTC which is not always the local server time. This helper UDF returns a range of partition IDs for each table that fits within a supplied date range.

When writing the TSQL its important to understand the column names and column order matter and are used within the report just as they are returned. So instead of returning ‘extension’ I return ‘File Extension’ as a more friendly and meaningful name. So take care to return only the data you intend or it will wind up on your report. Keep in mind too that should you support filters the user may or may not select a filter value so you should deal with the fact the parameter may be NULL and ensure you handle that appropriately in your query.

CREATE PROCEDURE [dbo].[proc_GetTopDownloadedDocuments]
   @StartTime               datetime			= NULL,
   @EndTime                 datetime			= NULL,
   @WebApplicationId        uniqueIdentifier		= NULL, 
   @MachineName             nchar(128)			= NULL,
   @MaxRows                 bigint			= 100
AS
BEGIN

	SELECT TOP(@MaxRows) [Url], [FileName] AS [File Name], [Extension] AS [File Extension], 
		   AVG(Size) as [File Size], SUM(Size) as [Total Download Size], COUNT(RowId) AS [Downloads]	
	FROM [dbo].[DownloadUsage] WITH (READPAST)
    	WHERE  [PartitionId] IN (SELECT PartitionId FROM dbo.fn_PartitionIdRangeMonthly(@StartTime, @EndTime)) 
	AND ([LogTime] BETWEEN @StartTime AND @EndTime)
	AND (@WebApplicationId IS NULL OR WebAppId = @WebApplicationId)
 	AND (@MachineName IS NULL OR MachineName = @MachineName)
	GROUP BY [Url], [FileName], [Extension]
	ORDER BY  Count(RowId) DESC

END
GO

CREATE PROCEDURE [dbo].[proc_GetTopDownloadedExtensions]
   @StartTime               datetime			= NULL,
   @EndTime                 datetime			= NULL,
   @WebApplicationId        uniqueIdentifier		= NULL, 
   @MachineName             nchar(128)			= NULL,
   @MaxRows                 bigint			= 100
AS
BEGIN

	SELECT TOP(@MaxRows) [Extension] AS [File Extension], AVG(Size) AS [Average Size], 
		   MIN(Size) AS [Minimum Size], MAX(Size) AS [Maximum Size], SUM(Size) as [Total Download Size], COUNT(RowId) AS [Downloads]	
	FROM [dbo].[DownloadUsage] WITH (READPAST)
	WHERE  [PartitionId] IN (SELECT PartitionId FROM dbo.fn_PartitionIdRangeMonthly(@StartTime, @EndTime)) 
	AND ([LogTime] BETWEEN @StartTime AND @EndTime)
	AND (@WebApplicationId IS NULL OR WebAppId = @WebApplicationId)
	AND (@MachineName IS NULL OR MachineName = @MachineName)
	GROUP BY [Extension]
	ORDER BY  Count(RowId) DESC

END
GO

CREATE PROCEDURE [dbo].[proc_GetDownloadedByDOW]
   @StartTime               datetime			= NULL,
   @EndTime                 datetime			= NULL,
   @WebApplicationId        uniqueIdentifier		= NULL, 
   @MachineName             nchar(128)			= NULL,
   @MaxRows                 bigint			= 100
AS
BEGIN

	SELECT TOP(@MaxRows) [dbo].[GetWeekDayName]([LogTime]) as [Day of Week], SUM(Size) as [Total Download Size], COUNT(RowId) AS [Downloads]	
	FROM [dbo].[DownloadUsage] WITH (READPAST)
	WHERE [PartitionId] IN (SELECT PartitionId FROM dbo.fn_PartitionIdRangeMonthly(@StartTime, @EndTime)) 
	AND ([LogTime] BETWEEN @StartTime AND @EndTime)
	AND (@WebApplicationId IS NULL OR WebAppId = @WebApplicationId)
	AND (@MachineName IS NULL OR MachineName = @MachineName)
	GROUP BY [Extension]
	ORDER BY  Count(RowId) DESC

END

blog-usage-image9

All of this TSQL is included in two different *.sql files within my solution where I have split my Install and Uninstall script. Just to be safe I have included checks during provisioning to remove any previous instance of a stored procedure before creating it again. I have marked each *.sql file’s build action with Embedded Resource in Visual Studio so it will include these files as string resources within the assembly when compiled. During my provisioning process I read from the string resource and pass this to SQL server to create or drop the stored procedures. This pattern allows me to iterate on various versions of the stored procedures from a single file without having to redeploy the solution each time.

At this point you should be doing a Different Strokes “What you talkin’ bout Willis” when I talk about creating stored procedures in the Usage DB. The Usage DB is a SharePoint database and as we all know changing the DB Schema of a SharePoint DB puts you into the realm of “The Unsupported”. The Usage DB is a little different in that its not critical path to how SharePoint operates. That is, all the data contained within the Usage DB for the most part is throw away. I have not heard of any heart ache arising from customers querying this data or using extensibility interfaces to extend the schema. This only applies to the Usage DB however so don’t try to do this for any other SharePoint DB. Since the Usage DB can grow quite large and is write optimized in most cases it makes since to move this data into a warehouse for reporting historical and trending analysis.

Report Registration

At this point its time to build the component which will register our Health Reports with the Report Store. For my implementation I created a static class named DownloadUsageReports with two public methods Register() and Unregister() which I have included below. I start the registration process by provisioning the stored procedures which, as I mentioned previously, the script is pulled from string resources embedded within the assembly.  With the schema changes made I then grab a local reference to the SPHealthReportStore (found in the Microsoft.SharePoint.Administration namespace) and call into the AddHealthReport() method passing a newly constructed SPHealthReportCreationData object. The SPHealthReportCreationData is where we supply the name of the report, as it will appear in the UI, the TSQL we want to execute each time the report is executed, a Boolean indicating if the TSQL passed is a stored procedure, and then a number of additional Booleans which indicate what filters we support. For all of my reports I support everything so ‘true’ is passed all around. The report name is the primary key and as a good citizen I check to ensure the report does not already exist before attempting to add it again.

Backing out the reports is even easier and includes a call to RemoveHealthReport() passing the report name for each report I want to back out of the Health Report Store. As with almost anything SharePoint you have done nothing until you call Update() so never forget this part.

 

        public static void Register()
        {

            RegisterReportingStoredProcsInUsageDatabase();

            SPHealthReportStore store = SPHealthReportStore.Local;

            if (store != null)
            {
                if (!store.Contains(Constants.TopDownloadedDocReportName))
                {

                    SPHealthReportStore.Local.AddHealthReport(new SPHealthReportCreationData(Constants.TopDownloadedDocReportName,
                                                                                                Constants.TopDownloadedDocProcName,
                                                                                                true, //Is Stored Proceedure
                                                                                                true, //Support Web Application Filter
                                                                                                true, //Supports Time Filter
                                                                                                true, //Support Machine Name Filter
                                                                                                true)); //Supports Max Rows

                }


                if (!store.Contains(Constants.TopDownloadedExtReportName))
                {

                    SPHealthReportStore.Local.AddHealthReport(new SPHealthReportCreationData(Constants.TopDownloadedExtReportName,
                                                                                                Constants.TopDownloadedExtProcName,
                                                                                                true, //Is Stored Proceedure
                                                                                                true, //Support Web Application Filter
                                                                                                true, //Supports Time Filter
                                                                                                true, //Support Machine Name Filter
                                                                                                true)); //Supports Max Rows

                }


                if (!store.Contains(Constants.DownloadedByDOWReportName))
                {

                    SPHealthReportStore.Local.AddHealthReport(new SPHealthReportCreationData(Constants.DownloadedByDOWReportName,
                                                                                                Constants.DownloadedByDOWProcName,
                                                                                                true, //Is Stored Proceedure
                                                                                                true, //Support Web Application Filter
                                                                                                true, //Supports Time Filter
                                                                                                true, //Support Machine Name Filter
                                                                                                true)); //Supports Max Rows

                }


                store.Update();
            }

        }
        
        public static void Unregister()
        {

            UnregisterReportingStoredProcsInUsageDatabase();


            SPHealthReportStore store = SPHealthReportStore.Local;

            if (store != null)
            {

                store.RemoveHealthReport(Constants.TopDownloadedDocReportName);
                store.RemoveHealthReport(Constants.TopDownloadedExtReportName);
                store.RemoveHealthReport(Constants.DownloadedByDOWReportName);

                store.Update();

            }

        }

 

Usage Reporting

Included below are a few screen shots with each representing the reports we defined earlier.

blog-usage-image10blog-usage-image11blog-usage-image12

Unfortunately we cannot control the column width or supply any additional formatting on pre-render so things like the Total Download Size will be written as 32488646 rather than 324,88,646. Now of course you could do some formatting within your stored procedure however keep in mind that each column in this report is sortable so any formatting you do which may change the column sort is just going to confuse your users more than if you just left the formatting as is. Another option is to create application pages and deploy those into Central Administration and use additional reporting techniques such as visualization by leveraging OOB SharePoint chart controls or 3rd party controls (ComponentArt controls were used for the screenshot below).

blog-usage-image13

Conclusion

As you have seen creating reports for your Usage data is relatively trivial. The tax you pay for this ease however is the limited formatting you can apply to the Health Reports. The reports we show here however are only available to Central Administration users and our end users cannot take advantage of this information. Since we do not want to open our Usage DB up to direct queries outside of Central Administration but we may want to expose a subset of this data to our user base we need to extend the solution further. In the next, and last, article in this series I will show you how to create a Usage Receiver which we can leverage to expose some usage data to end users.

Post to Twitter Post to Facebook Post to LinkedIn Post to Delicious Post to Digg

2 thoughts on “Extending The SharePoint 2010 Health & Usage – Part 3: Writing Custom Reports”

  1. Hi Todd,
    This is very elaborated and nice article.But I have a simple problem. As we know there are some default reports in which, for example: top visitors of the site is showing the login userid. I want to show the user full name from somewhere (AD ?) is there any simple or easy way to do this ? or do I need to create all new custom report?

    1. I would create a new custom report. I am not confident you can do with with the OOTB reports as they are not available to be modified.

Leave a Reply

Your email address will not be published. Required fields are marked *