Get log entries from the SharePoint 2013 ULS using C#

 

Suppose you aren't happy with the accessibility of the ULS log in SharePoint 2013. Often this is what you get from users who encounter an error:



"Thanks," you say. "Could you please recreate the error and click on the technical details and copy and paste the message to me?"

When you finally get the correlation Id, you have to scour the logs to find the log entries and start tracking down the error. There are various ways to make this process easier. One way is to use some variation of the C# code below:

var ulsLogEntries = new SPULSRetriever(
      300, 1000, DateTime.Now.AddMinutes(-180)).GetULSEntries(new Guid(_correlationId));

The SPULSRetriever class is part of the Microsoft.SharePoint.Diagnostics namespace and allows you to look up log entries for a specific correlation id. There is limited SDK documentation here: http://msdn.microsoft.com/en-us/library/jj168066.aspx

In the example above, the _correlationId variable is created earlier in the code as a string and converted to a Guid before being passed into the GetULSEntries as a parameter. The GetULSEntries method returns a collection of LogFileEntry members. The LogFileEntry members contain the properties for a single log entry.

You can then iterate through the collection and display the log entries in whatever fashion you like. An example of how you could use this is to create an application page available to the support team that allows them to input a correlation id and capture the appropriate log entries. Another possibility is just to add something to the various error pages that automatically sends the log to the support team when a user encounters an error.

Have fun! As always, let me know if you have any comments or feedback.

SharePoint 2013 PowerPivot Component Overview

[This post was completely overhauled on 6/18/2013 to provide more accurate and detailed information]

This post provides a high level overview of the components used in a SharePoint 2013 PowerPivot installation. It includes screenshots to illustrate the components discussed and in some cases I provide links to detailed instructions on how to install and configure the specified component.

Overview of the Component Architecture

The diagram below illustrates the components discussed in this post.

 

In a typical PowerPivot for SharePoint 2013 installation there are two SQL Server 2012 SP1 instances (they do not have to be on the same SQL Server).

• There is at least one database instance for the PowerPivot and Secure Store Service Applications
• There is one Analysis Services instance for PowerPivot, which is acts as the computational engine for Excel Services PowerPivot workbooks.

There are three service applications installed on SharePoint 2013 that are used by a full installation of PowerPivot:

• Secure Store Service Application - claims-aware authorization service that stores and retrieves credentials
• Excel Services Service Application - Browser based Excel for SharePoint
• PowerPivot Service Application - Primarily responsible for scheduled date refresh and usage statistics



Note that Analysis Services and Excel Services are the only things required to view PowerPivot workbooks in SharePoint 2013. The other components give you:

• The PowerPivot Gallery
• The administrator's dashboard
• Usage statistics
• Scheduled data refresh

See http://technet.microsoft.com/en-us/library/ee210692.aspx for TechNet documentation

 

PowerPivot Components in SQL Server 2012 SP1



The screenshot below shows a sample installation of a SQL Server Analysis Services instance used for PowerPivot on SharePoint 2013. Note that the server name ("SPS-SQL" in my example) will vary by installation, but the instance name ("POWERPIVOT") will not. The POWERPIVOT analysis service instance is installed with the SQL Server 2012 Setup Wizard.

 

Detailed instructions on how to setup the POWERPIVOT analysis service instance in SharePoint mode can be found here: http://msdn.microsoft.com/en-us/library/jj219067.aspx

The screenshot below also shows the database  ("SP_PowerPivot" in my example) used by the PowerPivot service application. This database is created by the PowerPivot Configuration Tool.

 

 

Note that there is no Excel Services Database. Excel Services uses cached storage only.

PowerPivot Related Service Applications

The service applications used by PowerPivot can be seen in Central Administration (see screen shot below). The PowerPivot Configuration Tool provided by Microsoft can help you setup and configure all three of these service applications.

Detailed instructions on using the PowerPivot Configuration Tool for SharePoint 2013 can be found here: http://msdn.microsoft.com/en-us/library/hh213153.aspx

 

The PowerPivot Service Application (Mid Tier Service in Health Analyzer Rules) has the following functions:

