Sharepoint 2010 – access site usage statistics

OK, so you made the assumption that Microsofts Sharepoint team would have provided a simple way to access something as common (and not to mention extremely useful!) as site usage statistics. Which pages and resources are accessed

• at what time
• for how long
• how often
• the browser type
• http status
• and last but definately not least: by who.

And not only did you assume this would be easily accessible, you also told a client you’d have it done in a couple of hours. I assume some of you that ended up here either got lucky straight away, or you have already spent way more time than expected and added yet another element to your list of reasons why Sharepoint will make you loose your hair prematurely.

If you assumed that this data should be available somewhere you were right. The trick is to know how to access it from your custom web part. The data is stored in the WSS_Logging database, in a table called RequestUsage. You can run a simple query just to see what’s in there and which fields you have access to:

SELECT *
FROM [RequestUsage] 

Then you can start getting a bit more creative. You see, the RequestUsage table contains statistics for all sorts of requests – like requests to the MetadataWebService. So depending on your needs, filter out the type of document types / extensions / paths you are interested in. I needed just to know the hit count of .aspx pages in a specific sub site, so my query looked somewhat like this:

SELECT distinct(DocumentPath), COUNT(*) as DocCount
    FROM [RequestUsage]
    WHERE DocumentPath LIKE '/Marketing%'
      AND ( DocumentPath LIKE '%.aspx'   )
GROUP BY DocumentPath      

This is not a lesson in SQL, but that query gives me a nice list of all .aspx documents accessed under the Marketing sub site with the corresponding hit count. To get the top 5 out of that result, you’ll end up with something like this:

with cte as (
    SELECT distinct(DocumentPath), COUNT(*) as DocCount
    FROM [RequestUsage]
    WHERE DocumentPath LIKE '/Marketing%'
      AND ( DocumentPath LIKE '%.aspx'   )
    GROUP BY DocumentPath )
select top 5 DocumentPath, DocCount from cte order by DocCount desc

So we have a query we would like to run, and then you can display the result in a web part. There are numerous ways to do that, and I won’t cover it here but I’ll show the C# code for running this query. The following code snippet is put together from my actual project, and should give you enough information to piece together a working method. Take note of how the connection string for the WSS_Logging database is retrieved:

// run with elevated privileges
SPSecurity.RunWithElevatedPrivileges(delegate()
{
    string connectionString = SPUsageApplication.DefaultApplication.UsageDatabase.DatabaseConnectionString;
    SqlConnection connection = new SqlConnection(connectionString);

    // build your query
    StringBuilder sb = new StringBuilder();
    sb.Append("with cte ");
    sb.Append("as ");
    sb.Append("( ");
    sb.Append("SELECT distinct(DocumentPath), COUNT(*) as DocCount ");
    sb.Append("FROM [RequestUsage] ");

    // ...
    // add all the WHERE filters you want here
    // ...

    sb.Append(" GROUP BY DocumentPath ");
    sb.Append("");
    sb.Append(") select top " + ItemCount + " DocumentPath, DocCount from cte order by DocCount desc");
    string sql = sb.ToString();

    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = string.Format(sql, SPContext.Current.Web.ServerRelativeUrl);
    command.CommandType = System.Data.CommandType.Text;
    command.Connection.Open();

    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        // access the fields, for example document name
        string docName = reader["DocumentPath"].ToString().Substring(reader["DocumentPath"].ToString().LastIndexOf('/') + 1);
        docName = HttpUtility.UrlDecode(docName);

        // build an HTML table, add items to a list or whatever you prefer
        // ..
    }

    // close the db connection
    command.Connection.Close();

}

The magic lies in how you build and define the filter to suit your requirements.

Sharepoint 2010 – display custom properties on Profile page

Tip #1:
Changing the order of properties displayed in the user profile page of MySite is a PITA if doing it through Central Admin (User Profile Service Application –> Manage User Properties). To move the item higher or lower you’ll trigger a postback for each and every single step, which takes a ridiculous amount of time. So download this tool from Codeplex and save yourself a bucketload of time.

Tip #2:
There are a few user profile properties that are shown by default, while the rest are visibly only by clicking the “More information” link. Changing the order of these is either simple (see tip #1) or time consuming and horrendous (use Central Admin). The properties visible by default (typically office and contact information) are not affected by the order you specify in Central Admin so how do you change them? And how do you add more properties? Open up the web application you use for MySite (usually something original like http://mysite) in Sharepoint Designer. Find Profile.aspx under “All files” and “Edit File in Advanced Mode”. Now you can use WYSIWYG editor capabilities to shape and alter the layout, and adding a new property only requires a new div tag with a reference to the property name (get the correct property name from “User Profile Properties” in Central Administration. Edit the details for the property you want to display, and get the “Name” property, e.g. “Pager” in this example):

<div style="padding-left: 20px;" class="ms-contactcardtext3" id="ProfileViewer_ValuePager">
Pager number: <SPSWC:ProfilePropertyValue PropertyName="Pager" dir="ltr" runat="server"/>
</div>

Sharepoint 2010 – how to show error details when debugging

Showing actual error details with the stacktrace is usually a quick change in web.config for any ASP.NET web application. For Sharepoint 2010 a few more steps are required, as there are several web.config files spread around.

In my case, the web.config files to change were:

C:\inetpub\wwwroot\wss\VirtualDirectories\[your_web_app]
Set <customErrors mode=”On”> to Off
Set <SafeMode .. CallStack=”False”> to True
Set <Compilation batch=”False” to True and <Compilation Debug=”False” to True

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS
Set <customErrors mode=”On”> to Off
Ensure <compilation batch=”   ”> is Off

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG
Set <customErrors mode=”On”> to Off

Sharepoint 2010 – disable client side validation while in edit mode

If your web part uses client side validation you would have experienced how annoying this is when opening the page in Edit Mode. I knew there had to be a way to turn this off, but it wasn't until today I actually spent a few minutes figuring it out. The solution is to add an IF statement in the Page_PreRender() method to check the current page mode, and then disable the client side script for the validation controls:

protected void Page_PreRender(object sender, EventArgs e)
{
    // determine if page is in Edit mode
    WebPartManager wp = WebPartManager.GetCurrentWebPartManager(Page);
    if (wp.DisplayMode == WebPartManager.EditDisplayMode)
    {
        // disable all client side validation
        RequiredFieldValidator1.EnableClientScript = false;
        RequiredFieldValidator2.EnableClientScript = false;
        ValidationSummary1.EnableClientScript = false;

    }
}

Sharepoint 2010 – webpart zone padding / spacing

To add some padding between the webparts within a webpart zone either add this in your custom .css:

.ms-PartSpacingVertical
{
    margin-bottom: 0px;
}

.ms-PartSpacingHorizontal
{
   margin-right: 20px;
}

Or, if you just want it on a specific page while the rest of your site is unaffected, use the semi-dirty method of adding a Content Editor Webpart with a <style> element (edit the HTML source):

<style type="text/css">

.ms-PartSpacingVertical
{
    margin-top: 20px;
}

.ms-PartSpacingHorizontal
{
   margin-right: 20px;
}

</style>