SourceOne Email Management: WebSearch may not return a complete set of search results
Summary: WebSearch may not return a complete set of search results.
Symptoms
During a full text index search, all results may not be committed to the SourceOne Search database. Consequently, search results may be incomplete.
This issue may not impact every search. The occurrence of this issue will depend on the existence of duplicate messages in the Full Text Index and the order in which those results are returned. This issue is more likely to occur on systems where:
- Duplicate items exist in the Full Text Index
- MaxSearchHitsThreads option has been enabled
- In-Place Migration archives exist where data from multiple EmailXtender servers was migrated into a single SourceOne Mapped Folder.
Cause
Resolution
Dell EMC recommends that customers apply one of the following SourceOne Email Management patch for the 7.2 version installed:
7.2 SP6 HF4 (7.2.6.6229)
7.2 SP7 or higher
Downloads are available on the SourceOne Email Management Product page at : http://support.emc.com
Once a version of SourceOne Email Management with the fix is installed all future searches are expected to complete with all results. Any searches performed while running an affected version will need to be executed again to guarantee completeness of results.
In SourceOne Email Management version 7.2.8 it may be possible to identify which index sets have duplicate or "Extra Items" content through Index Validation. These validation results can be used to identify which searches which need to be re-run. Perform the following steps:
First, configure Index Validation scanning using Mode 2 or higher. Index Validation will flag problem index sets with a yellow exclamation mark (!) and the status of the Index will be "Extra Items". If scans have been run on a previous release a re-scan can be executed in the SourceOne Email Management console by right-clicking each YYYYMM folder and selecting "Rescan". After scans have completed, all indexes with a state of "Extra Items" must be rebuilt successfully before any affected SourceOne Web Searches are re-run.
The next steps are to identify any potential SourceOne Web Searches that were previously ran that would have potentially searched across a problem index set.
NOTE: The SQL queries below assume that the name of the SourceOne databases are default. If they are not, the query will need to be modified with the correct name of the SourceOne Database Names.
First, to identify any past SourceOne Web Searches that were previously ran during the time of this issue, execute the following SQL Queries:
1. Run the following query to capture the installation date and upgrade dates of the SourceOne Environment:
use ES1Archive
select EndTime into #TEMP from [DBMaintenanceHistory] where (version >=7 and version <7.27000) and Description = 'SourceOne Archive database update complete'
insert into #TEMP select EndTime from [DBMaintenanceHistory] where Description = 'New SourceOne Native Archive database created' and Version >=7 and Version < 7.27000
GO
2. Run the following query to gather all the information for the SourceOne Web Searches that were ran based on the dates retrieved in Step 1:
use ES1Activity;
with cte as(
select ActivityID as activityid,TaskTypeID,Name as SearchName,search.value('(@searchOperation)[1]','varchar(max)') as operation, search.value('(.)[1]','varchar(max)') as daterange,
xConfig.value('(/SearchJobConfig/QueryString/SearchCriteria/@displayName)[1]','nvarchar(max)') as SearchUser from Activity
cross apply xConfig.nodes('/SearchJobConfig/QueryString/SearchCriteria/ExpressionSet/SimpleAttributeExpression') as ref(search)
where xConfig is not null --and --TaskTypeID = 8
and search.value('(@displayName)[1]','varchar(max)') = 'Date')
select activityid,TaskTypeID,SearchUser,SearchName, 'StartDate' = Replace(CASE
WHEN min(daterange) = max(daterange) THEN 'ALL'
ELSE min(daterange) END,'T',' '), 'EndDate' = Replace(CASE
WHEN min(daterange) = max(daterange) THEN 'ALL'
ELSE max(daterange) END,'T',' ') into #SearchDates from cte
group by activityid,TaskTypeID,SearchUser,SearchName
3. Run the following query to output all the information regarding the searches that are possibly affected. The output will include the basic information about the SourceOne Web Search including:
| SearchName: | The name of the search as it appears in the SourceOne Email Management Console\Operations\Job Management Window. |
| JobID: | The JobID value found in the Job Management windows of the SourceOne Email Management Console. |
| StartTime: | The Date\Time that the search was executed. |
| Search Start\End Date: | The Date criteria used in the search. If no Date field was used in the search, then ALL dates were searched. |
| Executing user: | The user who created and ran the search. |
use ES1Activity
select distinct A.Name as SearchName,J.JobID,A.StartTime,S.StartDate as [Search Start Date],
S.EndDate as [Search End Date], S.SearchUser as [Executing User] from Activity A
join Tasks T on A.ActivityID = T.ActivityID
join Jobs J on T.TaskID = J.TaskID
join #SearchDates S on (S.ActivityID = A.ActivityID)
where A.TaskTypeID =8 and A.StartTime > (select min(EndTime) from #TEMP) and A.StartTime < (select max(EndTime)from #TEMP)
order by A.StartTime
GO
drop table #TEMP
drop table #SearchDates
GO
NOTE: Only proceed with the following steps after Index Validation has been completed.
Next, to identify problematic SourceOne Index sets and their states, execute the following SQL Queries:
1. Run the following query to capture the installation date and upgrade dates of the SourceOne Environment:
use ES1Archive
select EndTime into #TEMP from [DBMaintenanceHistory] where (version >=7 and version <7.27000) and Description = 'SourceOne Archive database update complete'
insert into #TEMP select EndTime from [DBMaintenanceHistory] where Description = 'New SourceOne Native Archive database created' and Version >=7 and Version < 7.27000
GO
2. Execute the following query to capture any SourceOne indexes that were, at any time, marked as "Extra Items":
use ES1Archive
select WorkID,CmdType,cast(CmdParam as XML).value('(/WQ_Parms/WQ_FolderId/node()) [1]', 'bigint') as FolderID,cast(CmdParam as XML).value('(/WQ_Parms/WQ_FldrPath/node()) [1]', 'nvarchar(max)') as [Archive_Index Path],LastModified as ScanDate, SUBSTRING(ResultStr,(CHARINDEX( 'indexflags(', ResultStr, 0)+11),(CHARINDEX( ')', ResultStr, (CHARINDEX( 'indexflags(', ResultStr, 0)+11) )-(CHARINDEX( 'indexflags(', ResultStr, 0)+11))) as ResultStr,'Result' = CAST('Extra Items' as varchar(50))
into #ExtraItems
from WorkQueue
where CmdType =17 and (CHARINDEX( 'indexflags(', ResultStr, 0) > 0 and (CAST(SUBSTRING(ResultStr,(CHARINDEX( 'indexflags(', ResultStr, 0)+11),(CHARINDEX( ')', ResultStr, (CHARINDEX( 'indexflags(', ResultStr, 0)+11) )-(CHARINDEX( 'indexflags(', ResultStr, 0)+11))) as bigint) & CAST( 65536 as bigint) ) > 0)
GO
3. Execute the following query to capture any SourceOne Indexes that had been rebuilt during the time of the issue:
use ES1Archiveselect WorkID,CmdType,cast(replace(replace(CmdParam,'<WQ_Parms><WQ_FldrPath>',''),'</WQ_FldrPath></WQ_Parms>','') as nvarchar(max)) as [Archive_Index Path], min(LastModified) as [Rebuild Date], 'Result' = CASE WHEN CmdType=1 THEN 'Rebuilt' ELSE 'Unknown' END into #Rebuilt from WorkQueue (nolock) where CmdType = 1 and (LastModified >= (select min(EndTime) from #TEMP) and LastModified < (select max(EndTime) from #TEMP))group by WorkID,CmdParam,CmdType order by CmdParam,CmdTypeGOupdate #Rebuilt set [Archive_Index Path] = replace([Archive_Index Path],char(13),'')update #Rebuilt set [Archive_Index Path] = replace([Archive_Index Path],char(10),'')GOset IDENTITY_Insert #ExtraItems ONinsert into #ExtraItems (WorkID,CmdType,[Archive_Index Path],ScanDate,Result)select * from #Rebuilt where NOT EXISTS(select * from #ExtraItems where #ExtraItems.[Archive_Index Path] = #Rebuilt.[Archive_Index Path])GO
4. Execute the following query to capture any SourceOne indexes that were Validation has failed or were missed during the Index Validation process after upgrading to SourceOne 7.2 SP8:
use ES1Archive
select replace(FP.path,'\FPROOT\','') + CASE
WHEN LEN(FT.IndexNum) = 1 THEN ('00' + CAST(FT.IndexNum AS VarChar(20)))
WHEN LEN(FT.IndexNum) = 2 THEN ('0' + CAST(FT.IndexNum AS varchar(20)))
WHEN LEN(FT.IndexNum) = 3 THEN CAST(FT.IndexNum AS varchar(20)) END as [Archive_Index Path] into #IndexList
from FTIndex FT
join FolderPlan FP on FT.FolderNodeID = FP.FolderID
EXCEPT
select replace(FP.path,'\FPROOT\','') + CASE
WHEN LEN(FT.IndexNum) = 1 THEN ('00' + CAST(FT.IndexNum AS VarChar(20)))
WHEN LEN(FT.IndexNum) = 2 THEN ('0' + CAST(FT.IndexNum AS varchar(20)))
WHEN LEN(FT.IndexNum) = 3 THEN CAST(FT.IndexNum AS varchar(20)) END as [Archive_Index Path]
from FTIndex FT
join FolderPlan FP on FT.FolderNodeID = FP.FolderID
where (FT.LastScanDate > (select Max(Date) from DBMaintenanceHistory where Description = 'SourceOne Archive database update complete' and Version > 7.27))
GO
set IDENTITY_Insert #ExtraItems ON
insert into #ExtraItems (WorkID,CmdType,[Archive_Index Path],Result)
select '0','17',[Archive_Index Path],'Result'='Not-Validated' from #IndexList
WHERE NOT EXISTS (select * from #ExtraItems where #ExtraItems.[Archive_Index Path] = #IndexList.[Archive_Index Path])
GO
5. Executing this final query will output all the potentially affected SourceOne indexes. Any searches that ran against indexes were marked as "Extra Items" would need to be re-executed to get the correct results. Any searches that were ran against indexes that were "Rebuilt" could potentially need to be re-executed because a rebuild of an index would have resolved but depends on when the search was ran against the rebuilt index set.
select WorkID,[Archive_Index Path],ScanDate as Date,Result from #ExtraItems where Result !='Validated'
GO
drop table #TEMP
drop table #Rebuilt
drop table #ExtraItems
drop table #IndexList
GO
Additional Information
| https://downloads.dell.com/TranslatedPDF/PT-BR_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/ZH-CN_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/ES_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/DE_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/FR_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/IT_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/JA_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/NL_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/KO_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/RU_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/PT_KB535700.pdf |
| https://downloads.dell.com/TranslatedPDF/SV_KB535700.pdf |