SHAREPOINTBlogs.com Mirror

Getting Search Crawl Details from the DB

Ok, so I must be on a roll... Here's another glorious script that goes directly to the SharePoint DB's... Don't tell Bill!  As usual, this is not recommended by MS, etc., etc., etc.  This one is to get result sets of your Crawl Details.  It will show each attempt to start/stop/delete a crawl, what it's current status is, when it was requested, started, and finished.  Handy for monitoring your Search crawling with home grown tools ;)

 --Begin Script

/*
CrawlLogDetails.sql
Written by
Josef Nielsen
Nov. 2007

Displays MOSS Crawl Details (Type, status, and times)
Point this script at your Search DB (ie. SharedServices_Search_DB)
*/

BEGIN

-- Create temp tables for System values
CREATE TABLE [#CrawlStatus](
[CrawlStatusName] VARCHAR(35),
[CrawlStatusID] INT
)

CREATE TABLE [#CrawlType](
[CrawlTypeName] VARCHAR(25),
[CrawlTypeID] INT
)

-- Populate Crawl Status System Values
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_ACQUIRED', 1)
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_INSERTSTARTPAGE', 2)
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STARTCHECK', 3 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_START', 4)
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_FORBID',  5)
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_UPDATE_SEED', 6 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_QUERY_DONE', 7 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_DELETEUNVISITEDITEMS', 8 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_PAUSE', 9 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_RESUME', 10)
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_DONE', 11 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_UPDATE_STOP', 12 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_STOP', 13 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_STATUS_RESET',  14)
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_START_DELETE', 15 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_DELETE_CS', 16 )
INSERT INTO [#CrawlStatus] VALUES ('CRAWL_DELETE_SA', 17 )

-- Populate Crawl Type System Values
INSERT INTO [#CrawlType] VALUES ('CRAWLTYPE_FULL', 1 )
INSERT INTO [#CrawlType] VALUES ('CRAWLTYPE_INCREMENTAL', 2 )
INSERT INTO [#CrawlType] VALUES ('CRAWLTYPE_DELETE', 6 )

-- Join MSCrawlHistory to SCrawlHostList and our two temp tables
SELECT    [CrawlID]
        ,[HostName]
        ,[CrawlTypeName]
        ,[CrawlStatusName]
        ,[RequestTime]
        ,[StartTime]
        ,[EndTime]
  FROM [SharedServices1_Search_DB].[dbo].[MSSCrawlHistory]
  LEFT JOIN [dbo].[MSSCrawlHostList] ON [ProjectID] = [HostID]
  LEFT JOIN [#CrawlStatus] ON [Status] = [CrawlStatusID]
  LEFT JOIN [#CrawlType] ON [CrawlType] = [CrawlTypeID]
  WHERE 1 = 1

  -- Uncomment and use this conditional to filter the results to just one Web App
  --AND [HostName] = 'MySharePointSiteName'
  ORDER BY [RequestTime] DESC

END

-- Do a little clean up and get rid of those pesky temp tables
DROP TABLE [#CrawlStatus]
DROP TABLE [#CrawlType]

 --End Script

Read the complete post at http://www.sharepointblogs.com/echef/archive/2007/11/13/getting-search-crawl-details-from-the-db.aspx