Use Reporting Service Itself to Monitor Customers' Reporting Activities

   

The list of reports requested in Requirement or Functional Specification by customers is one thing, the actual list of reports used for day-to-day business after Production is another thing. While we know customers have the challenges of changing business needs, how do we understand and measure what are the reports that are actually being used by customers after Production? To some degrees, it is important to understand this fact so that future iteration of developments and/or maintenance can be placed in the right order of priorities.

I found an easy way to do that - if you are using SQL Server 2005 Reporting Service. The idea is to upload the IIS log for the Reporting Service into an IT or operation oriented database (anywhere in the internal network) and then organize the data for a few handy and useful reports through Reporting Service. To do that, there are two tools you should download:

Log Parser 2.2: Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory. You can download the Log Parser 2.2 here.

SQL 2005 Reporting Pack for Internet Information Service: a set of 12 predefined reports and a sample database to easily visualize and author managed reports such as site summary statistics, time period, page views and user statistics, based on information extracted from IIS log files. 
Download it here.

Install them both on your Reporting Server. Create a database for your IT reporting, say name it ITRPT. Use the following scripts to populate the log data into the database:

 

C:\Program Files\Log Parser 2.2>logparser -i:IISW3C -o:SQL "SELECT * INTO ITRPT.IISLog FROM \\myreportingserver\REPORT_LOGS\*.*"  -oConnString:"Driver={SQL Server};Server=Mydatabase;db=ITRPT" -database:ITRPT -createTable:ON

The above scripts will upload the IIS Log file (located in \\myreportingserver\REPORT_LOGS\*.* into a backend database ITRPT in a server Mydatabase. A new table IISLog will be created automatically.

You can now have access to a set of pre-defined reports to manage your Reporting Service site. The reports available including when users visit your Reports, where the users coming from, etc. In addition, by leveraging the IIS Log database table, you can perform some transformation to extract useful information:

SELECT replace(replace(substring(csUriQuery, 10, 300), '%2f', '/'), '+', ' ') as Report, count(*) as total  FROM iislogWHERE csUriQuery like 'ItemPath=%' and csUriQuery not like '%ViewMode=%' and csUriQuery not like '%SelectedSubTabId=%' and csUriQuery not like '%SelectedTabId=%' GROUP BY csUriQuery ORDER BY Total DESC

Basically what the above scripts does, is to check the Report folders in your home pages and then perform calculations on the total visits on the reports. Once you got there, by using Report Designer, you can create some very useful reports. For example, Top 20 Most Active Reports within 30 Days, Most Active Reports by Project, etc.

Comments

• Anonymous
  October 03, 2006
  The comment has been removed

• Anonymous
  October 03, 2006
  Hi J.D., There is an space error in the original post. The parameter should be "-oConnString:" instead  of "-o ConnString:". It must have been inserted during the publishing. I have removed the space and updated the post. Thank you for pointing it out! William.

• Anonymous
  March 15, 2009
  The comment has been removed

• Anonymous
  March 15, 2009
  The comment has been removed