Export (0) Print
Expand All
Add a Recycle Bin to Windows SharePoint Services for Easy Document Recovery
Use Windows SharePoint Services as a Platform for Building Collaborative Applications
Use Windows SharePoint Services as a Platform for Building Collaborative Apps, Part 2
Expand Minimize

Creating a Web Part to Display Search Data from SharePoint Portal Server 2003

SharePoint 2003
 

Rohit Puri
Rahul Sakdeo
Michael Talley
Christopher Hall
Gabe Bratton
Microsoft Corporation

August 2004

Applies to:
    Microsoft Office SharePoint Portal Server 2003

Summary:   Learn how to collect search-related data from a computer running SharePoint Portal Server and to create a Web Part to view and filter the collected data as data within a date range, as data for a day, and as data for a user. Understand how to load Internet Information Service (IIS) log items log file items into a SQL Server database using a SQL DTS package and stored procedures, and how to create a Web Part to display the data in a SharePoint Portal Server page. (21 printed pages)

Contents

Introduction
Store the Data: Create the Database, Related Stored Procedures, and the DTS Package
Create a Web Part to Present Search Data
A Note About Dates
Conclusion

Introduction

You can create an end-to-end solution to perform the collection and viewing of search term data with the powerful search data collection in Microsoft Office SharePoint Portal Server 2003. SharePoint Portal Server logs the search term data automatically. In this article, we show you how you can consolidate this data from daily log files into a SQL Server database, and make it usable for fast retrieval and analysis. We detail the following high-level steps you need to take to accomplish this task:

  1. Create a SQL database, related stored procedures, and a DTS package to load the log file data into a database.
  2. Create a Web Part to view and filter the search data from the SQL database.

    Be aware that the search results that are logged in the IIS log files and are transferred to the database do not include either the scope information of the query, if applicable, or results from property-only queries, such as queries for documents created by a particular person.

    Note   This article assumes you have a solid understanding of SharePoint Portal Server, .NET Framework technologies such as ASP.NET, and the Microsoft Visual C# programming language. You should use this article with the additional reference material available in the Microsoft SharePoint Products and Technologies 2003 Software Development Kit (SDK).

Store the Data: Create the Database, Related Stored Procedures, and the DTS Package

For central storage and easier querying, we recommend that you load the search term data information from the IIS log files into a SQL Server database. The three steps you take are as follows:

  1. Create the database and tables.
  2. Create the stored procedures.
  3. Create the Data Transformation Services (DTS) Package.

Step 1. Create the Database and Tables

Using Enterprise Manager or a similar tool, create a database on the SQL Server computer to store the IIS log file information, stored procedures, and DTS package.

Create the following two tables in a new SQL database:

  • tblLogItems
  • tblSearchTerms

Each table should have the following structure:

[DateData] [varchar] (255)

Note   For [DateData] in the tblSearchTerms table, this field is of type [datetime] (8).
[TimeData] [varchar] (255) 
[IP] [varchar] (255)
[Type] [varchar] (255)
[Page] [varchar] (255)
[SearchTermData] [varchar] (255)
[Port] [varchar] (255)
[UserData] [varchar] (255)
[IP2] [varchar] (255)
[Browser] [varchar] (255)
[HTTPCode] [varchar] (255)
[Col012] [varchar] (255)
[Col013] [varchar] (255)
[Col014] [varchar] (255)
[Col015] [varchar] (255)

Note   The DateData, TimeData, SearchTermData and UserData fields are used in the Web Part queries. Other information collected in the IIS log file is stored in the tables and can be used by modifying the Web Part queries.

Step 2. Create the Stored Procedures

In this step, create the following eight stored procedures:

  • ReturnFileName
  • DeleteIDA
  • AppendTerms
  • UpdateSearchTermData
  • GetDateData
  • GetUserData
  • GetDatabyDate
  • GetDatabyUser

Stored Procedure #1: ReturnFileName

The following stored procedure, used by the DTS package, returns the previous day's IIS log file name. You can specify a different local path or Universal Naming Convention (UNC) value for the @stPrefix variable. IIS log files are stored in the path <system drive>\WINDOWS\system32\LogFiles\W3SVC1\, but you can copy the files to another location for importing later into the database.