• Hosts the refresh scheduling engine, which calls Excel Services APIs for data refresh of Excel 2013 workbooks. For Excel 2010 workbooks, the service directly performs the data model processing but continues to reply on Excel Services for loading the data model and updating the workbook.
• Provides methods for components such as the user interface pages, to communicate with the system service.
• Manages requests for external access to workbooks as a data source, received through the PowerPivot Web Service.
• Scheduled data refresh request management for timer jobs and configuration pages. The service manages requests to read data in and out of the service application database and trigger data refresh with Excel Services.
• Usage processing and related timer job.


In the Excel Services, the name of the SQL Server Analysis Services instance used to store and manage PowerPivot workbook data must be identified (see screenshot below).

In the Secure Store Service Application, the PowerPivot Unattended Account for Data Refresh must be configured. The PowerPivot Configuration Tool will create this Unattended Account for you.

PowerPivot SharePoint 2013 Solutions

The farm will have two Globally deployed farm solutions and one farm solution that is deployed to the web application where you will be hosting PowerPivot workbooks (see screenshot below). These solutions are added to the farm by the PowerPivot Configuration Tool.

Your site has to have the PowerPivot Feature enabled to store PowerPivot workbooks, use the data refresh functionality, and display workbooks in the PowerPivot Gallery.

Ultimately, a PowerPivot gallery with a few workbooks in it will look something like this:

Please let me know if this post was helpful to you or if you have a question by writing a comment.

PowerPivot for SharePoint 2013 Account Permissions Overview

Following is a brief overview of the permissions and accounts required for PowerPivot on SharePoint 2013. This overview is intended to provide a basic understanding what accounts are required for PowerPivot to run correctly on a SharePoint 2013 farm.

SQL Server 2012 Analysis Services (POWERPIVOT)

Sample Account Name: SPAnalysisServices

Account Type: Domain User

Required Permissions:

• Run as account for the "SQL Server 2012 Analysis Services (POWERPIVOT)" windows service on the SQL Server application server.
• Member of local security group: SQLServerMSASUser$<ServerName>$PowerPivot
• Member of local security group: WSS_WPG

Notes:

• Windows service that runs on a SQL Server 2012 application server. In smaller SharePoint installations, this is usually the same server that the SharePoint database engine is installed.
• Built in machine accounts are not allowed
• Must be the same for all Analysis Services server instances used by a single farm

PowerPivot Service Application

Sample Account Name: SPPowerPivot

Account Type: Domain User

Required Permissions:

• Run as account for the PowerPivot Service Application
• Analysis Services system administrator
• Connect, read and write to PowerPivot Service Application Database

Notes:

• Shared web service that runs under an application pool identity in a SharePoint farm
• Built in machine accounts are not allowed
• On smaller farms or farms that don't need the account separation, this is typically the same domain account that is used to run other Service Application on SharePoint 2013.

 

PowerPivot Unattended Data Refresh Account

Sample Account Name: SPDataRefresh

Account Type: Domain User

Required Permissions:

• Must be assigned Contribute permissions on any PowerPivot workbook for which it is used
• Read permissions on any external data sources needed for a data refresh operation

Notes:

• By default, the PowerPivot Configuration Tool configures this as the farm account
• This can be left as the farm account if account separation is not needed and permission requirements outlined above are met. Larger farms or farms with external data sources may want a separate account for auditing and traceability.
• If you don't use the farm account, additional permission configuration may be required on the Central Administration site and the PowerPivot Service Application Database in order for the PowerPivot reports to work in Central Administration.

 

Excel Services Service Application

Sample Account Name: SPExcelServices

Account Type: Domain User

Required Permissions

• Analysis Services PowerPivot Instance system administrator

Notes

• Excel services should be configured in advance of the PowerPivot installation.
• On smaller farms or farms that don't need the account separation, this is typically the same domain account that is used to run other Service Application on SharePoint 2013.

Source Guidance and Additional Documentation

• http://msdn.microsoft.com/en-us/library/76a85cd0-af93-40c9-9adf-9eb0f80b30c1
• http://msdn.microsoft.com/en-us/library/76a85cd0-af93-40c9-9adf-9eb0f80b30c1

If you have any suggestions on how to improve the content of this post or if you just have a question, please comment below.