SQL Query for Full-Text Search Statuses

[Ryan_Wakefield]

Hello,

So we are running millions of documents through out FTS servers right now and one of the things that I wanted to do was to recreate the FTS Status window in a Reporting dashboard. The problem is that I think that you have to bitwise the statuses to get the proper value of the status, but I can't figure out the bitwise value to apply to get the proper statuses. Does anyone have the query that could give me this?

Thanks.

[Mike_Marvin]

Ryan,

I don't have a query for it on hand at the moment but these are the values and the status they correspond to:

UpToDate        = 0,ContentAdded    = 1,UpdatedMetaData = 2,   ReIndexed       = 4,      UpdatedContent  = 8,PurgeOrDelete   = 32,WasUpdated = 0x01000000,Backfile = 0x10000000,     Error       = 0x20000000,        InProgress      = 0x40000000

Hope that helps!

Mike Marvin

[Ryan_Wakefield]

Thank @Mike Marvin . So I was able to build a query, but I know it isn't 100% complete as I still am missing Backfile Pending and Backfile In-Progress. As soon as I can get those, I can start to build the final query that would build a table similar to the status window and thus allow a dashboard to be up and it to refresh every X [time frame] or just be static and refresh ad hoc.

Here is the query I built. It isn't complete, but it is close.

--------------------------------------------------------------------------------------------------------

Set transaction isolation level read uncommitted
select statuses.Text_Status,format(SUM(statuses.Count),'N0') AS 'Totals' FROM(
Select --status,
CASE
WHEN (status = 0) THEN 'Complete'
WHEN ((status = status|16777216) and (status<536870912)) THEN 'Day Forward Pending'
WHEN ((status = status|536870912) and (status<805306368)) THEN 'Day Forward Failed'
WHEN (status = status|805306368) THEN 'Backfile Failed'
WHEN (status = status|1073741824) THEN 'Day Forward In Progress'
END AS 'Text_Status',
COUNT(status) AS 'Count'
FROM hsi.hscatalogdocs
Group BY status) statuses
group by statuses.Text_Status

--------------------------------------------------------------------------------------------------------

Thanks.