One of the clients that I was working with needed some extensibility to the out of the box SharePoint Usage Reports.
The data displayed on the reports didn’t meet their requirements, first, they needed to match the data with the user data in SAP in order to get the reports by Region and other parameters, and second, they needed historical data, and not only the last 30 days of information.
The first requirement was not a problem, and at the beginning we didn’t think the second would be a problem either, but oh we were wrong.
Using the out of the box usage reports provided by SharePoint was of course not even an option, we needed a way to get the data and create our own reports.
I spent some time doing research and this is what I found:
Usage Report data is stored in log files in the IIS and also in the Shared Services database, and not only for the last 30 days, but for all the time since the usage reports were activated, so how is it possible that SharePoint doesn’t have a way to expose this data? Well, that’s how the world works.
Working directly with the SharePoint Databases is not supported by Microsoft, so it seemed that my only option was to create a program to read the IIS files. Have you taken a look at those files? Well, they are quite a mess and I didn’t want to have to do anything with them. So I consulted one of my closest friends: “Reflector”, and once again he gave me the solution I was looking for
I found out that the PortalContext object contains a reference to the Shared Services Database through the property “AnalyticsSqlSession”, using this property you get a connection to the database without having to worry about server name, database name, etc.
Since all the usage data is stored in the Shared Services database this was definitely helpful, but let’s not forget that working directly with the databases is not supported, and although I was not accessing directly the database using SQL server, I was however connecting to it and making some queries (READ ONLY).
We presented our solution to Microsoft, and to our surprise, the solution was accepted because somehow we were using the Object Model to get the data, nice!
Having the solution and Microsoft approval, everything got easier. These are the tables in the database that are related to the usage reports:
As you can see, everything is in there! We can do the queries we want and get the data we are looking for.
Going back to the AnalyticsSqlSession property of the PortalContext object, this is how I got it:
The data displayed on the reports didn’t meet their requirements, first, they needed to match the data with the user data in SAP in order to get the reports by Region and other parameters, and second, they needed historical data, and not only the last 30 days of information.
The first requirement was not a problem, and at the beginning we didn’t think the second would be a problem either, but oh we were wrong.
Using the out of the box usage reports provided by SharePoint was of course not even an option, we needed a way to get the data and create our own reports.
I spent some time doing research and this is what I found:
- Using the Object Model. The SPWeb object contains the method GetUsageData(). This is the method called by the Usage Repor pages in sharePoint. It Returns a table that contains information about the usage of a Web site, based on a time interval. Unfurtunately there are only two options for the time interval, either today or last month. So, no way to get more than 30 days of information using the Object model.
- Web Services. No web service exposes usage report data, so I thought about using owssvr.dll, but again, you can only get the last 30 days of data.
Usage Report data is stored in log files in the IIS and also in the Shared Services database, and not only for the last 30 days, but for all the time since the usage reports were activated, so how is it possible that SharePoint doesn’t have a way to expose this data? Well, that’s how the world works.
Working directly with the SharePoint Databases is not supported by Microsoft, so it seemed that my only option was to create a program to read the IIS files. Have you taken a look at those files? Well, they are quite a mess and I didn’t want to have to do anything with them. So I consulted one of my closest friends: “Reflector”, and once again he gave me the solution I was looking for
I found out that the PortalContext object contains a reference to the Shared Services Database through the property “AnalyticsSqlSession”, using this property you get a connection to the database without having to worry about server name, database name, etc.
Since all the usage data is stored in the Shared Services database this was definitely helpful, but let’s not forget that working directly with the databases is not supported, and although I was not accessing directly the database using SQL server, I was however connecting to it and making some queries (READ ONLY).
We presented our solution to Microsoft, and to our surprise, the solution was accepted because somehow we were using the Object Model to get the data, nice!
Having the solution and Microsoft approval, everything got easier. These are the tables in the database that are related to the usage reports:
Going back to the AnalyticsSqlSession property of the PortalContext object, this is how I got it:
Worked like a charm!