Note   You must alter the variable declaration for @stPrefix to match exactly the length of the string you specify for your local or UNC path.
CREATE PROCEDURE ReturnFileName
AS
   DECLARE @dtRetrieveFile datetime,
   @stPrefix char(25),
   @stSuffix char(4),
   @stTemp char(2),
   @stYear char(2),
   @stMonth char(2),
   @stDay char(2)

   SET @dtRetrieveFile = DateAdd(d, -1, GetDate())
   -- may include UNC or local path in @stPrefix 
   -- change declaration to the number
   -- of characters in @stPrefix
   SET @stPrefix = '\\server_name\share\ex'
   SET @stSuffix = '.log'
   
   -- Get two-digit year
   SET @stYear = Cast(Right(DatePart(yy,@dtRetrieveFile), 2) As char(2))

   -- Get two-digit month
   SET @stTemp = Cast(Datepart(mm,@dtRetrieveFile) As char(2))
   SELECT @stMonth =
   CASE Len(@stTemp)
      WHEN 1 THEN '0' + @stTemp
      ELSE @stTemp
   END

   -- Get two-digit day
   SET @stTemp = Cast(DatePart(dd,@dtRetrieveFile) As char(2))
   SELECT @stDay =
   CASE Len(@stTemp)
      WHEN 1 THEN '0' + @stTemp
      ELSE @stTemp
   END

   SELECT @stPrefix + @stYear + @stMonth + @stDay + @stSuffix
GO

Stored Procedure #2: DeleteIDA

The following stored procedure, used in the DTS package, deletes unrelated entries from the tblLogItems table.

CREATE PROCEDURE DeleteIDA
AS
DELETE tblLogItems WHERE SearchTermData = '/default.ida' Or SearchTermData Like '%root.exe'
GO

Stored Procedure #3: AppendTerms

The following stored procedure, used in the DTS package, appends records containing search terms into the tblSearchTerms table from the tblLogItems table. The DateData field is converted from the IIS log file format (yyyy-mm-dd) to the USA format (mm/dd/yyyy) for display purposes and easier querying in the Web Part code.

CREATE PROCEDURE dbo.AppendTerms
AS INSERT INTO dbo.tblSearchTerms
                      (DateData, TimeData, IP, Page, SearchTermData, 
                      Port, UserData, IP2, Browser, HTTPCode, Col012, 
                      Col013, Type)
SELECT     CAST(DateData AS DateTime), TimeData, IP, Page, SearchTermData, 
Port, UserData, IP2, Browser, HTTPCode, Col012, Col013, Type
FROM         dbo.tblLogItems
WHERE     (Type LIKE 'POST') AND (NOT (UserData LIKE '-')) AND (NOT (SearchTermData LIKE '-'))
GO

Stored Procedure #4: UpdateSearchTermData

The following stored procedure, used in the DTS package, cleans the search terms recently added to the tblSearchTerms table.

CREATE PROCEDURE dbo.UpdateSearchTermData
AS UPDATE    dbo.tblSearchTerms
SET              SearchTermData = REPLACE(SUBSTRING(SearchTermData, 64,
 PATINDEX('%+Search+Server+Name%', SearchTermData) - 64), '+', ' ')
WHERE     (PATINDEX('%+Search+Server+Name%', SearchTermData) > 0)
GO

Stored Procedure #5: GetDateData

The following stored procedure, used by the Web Part, retrieves a list of dates from the tblSearchTerms table.

CREATE PROCEDURE dbo.GetDateData
AS SELECT DISTINCT DateData
FROM         dbo.tblSearchTerms
GROUP BY DateData
GO

Stored Procedure #6: GetUserData

The following stored procedure, used by the Web Part, retrieves a list of distinct users from the tblSearchTerms table.

CREATE PROCEDURE dbo.GetUserData
AS SELECT DISTINCT UserData
FROM         dbo.tblSearchTerms
GROUP BY UserData
GO

Stored Procedure #7: GetDatabyDate

The following stored procedure is used by the Web Part to retrieve the search terms for a particular day or date range from the tblSearchTerms table.

CREATE PROCEDURE dbo.GetDatabyDate(@StartDate DATETIME,
@EndDate DATETIME)
AS SELECT     SearchTermData, DateData, COUNT(SearchTermData) AS strCount
FROM         dbo.tblSearchTerms
WHERE     (DateData >= @StartDate) AND (DateData <= @EndDate)
GROUP BY SearchTermData, DateData
GO

