How can I access historical search queries older than 30 days in SharePoint 2007?

Question

When accessing /ssp/admin/_layouts/SpUsageSSPSearchQueries.aspx, I can view the Top Queries Over Previous 30 Days report which shows all search terms that were used at least five times in the past month. How can I view the list of queries for other time periods (such as the 30 days prior to that, or for the past year?)

I believe the data is retained as the graphs on the Search Queries Report page show data for the previous 12 months.

If I have to retrieve the data directly from the SQL database, which table should I be looking for?

Answer 1

Typically, there's be warnings regarding support implications on accessing the SP databases directly but I since SP2007 is out of support scope, I think that's your default position now.

The database tables you'd need are: -

ANLDay ANLHistoricalSiteUsage ANLHistoricalWebUsage ANLHit ANLResource ANLUser ANLWeb

You may also want to edit a stored procedure that sets the 30 day period we're all so familiar with.

Info above, full credit and more indepth answer provided by Julian Stevens in his blog here

http://www.jstevensblog.com/post/Usage-Reports-longer-than-30-days-SharePoint-Log-Parsing.aspx

Answer 2

Upon additional digging within the SQL database, I was able to locate the tables containing the search queries. The relevant SQL tables for the search results are MSSQLogQuerySummary and MSSQLogQueryString.

MSSQLogQueryString contains a list of all historical search queries, along with the most recent date that keyword was used. However, MSSQLogQuerySummary only contains the references to MSSQLogQueryString for the past 30 days, so it is not possible to retroactively determine the count of query usage for prior months (without restoring an older copy of the database from a backup and then manually reporting on that data.)

Query string table:

Query summary table:

It may be possible to change the stored procedures to prevent it from truncating the summary table every 30 days; I did not attempt to do this as I do not have a suitable test environment available at the moment. If more than 30 days was present in the database, it would then be possible to run a report based on the correlation between the two tables.