Use IIS logging to monitor SharePoint activity

harmon.ie Knowledge Base - All Editions

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. This data can be useful to get information about harmon.ie’s success in driving SharePoint adoption.

Configuring Web site logging

To configure Web site logging, follow the instructions in How to enable logging in Internet Information Services (IIS).

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.
  • Username (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 find out the number of documents uploaded using harmon.ie, enter the following query (This query can be modified according to your needs. For example, to include user information, add a User column to the GROUP BY statement.):

1
2
3
4
5
6
7
8
9
10
SELECT cs(User-Agent),Count(*)
FROM c:\Logs\*log
WHERE   (sc-status='200' OR sc-status='302')
   AND cs-method='PUT'
   AND (TO_LOWERCASE(cs(User-Agent)) LIKE '%Integrator%' OR TO_LOWERCASE(cs(User-Agent)) LIKE '%harmon.ie%')
   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.

The following query finds out how many SharePoint documents were opened using harmon.ie.

1
2
3
4
5
6
7
8
9
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%' OR TO_LOWERCASE(cs(User-Agent)) LIKE '%harmon.ie%')
   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.

The follow query displays the number of harmon.ie users, that may be referred for license and usage tracking purposes.

1
2
3
4
5
6
7
SELECT cs-username  
FROM *.log 
WHERE ((TO_LOWERCASE(cs(User-Agent)) LIKE '%%harmon%%') 
    OR TO_LOWERCASE(cs(User-Agent)) LIKE '%%integrator%%') 
    AND cs-username<>NULL 
GROUP BY cs-username 
ORDER BY cs-username