Stored Procedure #8: GetDatabyUser

The following stored procedure is used by the Web Part to retrieve search terms for a particular user from the tblSearchTerms table.

CREATE PROCEDURE dbo.GetDatabyUser
(@UserName VARCHAR(255))
AS SELECT     SearchTermData, DateData, COUNT(SearchTermData) AS strCount
FROM         dbo.tblSearchTerms
WHERE     (UserData = @UserName)
GROUP BY SearchTermData, DateData
GO

Step 3. Create the DTS Package

The Data Transformation Services (DTS) package is created using the DTS designer available within the SQL Server Enterprise Manager. To create a package, right-click the Data Transformation Services folder under the SQL Server instance, and then select New Package.

To create the DTS package, perform these seven procedures:

  • Create the connections.
  • Create the Dynamic Properties Task.
  • Create the Execute SQL Tasks.
  • Create the Workflow between connections.
  • Create the rest of the Workflow.
  • Reset the Dynamic Properties task.
  • Save the DTS package and schedule it to run once daily.

To create the connections

  1. Insert a new Text File (Source) connection.
  2. In the File Name box, browse to or type in the path to an existing IIS log file.
  3. In the Select File Format step of the Text File Properties wizard, click Delimited, and then click Next.
  4. In Specify Column Delimiter, click Other, and then type in a space. You should now see items from the IIS log file in the Preview box.
  5. Click Finish, and then click OK.
  6. Insert a new Microsoft OLE DB Provider for SQL Server connection.
  7. Choose the Server and the Database, based on your environment, and then click OK.

To create the Dynamic Properties Task

  1. Insert a new Dynamic Properties Task.
  2. Name the task ReturnFileName, and then in Dynamic Properties Task Properties, click Add.
  3. In the Dynamic Properties Task: Package Properties dialog box, select the Text File (Source) connection.
  4. Select the DataSource property, and then click Set.
  5. In the Add/Edit Assignment dialog box, for Source, select Query.
  6. For Connection, select Microsoft OLE DB Provider for SQL Server.
  7. In the Query box, type execute ReturnFileName.
  8. Click Refresh, and then confirm that the Preview box contains the result of the stored procedure ReturnFileName.
  9. Click OK, and then click OK again.

To create the Execute SQL Tasks

  1. Insert a new Execute SQL Task.
  2. Name the new task DeleteIDA, then for Connection, select the Microsoft OLE DB Provider for SQL Server.
  3. In the SQL statement box, type execute DeleteIDA.
  4. Click Parse Query to confirm the syntax, click OK to the message, and then click OK again.
  5. Insert a new Execute SQL Task.
  6. Name the new task AppendTerms, then for Connection, select the Microsoft OLE DB Provider for SQL Server.
  7. In the SQL statement box, type execute AppendTerms.
  8. Click Parse Query to confirm the syntax, click OK to the message, and then click OK again.
  9. Insert a new Execute SQL Task.
  10. Name the new task UpdateSearchTermData, then for Connection, choose the Microsoft OLE DB Provider for SQL Server.
  11. In the SQL statement box, type execute UpdateSearchTermData.
  12. Click Parse Query to confirm the syntax, click OK to the message, and then click OK again.

To create the Workflow between connections

  1. Select the Text File (Source) connection.
  2. Press and hold the CTRL key, and then select Microsoft OLE DB Provider for SQL Server.
  3. Right-click Transform Data Task to select it, and then double-click the arrow pointing from the Text File (Source) connection to the Microsoft OLE DB Provider for SQL Server connection.
  4. On the Source tab, name the task Copy Log.
  5. On the Destination tab, select the tblLogItems table.
  6. On the Transformations tab, confirm the arrows are present (if not, map the fields from the text file to the table, in sequential order), and then click OK.

To create the rest of the Workflow

  1. Select the ReturnFileName task.
  2. Press and hold the CTRL key, and then select Text File (Source) connection.
  3. On the Workflow menu, click On Success.
  4. Select the Microsoft OLE DB Provider for SQL Server connection.
  5. Press and hold the CTRL key, and then select the DeleteIDA task.
  6. On the Workflow menu, select On Success.
  7. Select the DeleteIDA task.
  8. Press and hold the CTRL key, and then select the AppendTerms task.
  9. On the Workflow menu, click On Success.
  10. Select the AppendTerms task.
  11. Press and hold the CTRL key, and then select the UpdateSearchTermData task.
  12. On the Workflow menu, click On Success.

