Using IIS logging to monitor SharePoint activity

KB: Using IIS logging to monitor SharePoint activity

SharePoint administrators can configure IIS logging to monitor SharePoint activity. Specifically, you can find out how many SharePoint documents are published and accessed with harmon.ie, versus Internet Explorer. This data can be useful to get the hard facts about harmon.ie’s success in driving SharePoint adoption.

Configuring Web site logging

To configure Web site logging, follow the instructions in How to configure Web site logging in Windows Server 2003.

In the Extended Logging Properties dialog, make sure the following items are selected to be monitored in the IIS logs:

  • Date (date): The date when the activity occurred.
  • User Name (cs-username): The name of the authenticated user who accessed the server. A hyphen represents anonymous users.
  • Method (cs-method): The action the client performed (e.g., a GET method).
  • URI Stem (cs-uri-stem): The resource accessed (e.g., Default.htm).
  • Protocol Status (sc-status): The HTTP status of the action.
  • Bytes Sent (sc-bytes): The number of bytes the server sent.
  • Bytes Received (cs-bytes): The number of bytes the server received.
  • User Agent (cs(User-Agent)): The browser the client used.

Analyzing the Web site log files

IIS log files can be parsed and processed using Microsoft’s Log Parser, a command line tool that allows you to run SQL queries against text-based data. To edit and run queries, it may be easier to use one of the GUI tools that work with the Log Parser.

Documents upload query

To compare the number of documents uploaded to SharePoint from Outlook or Notes, using harmon.ie, with the number of documents uploaded using SharePoint’s Web interface, enter this query:

 
SELECT cs(User-Agent),Count(*)
FROM c:\Logs\*log 
WHERE 	(sc-status='200' OR sc-status='302') 
   AND cs-method='POST' 
   AND (TO_LOWERCASE(cs-uri-stem) LIKE '%upload.aspx%' OR TO_LOWERCASE(cs-uri-stem) 
            LIKE '%author.dll%') 
   AND (TO_LOWERCASE(cs(User-Agent)) LIKE '%msie%' OR TO_LOWERCASE(cs(User-Agent)) 
            LIKE '%firefox%' OR TO_LOWERCASE(cs(User-Agent)) LIKE '%Integrator%') 
   AND TO_INT(cs-bytes)>TO_INT(5000) 
   AND (date > TIMESTAMP ('2009-01-01 00:00:00', 'yyyy-MM-dd hh:mm:ss'))
   AND cs-username<>NULL
GROUP BY cs(User-Agent)

Note: Change the path to the log files in the FROM clause, and the date range in the WHERE clause.

This query can be modified according to your needs. For example, to include user information, add a User column to the GROUP BY statement.

Microsoft Office documents access query

The queries below are used to compare the number of Microsoft Office documents that were opened from Outlook or Notes, using harmon.ie, with the number of documents opened from SharePoint’s Web interface.

The sum of the following two queries is the total number of SharePoint documents that were accessed from Microsoft Office authoring applications (e.g. MS Word, Excel, PowerPoint etc…). This number includes both documents that were opened from SharePoint Integrator and from SharePoint’s Web interface.

 
SELECT cs(User-Agent),Count(*)
FROM c:\Logs\*log 
WHERE sc-status='200'
   AND cs-method='GET' 
   AND TO_LOWERCASE(cs(User-Agent)) LIKE '%Microsoft-WebDAV-MiniRedir%'
   AND (date > TIMESTAMP ('2009-01-01 00:00:00', 'yyyy-MM-dd hh:mm:ss'))
   AND cs-username<>NULL
GROUP BY cs(User-Agent)
 
SELECT cs(User-Agent),Count(*)
FROM c:\Logs\*log 
WHERE sc-status='200'
   AND cs-method='POST' 
   AND TO_LOWERCASE(cs-uri-stem) LIKE '%author.dll%'
   AND TO_LOWERCASE(cs(User-Agent)) LIKE '%MSFrontPage%'
   AND TO_INT(sc-bytes)>TO_INT(5000) 
   AND (date > TIMESTAMP ('2009-01-01 00:00:00', 'yyyy-MM-dd hh:mm:ss'))
   AND cs-username<>NULL
GROUP BY cs(User-Agent)

The following query finds out how many SharePoint documents were opened using harmon.ie. Deduct this number from the number calculated in the queries above to find out how many SharePoint documents were opened from SharePoint’s Web interface.

 
SELECT cs(User-Agent),Count(*)
From c:\Logs\*log 
WHERE 	sc-status='200'
   AND cs-method='HEAD' 
   AND TO_LOWERCASE(cs(User-Agent)) LIKE '%Integrator%'
   AND (date > TIMESTAMP ('2009-01-01 00:00:00', 'yyyy-MM-dd hh:mm:ss'))
   AND cs-username<>NULL
GROUP BY cs(User-Agent)

Note: Change the path to the log files in the FROM clauses, and the date range in the WHERE clauses.

Read also: