Email Body Search

If you want to allow you agents to search through the body of emails (either in the ERM management pages or in the Voxtron Client), you’ll have to install and configure the Fulltext Search of SQL Server.

Installing the Full text search

You can use the following command, If you don’t know if the fulltext already is installed or not:

SELECT SERVERPROPERTY (‘IsFullTextInstalled’)

fulltext_installed

 

It will return 1 if the feature is installed, 0 otherwise.

To install the feature, open the SQL Server Installation Center -> “New SQL Server stand-alone installation or add features to an existing installation”.

Make sure to select “Add features to an existing instance of SQL Server XXXX” under “Installation Type. In the “Feature Selection” window, select the “Full-Text and Semantic Extractions for Search” option. Continue the wizard to install.

sqlserver-fulltext

After installation, verify if it was successful with the command above.

Configuring the Full text search

Before configuring the full text search, take a look at the filters (see below).
To configure the full text search run the following commands:

USE Voxtron10_Files

CREATE FULLTEXT CATALOG [Voxtron10_Files_FulltextCatalog] WITH ACCENT_SENSITIVITY=OFF AUTHORIZATION [dbo]

CREATE FULLTEXT INDEX ON dbo.FS10_FilePart (FileData TYPE COLUMN FilePartType Language 1033)
KEY INDEX PK__FS_FilePart ON Voxtron10_Files_FulltextCatalog WITH CHANGE_TRACKING AUTO

index-creation.PN

These commands will create the basic structure for Fulltext Search. If you want more advanced options like stopwordlist, document property search, … then read the Windows documentation on Fulltext Search.

Verify if the index in created either by right clicking the dbl.FS10_FilePart table and then selecting “Full-Text index > Properties”. Click on it and you will see the following popup:

index-properties

Verify the Full-Text Catalog, Database, Table, …

You can select “Repopulate index” to repopulate the index with the current messages.

Filters

(See item 4 in the notes)

The Fulltext Search of SQL Server makes use of filters to determine the data structure of Binary columns (like the mails). You can find a list of filter installed in SQL Server with this command:

EXEC sp_help_fulltext_system_components ‘filter’;

The VCC will store the mails with the “mht” document type, make sure a filter is defined for this type.

fiolers

ERM90 stores mails with the “eml” document type. There is no filter by default for this type. If you updated your ERM90 to ERM 91, these mails will not be indexed for the Fulltext Search.
If you want these mails also used in the search functionality, you’ll have to add a filter for “eml” or change the values of the dbo.FS10_FilePart.FilePartType column from “eml” to “mht”. Afterwards you’ll have to manually rebuild the index.

Adding the filter for “eml can be done by installing Microsoft Office 2010 Filter packe (64bit if you have a 64bit SQL Server) and Filter Pack ServicePacke (KB2460041).
https://www.microsoft.com/en-us/download/details.aspx?id=17062
https://www.microsoft.com/en-us/download/details.aspx?id=26604

After you have installed the Filter Pack you must tell your SQL Server to load the filter:
EXEC sp_fulltext_service @action=’load_os_resources’, @value=1;
EXEC sp_fulltext_service ‘update_languages’;
EXEC sp_fulltext_service ‘restart_all_fdhosts’;

Now you should also have the “eml” filter. Drop the FulltextCatalog and repeat the steps explained above.

Notes

  1. The make the “body” search available, you have to enable the “ERM\Groups\Search\FullTestSearch” at the VCCConfig page.
  2. The searchpattern has the same patterns as CONTAINS:
    https://msdn.microsoft.com/en-gb/library/ms187787%28v=sql.120%29.aspx

    So your searchpattern “*test*” found more items instead of test (without quotes and wildcard) also the quotes should be used.

    Sample:

    SELECT * FROM [dbo].[FS10_FilePart] WHERE Contains(FileData, “test”);
    Will find records with values like “this is a test” and “a test-case is the plan”.

    SELECT * FROM [dbo].[FS10_FilePart] WHERE Contains(FileData, ‘”*test*”‘);
    Will also find records with values like “i am testing this” and “this is the greatest”.

  3. To list all index keywords you can use this query:

    SELECT display_term, column_id, document_count
    FROM sys.dm_fts_index_keywords
    (DB_ID(‘Voxtron10_Files’), OBJECT_ID(‘FS10_FilePart’))

  4. It is not nessecary to change the file extension from .eml to .mht, if you installed the office 2010 filter pack, which is required for SQL 2014.
  5. If you have another language than English as your primary language, use another language ID:
    https://msdn.microsoft.com/en-us/library/ms176076.aspx

  6. Use a stoplist to make your FulltextCatalog better:
    https://technet.microsoft.com/en-us/library/ms142551%28v=sql.110%29.aspx
  7. The FulltextLogs of the SQL Server are located at the LOG folder of the SQL Server. Files have SQLFT as prefix:
    https://technet.microsoft.com/en-us/library/ms142495%28v=sql.105%29.aspx

Leave a Reply