Reset the Dynamic Properties task

  1. Right-click the ReturnFileName task, select Properties, and then click Edit.
  2. For the Text File (Source) connection, highlight DataSource, and then click Set.
  3. Click Refresh.
  4. Click OK, and then click OK again.

Save the DTS package and schedule it to run once daily

  1. On the Package menu, click Save As, and then save the DTS package with a descriptive name.
  2. Close the DTS Package designer.
  3. In the Data Transformation Services folder, right-click the DTS package and then select Schedule Package.
  4. In the Edit Recurring Job Schedule dialog box, select Daily, and then set the frequency to occur once daily, at a time when the server is not busy.
    Note   The DTS package imports the previous day's IIS log file.

Create a Web Part to Present Search Data

After you store the required data in the SQL table and the DTS package executes at least once, you can query the data using a Web Part, which filters the data and displays a summary of the searches performed on the SharePoint Portal Server computer. Now we can look at how you can create a user interface that allows you to view the data in three ways:

  • As data within a date range.
  • As data for a day.
  • As data for a user.
    Note    Instead of creating a custom Web Part, you could use the SharePoint Data View Web Part and configure it in Microsoft Office FrontPage 2003 to view the data in the SQL table. For more information about the Data View Web Part, see the Microsoft Office FrontPage 2003 Help.

Create a Basic Web Part

You begin by creating a Web Part to display search data. That process is not addressed in this article, but you can follow the steps in the MSDN article Creating a Basic Web Part. To get a quick start on the process, you can download the Web Part Templates for Visual Studio .NET (WebPartTemplatesforVSNetSample2.exe). When you finish, you add a reference to System.Data.dll.

Modify the Default Code File to Add Presentation Code

You can make basic additions and modifications to the default code for the basic Web Part to allow it to display the data in your tblSearchTerms table by doing the following:

  • Adding namespace directives.
  • Defining class-level variables.
  • Defining child controls.
  • Writing code behind the click events to get the search statistics based on the user selection.
  • Modifying the RenderWebPart method.

Add Namespace Directives

  1. In Visual Studio .NET, open Solution Explorer, and then double-click WebPart1.cs.
  2. Near the top of your code, add the following namespace directives to the Web Part.
    using Microsoft.SharePoint.Utilities;
    using Microsoft.SharePoint.WebPartPages;
    using Microsoft.SharePoint.WebControls;
    using System.Web.UI.HtmlControls; 
    using System.Data;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    
    

Define the Class-level Variables

After you define the class-level variables, you replace Server_Name and Database_Name to reflect your environment. Use the following code:

private string text ="<br>";
const string tblName = "tblSearchTerms";
const  string strConn = "Data Source=Server_Name;Initial
 Catalog=Database_Name;Integrated Security=SSPI;";

HtmlSelect startDate,endDate,selectDay,selectUser;
Label lblDateRange,lblDay,lblUser;
HtmlButton btnDateRange,btnDay,btnUser;
string sTableData="";
HtmlTextArea Result;

const string defaultText = "";

private SPWeb oWeb = null;

Define Child Controls

You use the HTML child controls to provide the user interface. You define four HtmlSelect controls to display three choices for the user to get search statistics data, as follows:

  • Date Range (number of searches done each day).
  • Day Data (what was searched for in that day and how many times).
  • User Data (what the user searched for and how many times the user searched).

You also define an HtmlTextArea control to display the results, and three HtmlButton controls to execute the code for each of the three preceding choices.

Add the following lines of code to CreateChildControls:

oWeb = SPControl.GetContextWeb(Context);

//Result text area
Result = new HtmlTextArea();
Result.Cols = 50;

//Start Date, End Date, Day, User combo boxes
startDate = new HtmlSelect();
endDate = new HtmlSelect();
selectDay = new HtmlSelect();
selectUser = new HtmlSelect();

//Respective labels
lblDateRange = new Label();
lblDateRange.Text = "Date Range Data:";
lblDay = new Label();
lblDay.Text = "Day Data:";
lblUser = new Label ();
lblUser.Text = "User Data:";

