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

Hi,

- Out of Box there is no option for this; however it is possible to pull up Usage Analysis reports past 30 days with SharePoint Designer. 
- The reason it is limited to 30 days is because any query that pulls the data for a period of more than 30 days through SharePoint, can cause a performance hit as it will task the SQL Server while attempting to pull this information. 

Please refer http://msdn.microsoft.com/en-us/library/ms550992.aspx MOSS SDK for the specific tables being used for this process.

Please let us know if this helps, Thanks  

---

Warm Regards, Pratik Vyas | SharePoint Consultant | http://sharepointpratik.blogspot.com/ | Posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

We surely can get the data for more than 30 days from SharePoint designer and I have been getting it for other sites. However, for this particular site, the SPD is crashing for some reason. It shows me the usage summary though. Since the source of Web and SPD is database, I was wondering if we could get this report from DB as well.

Answer 3

You can query the web analytics reporting database.

http://social.technet.microsoft.com/wiki/contents/articles/5117.get-web-analytics-summary-for-all-site-collections-in-sharepoint-2010-en-us.aspx