Introduction
I'm currently working for a cable company that wants to set up a public SharePoint site that will allow customers to view content that will help them set up their home systems (network, XBox, etc.). One great idea that a co-worker came up with is to have a Web Part displayed on the default page to show the most viewed content by our customers. This will allow our customers to have easy access to solutions for the most frequent problems that other customers have.
Background
If you know SharePoint well enough, then you probably already know about Usage Reports. The first thing you are going to need to do is setup Usage Reports for your site collection. A great walkthrough that will help you do this is available here. If you have used them, then you know they are very limited in what they can do. So, I dug down (reflected SharePoint DLLs) and found that the Usage Analysis Job that runs by default every night populates the ANL<suffix> tables that are located in your SharedServicesDB.
- ANLResource - A row represents any given content item in SharePoint whether it be a PDF, ASPX page, etc. There will be no duplicates in this table.
- ANLHit - A row represents a single hit to a specific resource made by a user.
The ANLResource table is great because it contains two very important fields, the WebGuid and SiteGuid. The will allow us to scope the query to the site that the user is currently browsing to. Also, another important field is the ANLHit.UserId because it allows us to scope our query to a specific user. Then, we'll be able to show only content that the user has clicked. Great!! Right?
Don't forget that the Usage Report runs nightly. So, keep in mind that even if you click a link a thousand times, no changes will be reflected in the Web Part until the job has run.
Installation
Hopefully, most of you already know how to install Web Parts, but if you need help, follow these steps:
- GAC both Mullivan.Shared.dll and Mullivan.SharePoint.WebParts.dll.
- Register Mullivan.SharePoint.WebParts.dll in the SharePoint Web Config.
Go to C:\inetpub\wwwroot\wss\VirtualDirectories\<Port Number>\web.config. Add the following in between the SafeControls node:
<SafeControl
Assembly="Mullivan.SharePoint.WebParts, Version=1.0.0.0,
Culture=neutral, PublicKeyToken=c37a514ec27d3057"
Namespace="Mullivan.SharePoint.WebParts" TypeName="*" Safe="True" />
- Go to Site Settings -> Webparts -> click New on the menu.
Scroll to the bottom and check Mullivan.SharePoint.WebParts.MostViewedWebPart, and then scroll back to the top and click Populate Gallery.
Done! You should see it in your Web Parts collection.
Configuration
Shown above is the property pane for this Web Part. I'll explain each field below:
- User Only - If checked, it scopes the query to only display content that the current user has clicked.
- Scope - Sets the query to either pull content from all the sites or just the site that the Web Part is located in.
- Return Count - The max items that should be returned from the query.
- Span - The amount of days that the query should go back from now and determine the hit count from.
- Extensions - A comma delimited list of file extensions that limits the query to specific file types. So, if you want to display only PDF and Word docs, then you would use "pdf, doc, docx".
The Code
Are you ready?? I'm a bit mischievous and spend a lot of time reflecting DLLs. I'm not one who likes to spend time reading words in a book when I can just get right to it and see what the heck everything is doing. :P
So, I thought long and hard (in my world, that's 5 minutes) about how I'm going to create a connection to the Shared Services database. Well, using Reflection, I found that the TopPages control located on SPUsageSite.aspx uses an internal property called "AnalyticsSqlSession" on the PortalContext object. So, I just decided to use Reflection to dig that out and gain access to it. You may be thinking that's crazy, but it kept me from having to store some kind of connection string to that database.
private SqlDataReader GetSqlReader(SqlCommand cmd)
{
PortalContext pContext = PortalApplication.GetContext();
Type tContext = pContext.GetType();
PropertyInfo pSqlSession = tContext.GetProperty("AnalyticsSqlSession",
BindingFlags.NonPublic | BindingFlags.Instance);
object sqlSession = pSqlSession.GetValue(pContext, null);
Type tSqlSession = sqlSession.GetType();
MethodInfo mExecute = tSqlSession.GetMethod("ExecuteReader",
new Type[1] { typeof(SqlCommand) });
return mExecute.Invoke(sqlSession, new object[1] { cmd }) as SqlDataReader;
}
So now, we are going to want to generate our query string. We want to make sure that values that may frequently change might be built into our query as SQL parameters. So, we are going to use the start date, user's name, and website GUID as SQL parameters. The TOP and file extensions are not going to be changed, so the query should cache and be reused in the SQL Server database.
private string GetQueryText(bool isUserQuery, int returnCount,
MostViewedScope scope, string[] extensions)
{
string query = @"
SELECT TOP {0} ANLResource.ResourceId AS ResourceId,
ANLResource.DocName AS DocName,
ANLResource.FullUrl AS FullUrl,
ANLResource.WebGuid AS WebGuid,
COUNT_BIG(*) AS HitCount
FROM ANLHit
INNER JOIN ANLResource
ON ANLHit.ResourceId = ANLResource.ResourceId";
if (isUserQuery)
{
query += @"
INNER JOIN ANLUser
ON ANLHit.UserId = ANLUser.UserId";
}
query += @"
INNER JOIN ANLDay
ON ANLHit.DayId = ANLDay.DayId
WHERE
ANLDay.FullDate > @StartDate";
if (scope == MostViewedScope.CurrentSite)
query += "AND ANLResource.WebGuid = @WebGuid";
else
query += "AND ANLResource.SiteGuid = @SiteGuid";
if (isUserQuery)
{
query += @"AND ANLUser.UserName = @UserName";
}
if (extensions != null && extensions.Length > 0)
{
query += @"AND (";
for (int i = 0; i < extensions.Length; i++)
{
if (i != 0)
query += " OR ";
query += string.Format("(CHARINDEX('.{0}', ANLResource.DocName) > 0)",
extensions[i].Trim());
}
query += ") ";
}
query += @"
GROUP BY ANLResource.ResourceId,
ANLResource.DocName,
ANLResource.FullUrl,
ANLResource.WebGuid
ORDER BY HitCount DESC";
return string.Format(query, returnCount); ;
}
I know that looks a little messy, but that's how it needs to be done. The following is an example of the query after it is generated:
SELECT TOP 10 ANLResource.ResourceId AS ResourceId,
ANLResource.DocName AS DocName,
ANLResource.FullUrl AS FullUrl,
ANLResource.WebGuid AS WebGuid,
COUNT_BIG(*) AS HitCount
FROM ANLHit
INNER JOIN ANLResource
ON ANLHit.ResourceId = ANLResource.ResourceId
INNER JOIN ANLUser
ON ANLHit.UserId = ANLUser.UserId
INNER JOIN ANLDay
ON ANLHit.DayId = ANLDay.DayId
WHERE
ANLDay.FullDate > @StartDate
AND ANLResource.WebGuid = @WebGuid
AND ANLUser.UserName = @UserName
AND ((CHARINDEX('.aspx', ANLResource.DocName) > 0)
OR (CHARINDEX('.pdf', ANLResource.DocName) > 0)
OR (CHARINDEX('.docx', ANLResource.DocName) > 0)
OR (CHARINDEX('.doc', ANLResource.DocName) > 0))
GROUP BY ANLResource.ResourceId,
ANLResource.DocName,
ANLResource.FullUrl,
ANLResource.WebGuid
ORDER BY HitCount DESC
OK.. So now, let's execute our query by calling the method GetReportData().
private DataTable GetReportData()
{
string currentUser = null;
if (this.IsUserQuery
&& this.Page.User != null
&& this.Page.User.Identity != null)
currentUser = this.Page.User.Identity.Name;
DataTable table = null;
using (SqlCommand command = new SqlCommand())
{
SqlDataReader reader = null;
command.CommandText = GetQueryText(!string.IsNullOrEmpty(currentUser),
this.ReturnCount, this.Scope, this.Extensions);
command.CommandType = CommandType.Text;
if (!string.IsNullOrEmpty(currentUser))
{
SqlParameter spUserName =
new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
spUserName.Value = currentUser;
command.Parameters.Add(spUserName);
}
SqlParameter spScope = null;
if (this.Scope == MostViewedScope.CurrentSite)
{
spScope = new SqlParameter("@WebGuid", SqlDbType.UniqueIdentifier);
spScope.Value = SPControl.GetContextWeb(this.Context).ID;
}
else
{
spScope = new SqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
spScope.Value = SPControl.GetContextSite(this.Context).ID;
}
command.Parameters.Add(spScope);
SqlParameter spStartDate = new SqlParameter("@StartDate", SqlDbType.DateTime);
spStartDate.Value = DateTime.Today.Subtract(
TimeSpan.FromDays(Convert.ToDouble(this.SpanDays)));
command.Parameters.Add(spStartDate);
table = new DataTable();
table.Locale = CultureInfo.InvariantCulture;
reader = GetSqlReader(command);
try
{
table.Load(reader);
return table;
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
}
Alright, so now, let's render our data inside of our part.
protected override void OnLoad(EventArgs e)
{
try
{
_data = GetReportData();
}
catch (Exception ex)
{
_errorMessage = ex.ToString();
}
base.OnLoad(e);
}
protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
if (!string.IsNullOrEmpty(_errorMessage))
{
writer.Write(HttpUtility.HtmlEncode(_errorMessage).Replace("\n", "<br/>"));
return;
}
StringBuilder sb = new StringBuilder();
#region "Html"
string htmlItem = @"
<tr>
<td class=""ms-propertysheet"" style=""padding-left:1px"">
<table cellspacing=""0"" cellpadding=""0"" width=""100%"" border=""0"">
<tr>
<td valign=""top"" nowrap=""nowrap""
class=""ms-descriptiontext""
width=8px style=""padding-top:5px;"">
<IMG src=""{2}"" width=16px height=16px alt='' >
</td>
<td valign=top class=""ms-descriptiontext""
style=""padding-top:7px;padding-left: 3px;"">
<a href=""{0}"" title=""{3}"">{1}</a>
</td>
</tr>
</table>
</td>
</tr>
";
string htmlTable = @"
<table width=100% cellpadding=0 cellspacing=0 border=0>
<tr>
<td nowrap class=""ms-linksectionheader""
style=""padding: 4px;"" width=""100%"">
<H3 class=""ms-standardheader"">
{0}
</H3>
</td>
</tr>
<tr>
<td height=""1px"">
<IMG SRC=""/_layouts/images/blank.gif"" width=1 height=1 alt="""">
</td>
</tr>
<tr>
<td width=""100%"" style=""padding: 0px 4px 4px 4px;"" colspan=""2"">
<table cellpadding=""0"" cellspacing=""0"" border=""0"">
<tr>
<td valign=""top"" class=""ms-descriptiontext""
style=""padding-top:5px"">
</td>
</tr>
{1}
</table>
</td>
</tr>
<tr>
<td height=""15px"">
<IMG SRC=""/_layouts/images/blank.gif""
width=1 height=15 alt="""">
</td>
</tr>
</table>
";
#endregion "Html"
foreach (DataRow dr in _data.Rows)
{
string docName = dr["DocName"].ToString();
string fullUrl = dr["FullUrl"].ToString();
long hitCount = (long)dr["HitCount"];
string desc = string.Format("This file has been requested {0} " +
"times in the past {1} days.", hitCount, this.SpanDays);
Guid webGuid = (Guid)dr["WebGuid"];
string extUrl = "/_layouts/images/icgen.gif";
try
{
SPSite site = SPControl.GetContextSite(this.Context);
using (SPWeb web = site.AllWebs[webGuid])
{
string relativeUrl = fullUrl;
int idx = relativeUrl.IndexOf(web.ServerRelativeUrl,
StringComparison.InvariantCultureIgnoreCase);
if (idx > -1)
relativeUrl =
relativeUrl.Substring(idx, relativeUrl.Length - idx);
SPListItem spListItem = web.GetListItem(relativeUrl);
docName = spListItem.Title;
object icon = spListItem["DocIcon"];
if (icon != null)
extUrl = string.Format("/_layouts/images/ic{0}.gif",
Convert.ToString(icon));
}
}
catch
{
continue;
}
sb.Append(string.Format(htmlItem, fullUrl, docName, extUrl, desc));
}
writer.Write(string.Format(htmlTable, this.Title, sb.ToString()));
}
OK.. That's pretty much it. There is an Editor attached to this Web Part for configuration, but I'm not going to get into that. There are many blogs out there that will help understand how that works.
Points of Interest
There are two other Web Parts included in the project. The weather Web Part was taken from a blog made by the Mossman located here. The other is a Web Part that I blogged about yesterday. It's a really great Web Part that displays a navigation bar, check it out here.
Conclusion
Let me know what you think. If you have any ideas, then I'll be sure to take a look and see if I can provide an update. I hope you found this Web Part useful.
History
- 15th January, 2009: Initial post
- 19th March, 2009: Updated source code
- 24th March, 2009: Updated source code