//Buttons to execute queries and their click events
btnDateRange =  new HtmlButton();
btnDateRange.InnerText = "Go";
btnDateRange.ServerClick += new EventHandler (btnDateRange_click);
btnDay = new HtmlButton();
btnDay.InnerText = "Go";
btnDay.ServerClick += new EventHandler (btnDay_click);
btnUser =  new HtmlButton();
btnUser.InnerText = "Go";
btnUser.ServerClick += new EventHandler (btnUser_click);

//Populate the Date Range, Day, and user combo boxes with data
//from the table 
SqlConnection objConn = new SqlConnection(strConn);

//Set up stored procedure calls
SqlCommand cmdDate = new SqlCommand("GetDateData", objConn);
SqlCommand cmdUsers = new SqlCommand("GetUserData", objConn);
cmdDate.CommandType = CommandType.StoredProcedure;
cmdUsers.CommandType = CommandType.StoredProcedure;

try
{
   //Open stored procedure for Dates
   SqlDataReader drDate;
   objConn.Open();
   drDate = cmdDate.ExecuteReader();

   //Read dates into the controls
   while (drDate.Read())
   {
      //Convert the time zone of the date to UTC
      //FormatDate converts it back to a local time
      //on the SharePoint Portal Server computer; the following two 
      //lines negate time zone conversions but
      //are necessary to show dates in the local SharePoint calendar
      System.DateTime myDate = oWeb.RegionalSettings.TimeZone.LocalTimeToUTC((System.DateTime)drDate
      .GetValue(0));
      string myFormattedDate = SPUtility.FormatDate(oWeb,myDate,SPDateFormat.DateOnly);
      startDate.Items.Add(SPEncode.HtmlEncode(myFormattedDate));
      endDate.Items.Add(SPEncode.HtmlEncode(myFormattedDate));
      selectDay.Items.Add(SPEncode.HtmlEncode(myFormattedDate));
   }

   //Close reader and connection
   drDate.Close();
   objConn.Close();
}
catch(Exception ex)
{
   Result.InnerText = "An Error has occurred: " + ex.Message;
}

try
{
   //Open stored procedure for Users
   SqlDataReader drUsers;
   objConn.Open();
   drUsers = cmdUsers.ExecuteReader();

   //Read users into the control
   while (drUsers.Read())
   {
      selectUser.Items.Add(drUsers.GetValue(0).ToString());
   }
   //Close reader and connection
   drUsers.Close();
   objConn.Close();
}
catch(Exception ex)
{
   Result.InnerText = "An Error has occurred: " + ex.Message;
}
//Add all the controls to render 
Controls.Add (startDate);
Controls.Add (endDate);
Controls.Add (btnDateRange);
Controls.Add (selectDay);
Controls.Add (btnDay);
Controls.Add (selectUser);
Controls.Add (btnUser);
Controls.Add (Result);
Controls.Add (lblDateRange);
Controls.Add (lblUser);
Controls.Add (lblDay);

Write Code Behind the Click Events of the HtmlButton Controls

In each of the click events, use the SqlConnection to open a connection to the database containing the tblSearchTerms table and run the respective query against it. After you get the data back, pass it to the HtmlTextArea control on your Web Part that displays the result.

Add the following code after the CreateChildControls section:

