Site usage report from Database

Question

I have a SP 2007 site where site usage is enabled and I can check the usage for last thirty days. I wanted the usage details for last 6 months for which I went to SharePoint Designer but my SPD is crashing again and again for this site. I updated it to SP3 with no luck. Can anyone please tell me how to get these reports from DB. I know there are views which can get me such data. I tried with

ANLHitsByResourceByDay
ANLHitsByResourceByUserByDay
ANLSiteResourceHits

But I need to joining it for correct data. I want the hit of index.html file located in one specific document library. Any help would be appreciated.

Answer 1

In researching for a similar problem, I've come across two different ways to get usage data from the MOSS 2007 Shared Services Database (db where the ANL* tables are located). They both query the database but in different ways:

• The SharePoint is not enough blog provides a sample query against the 'ANL' tables on how to view the most popular documents in a SharePoint site.
• Karla Ponce's SharePoint with Attitude Getting more than 30 Days of SharePoint Usage Report Data blog post shows how to query the Shared Services Database using the SharePoint object model. From the post: "PortalContext object contains a reference to the Shared Services Database through the property “AnalyticsSqlSession.” She provides an example of how to setup a read-only query against the usage tables.

In my situation, I am working with WSS 3.0 and there is no Shared Service database. So I am getting the needed usage information from the Usage Event Logs.