public void btnDateRange_click(object sender, EventArgs e)
{
   //Use ParseDate to convert from SharePoint calendar date 
   //to System.DateTime
   //Use a time value of "12:00:00" as a placeholder to ensure
   //time conversion does not cross dates
   //Use the Date property of the System.DateTime object; 
   //these dates are used for the stored procedure
   System.DateTime dtStartDate = 
    SPUtility.ParseDate(oWeb,startDate.Value.ToString(),"12:00:00",false).Date;
   System.DateTime dtEndDate =
    SPUtility.ParseDate(oWeb,endDate.Value.ToString(),"12:00:00",false).Date;
   int cntRows = new Int16();

   //Set up connection
   SqlConnection objConn = new SqlConnection(strConn);
   //Set up sp command
   SqlCommand cmdDateFiltered = new SqlCommand("GetDatabyDate", objConn);
   cmdDateFiltered.CommandType = CommandType.StoredProcedure;
   
   //Add parameters to sp command
   cmdDateFiltered.Parameters.Add("@StartDate", SqlDbType.DateTime);
   cmdDateFiltered.Parameters["@StartDate"].Value = dtStartDate;
   cmdDateFiltered.Parameters.Add("@EndDate", SqlDbType.DateTime);
   cmdDateFiltered.Parameters["@EndDate"].Value = dtEndDate;

   try
   {
      SqlDataReader drDateFiltered;
      objConn.Open();
      drDateFiltered = cmdDateFiltered.ExecuteReader();
      while (drDateFiltered.Read())
      {
         //Read data into variables
         string myTerm = drDateFiltered.GetValue(0).ToString();
         //Convert the time zone of the date to UTC
         //FormatDate converts it back to a local time
         //on the SharePoint Portal Server computer
         //The following two lines negate time zone conversions but
         //are necessary to show dates in the local SharePoint calendar
         System.DateTime myDate =
          oWeb.RegionalSettings.TimeZone.LocalTimeToUTC((System.DateTime)drDateFiltered.GetValue(1));
         string myFormattedDate = SPUtility.FormatDate(oWeb,myDate,SPDateFormat.DateOnly);
         sTableData = sTableData + myFormattedDate + "\t" + myTerm + 
          "\t" + myStrCount + "\n";
         cntRows += 1;
      }

      //Close reader and connection
      drDateFiltered.Close();
      objConn.Close();
   }
   catch(Exception ex)
   {
      Result.InnerText  = "An error has occurred: \n" + ex.Message;
   }
   
   //Set row count on HTML control and fill with result
   Result.Rows = cntRows + 2;
   Result.InnerText  = "Date" + "\t" + "Search Term" + "\t" + "Count" 
    + "\n" + "------------------------------------" +"\n" + SPEncode.HtmlEncode(sTableData);
}

public void btnDay_click(object sender, EventArgs e)
{
   //Use ParseDate to convert from SharePoint calendar date 
   //to System.DateTime
   //Use a time value of "12:00:00" as a placeholder to ensure
   //time conversion does not cross dates
   //Use the Date property of the System.DateTime object; 
   //this date is used for the stored procedure
   System.DateTime dtStartDate = 
    SPUtility.ParseDate(oWeb,selectDay.Value.ToString(),"12:00:00",false).Date;
   int cntRows = new Int16();

   //Set up connection
   SqlConnection objConn = new SqlConnection(strConn);
   
   //Set up sp command
   SqlCommand cmdDateFiltered = new SqlCommand("GetDatabyDate", objConn);
   cmdDateFiltered.CommandType = CommandType.StoredProcedure;
   
   //Add parameters to sp command
   cmdDateFiltered.Parameters.Add("@StartDate", SqlDbType.DateTime);
   cmdDateFiltered.Parameters["@StartDate"].Value = dtStartDate;
   cmdDateFiltered.Parameters.Add("@EndDate", SqlDbType.DateTime);
   cmdDateFiltered.Parameters["@EndDate"].Value = dtStartDate;

   try
   {
      SqlDataReader drDateFiltered;
      objConn.Open();
      drDateFiltered = cmdDateFiltered.ExecuteReader();
      while (drDateFiltered.Read())
      {
         //Read data into variables
         string myTerm = drDateFiltered.GetValue(0).ToString();
         //Convert the time zone of the date to UTC
         //FormatDate converts it back to a local time
         //on the SharePoint server. The following two lines
         //negate time zone conversions but are necessary
         //to show dates in the local SharePoint calendar
         System.DateTime myDate =
          oWeb.RegionalSettings.TimeZone.LocalTimeToUTC((System.DateTime)drDateFiltered.GetValue(1));
         string myFormattedDate = SPUtility.FormatDate(oWeb,myDate,SPDateFormat.DateOnly);
         string myStrCount = drDateFiltered.GetValue(2).ToString();
         sTableData = sTableData + myFormattedDate + "\t" + myTerm + 
          "\t" + myStrCount + "\n";
         cntRows += 1;
      }

      //Close reader and connection
      drDateFiltered.Close();
      objConn.Close();
   }
   catch(Exception ex)
   {
      Result.InnerText  = "An error has occurred: \n" + ex.Message;
   }
   
   //Set row count on HTML control and fill with result
   Result.Rows = cntRows + 2;
   Result.InnerText  = "Date" + "\t" + "Search Term" + "\t" + "Count" 
   + "\n" + "------------------------------------" +"\n" + SPEncode.HtmlEncode(sTableData);
}

public void btnUser_click(object sender, EventArgs e)
{
   string strUser = selectUser.Value.ToString();
   int cntRows = new Int16();

   //Set up connection
   SqlConnection objConn = new SqlConnection(strConn);
   
   //Set up sp command
   SqlCommand cmdUserFiltered = new SqlCommand("GetDatabyUser", objConn);
   cmdUserFiltered.CommandType = CommandType.StoredProcedure;
   
   //Add parameters to sp command
   cmdUserFiltered.Parameters.Add("@UserName", SqlDbType.VarChar,255);
   cmdUserFiltered.Parameters["@UserName"].Value = strUser;
   
   try
   {
      SqlDataReader drUserFiltered;
      objConn.Open();
      drUserFiltered = cmdUserFiltered.ExecuteReader();
      while (drUserFiltered.Read())
      {
         //Read data into variables
         string myTerm = drUserFiltered.GetValue(0).ToString();
         //Convert the time zone of the date to UTC
         //FormatDate converts it back to a local time
         //on the SharePoint Portal Server computer. 
         //The following two lines negate time zone conversions but
         //are necessary to show dates in the local SharePoint calendar
         System.DateTime myDate = 
          oWeb.RegionalSettings.TimeZone.LocalTimeToUTC((System.DateTime)drUserFiltered.GetValue(1));
         string myFormattedDate = SPUtility.FormatDate(oWeb,myDate,SPDateFormat.DateOnly);
         string myStrCount = drUserFiltered.GetValue(2).ToString();
         sTableData = sTableData + myFormattedDate + "\t" + myTerm + 
         "\t" + myStrCount + "\n";
         cntRows += 1;
      }

      //Close reader and connection
      drUserFiltered.Close();
      objConn.Close();
   }
   catch(Exception ex)
   {
      Result.InnerText  = "An error has occurred: \n" + ex.Message;
   }
   
   //Set row count on HTML control and fill with result
   Result.Rows = cntRows + 2;
   Result.InnerText  = "Date" + "\t" + "Search Term" + "\t" + "Count" 
   + "\n" + "------------------------------------" +"\n" + 
   SPEncode.HtmlEncode(sTableData);
}

Modify the RenderWebPart Method

Finally, you need to modify code in the RenderWebPart method to render the child controls in the Web Part. Add the following code in RenderWebPart.

lblDateRange.RenderControl(output);
output.Write(text);
startDate.RenderControl(output);
endDate.RenderControl(output);
btnDateRange.RenderControl(output);
output.Write(text);
lblDay.RenderControl(output);
output.Write(text);
selectDay.RenderControl(output);
btnDay.RenderControl(output);
output.Write(text);
lblUser.RenderControl(output);
output.Write(text);
selectUser.RenderControl(output);
btnUser.RenderControl(output);
output.Write(text);
output.Write(text);
Result.RenderControl(output);
output.Write(text);

A Note About Dates

The SQL stored procedures and Web Part code assume that you are aggregating data from one SharePoint Portal Server computer in one time zone. It is possible to modify the stored procedures and Web Part code to include the time information, but the time zone information adjusts based on the SharePoint Portal Server configuration on which the Web Part is running currently, not the time zone information for each individual search that is performed.

The FormatDate and ParseDate methods of the SPUtility class are used when displaying and retrieving date information in the browser, respectively. Date operations between the Web Part code and the SQL Server computer are handled using the .NET System.DateTime object.

The FormatDate method of the SPUtility class adjusts the time zone of the date to the local SharePoint Portal Server computer time zone, and formats the date so it can appear in the local calendar of the SharePoint Portal Server computer. The example code does not account for the time at which the search occurred. Moreover, a time of "12:00:00" is passed to the ParseDate method to ensure that the date retrieved from the Web Part, and that is subsequently used in the stored procedure, is not adjusted across days.

Conclusion

Aggregating Microsoft Office SharePoint Portal Server 2003 search term data in a SQL Server database, then using a custom Web Part to query and display that information is a powerful way to view the data contained within daily IIS log files. SharePoint Portal Server logs the search term data automatically. However, by consolidating this data from daily log files into a SQL Server database, you can make the data available and highly usable for fast retrieval and analysis.

Show:
© 2014 Microsoft