Attempting to enable or disable a Discovery Accelerator (DA) Case for Analytics fails due to unfinished or non-existent processes.

Article:TECH183565  |  Created: 2012-03-12  |  Updated: 2014-05-05  |  Article URL http://www.symantec.com/docs/TECH183565
Article Type
Technical Solution

Product(s)

Issue



Attempting to enable or disable a Discovery Accelerator (DA) Case for Analytics fails due to unfinished or non-existent processes.

 


Error



Event ID 503:

- APP Analytics - Add Items Task with TransactionId 172 Failed. Exception: Symantec.Accelerator.Analytics.DataAccess.DataAccessException ---> System.Data.SqlClient.SqlException: Invalid object name 'tblIntAnalysedItems_233'.
- APP Analytics - Add Items Task with TransactionId 184 Failed. Exception: Symantec.Accelerator.Analytics.DataAccess.DataAccessException ---> System.Data.SqlClient.SqlException: Invalid object name 'tblIntAnalysedItems_213'.

Event ID 493:

- APP Analytics - Could not start RuleEngineJob. View error log for details. Exception:

Event ID 519:

- APP Analytics - AnalyticsConversationAnalyserTask.exe (PID = 1920) with TransactionId 187 failed to read items to process, terminating. Exception: Symantec.Accelerator.Analytics.DataAccess.DataAccessException ---> System.Data.SqlClient.SqlException: Invalid object name 'tblIntAnalysedItems_233'.
- APP Analytics - AnalyticsConversationAnalyserTask.exe (PID = 3568) with TransactionId 175 failed to read items to process, terminating. Exception: Symantec.Accelerator.Analytics.DataAccess.DataAccessException ---> System.Data.SqlClient.SqlException: Invalid object name 'tblIntAnalysedItems_213'.

 


Environment



- Symantec Enterprise Vault 9.0, any version, on Microsoft Windows Server 2003, any supported version, or 2008 R2, any supported version.
- Symantec Discovery Accelerator 9.0, any version, on Microsoft Windows Server 2003, any supported version, or 2008 R2, any supported version.
- Microsoft SQL Server 2005, any supported version, or 2008, any supported version, on Microsoft Windows Server 2003, any supported version, or 2008 R2, any supported version.

 


Cause



The Discovery Accelerator configuration database contains tables that associate Analytics tasks that need to be performed for DA Cases that have been enabled for Analytics. When the DA databases are moved improperly or some communications break down occurs while Analytics enabled Cases are being deleted, the status of certain active Analytics jobs can become stuck or disappear and will no longer proceed to the next step without manual intervention.

 Also, interactions between Analytics enabled cases in multiple (i.e., 2 or more) DA Customers on separate DA Servers but sharing the same DA Configuration database can cause Analytics processing to become hung.  Such process hanging can cause, but not be limited to, the following results:

  1. Failure to completely disable one or more DA Cases from Analytics.
  2. Failure to completely enable one or more DA Cases for Analytics.

 

Two workarounds exist to allow DA cases to regain proper Analytics functionality.  Workaround 1 requires replacing the DA Configuration database.  Workaround 2 is to manually complete any Analytics disabling or enabling processing.

Workaround 1: Create a new Discovery Accelerator (DA) Configuration database and attach the current DA Customer(s) to this new database with the assistance of Symantec Enterprise Vault Support personnel.  The customized configuration settings within each DA Customer must be copied to the new configuration database in order to keep those settings.

Run the following SQL Queries to obtain initial information needed for resolving this issue.

1. Run the following against the DA Configuration Database, output to file:\

PRINT 'SQL Version information:'
PRINT '(Refer to http://support.microsoft.com/kb/321185)'
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
SELECT @@VERSION
PRINT''
PRINT 'DA SQL Server: ' + @@SERVERNAME
PRINT 'DA Configuration Database: ' + db_name()
PRINT ''
PRINT 'Dump of tblVersion:'
SELECT * FROM tblVersion
PRINT ''
PRINT 'Dump of tblCustomer:'
SELECT * FROM tblCustomer
PRINT ''
PRINT 'Dump of tblCustomerFileCatalogPath:'
SELECT * FROM tblCustomerFileCatalogPath
PRINT ''
PRINT 'Dump of tblAnalyticsServers:'
SELECT * FROM tblAnalyticsServers
PRINT ''
PRINT 'Dump of tblAnalyticsTaskMaster:'
SELECT * FROM tblAnalyticsTaskMaster
PRINT ''
PRINT 'Dump of tblCaseAnalyticsInfo:'
SELECT tcai.*, ts.Name Analytics_State
FROM tblCaseAnalyticsInfo tcai
JOIN tblStatus ts ON tcai.CaseStatus = ts.StatusID
PRINT ''
PRINT 'Dump of tblIntAnalyticsServerTasks:'
SELECT * FROM tblIntAnalyticsServerTasks
PRINT ''
PRINT 'Dump of tblIntAnalyticsTasksQueue:'
SELECT tiatq.*, ts.Name Task_State
FROM tblIntAnalyticsTasksQueue tiatq
JOIN tblStatus ts ON tiatq.CurrentStatus = ts.StatusID
PRINT ''
PRINT 'List of current TransactionIDs:'
SELECT tas.ID Server_ID, tas.ServerName, tas.IPAddress, tas.IsLive, tas.LastUpdated
     , tiatq.TransactionID, tiatq.CaseID, tiatq.StartedAt, tiatq.SuccessItemCount, tiatq.FailedItemCount, tiatq.TaskFailedReason
     , tatm.TaskName
     , ts1.Name Task_Status
FROM tblIntAnalyticsTasksQueue tiatq
JOIN tblIntAnalyticsServerTasks tiast ON tiatq.ServerTaskID = tiast.TaskID
JOIN tblAnalyticsServers tas ON tiast.AnalyticsServerID = tas.Id
JOIN tblAnalyticsTaskMaster tatm ON tiatq.ServerTaskID = tatm.Id
JOIN tblStatus ts1 ON tiatq.CurrentStatus = ts1.StatusID
ORDER BY tiatq.CaseID, ts1.Name

2. Run the following against the DA Customer Database, output to file:

PRINT 'SQL Version information:'
PRINT '(Refer to http://support.microsoft.com/kb/321185)'
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
SELECT @@VERSION
PRINT''
PRINT 'DA SQL Server: ' + @@SERVERNAME
PRINT 'DA Customer Database: ' + db_name()
PRINT ''
PRINT 'Dump of tblCase:'
SELECT tc.CaseID, FolderType, tc.Name Case_Name, tc.StatusID
     , tc.LastAnalyticsStatusID, ts1.Name Last_Analytics_Status, tc.AnalyticsStatusID, ts2.Name Analytics_Status, tc.AnalyticsStatusText
     , tc.MarkedForDeletion, tc.DeletedByPrincipalID
     , tc.LegalHoldState, tc.LegalHoldStatus, tc.LegalHoldGroupID, tc.LegalHoldError
FROM tblCase tc
JOIN tblStatus ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
JOIN tblStatus ts2 ON tc.AnalyticsStatusID = ts2.StatusID
WHERE tc.Type = 101
---AND (AnalyticsStatusText IS NOT NULL OR AnalyticsStatusText <> '')
ORDER BY tc.CaseID
PRINT ''
PRINT 'List of Analytics Tables:'
SELECT name Analytics_Table_Name FROM sys.tables
WHERE name LIKE '%tblContacts%'
OR name LIKE '%tblConversations%'
OR name LIKE '%tblIntAnalysedItems%'
OR name LIKE '%tblIntConversationItems%'
OR name LIKE '%tblIntItemContacts%'
OR name LIKE '%tblIntItemCustomAttributes%'
PRINT ''
PRINT 'List of Analytics RuleEngine Agent Jobs:'
SELECT * FROM msdb.dbo.sysjobs WHERE name LIKE N'%RuleEngine%'

Upon completion of the above information gathering queries, please contact Symantec Enterprise Vault Technical Support for assistance in completing the resolution of this issue.

Workaround 2: Manually complete the Analytics disabling and / or enabling processing for cases that have been selected to disable or enable Analytics.

1. Determine the current Analytics status of all DA Cases in all DA Customers.

a.  Determining the current Analytics status requires looking in the DA Configuration and Customer databases for Analytics related information.

i.   Open a SQL Query pane focused on the DA Configuration database.
ii.  Run the following SQL query against the DA Configuration database using an account with at least db_reader permission:

SELECT tcai.CustomerGUID
     , tc.Name AS 'Customer Name'
     , tcai.CaseID AS 'Customer CaseID'
     , tcai.CaseStatus
     , ts.Name AS 'Status Name' 
FROM tblCaseAnalyticsInfo AS tcai
INNER JOIN tblCustomer AS tc
  ON tcai.CustomerGUID = tc.CustomerGUID
INNER JOIN tblStatus AS ts
  ON tcai.CaseStatus = ts.StatusID
ORDER BY tcai.CustomerGUID
  , tcai.CaseID

iii.  Review the output of the above query as it will list all DA Cases that currently have Analytics enabled or were previously enabled but are now disabled.  Note the CustomerGUID, CaseId, and CaseStatus data as multiple DA Customers can have Cases with the same CaseID (i.e., Customer1 has a Case with CaseID 4 and Customer2 also has a Case with CaseID 4, but these are completely separate cases).


iv.  Possible CaseStatus values (check the tblStatus table in the DA Configuration database for current values) are:

200 - Initialized, denotes the case has been enabled for Analytics and is in the initial steps to begin processing.
201 - Processing, denotes the case is enabled and actively working or awaiting new work.
202 - Paused, denotes the case has had Analytics processing paused.
203 - Closed, denotes the case has been disabled from Analytics.

v.  Run the following SQL query against the DA Configuration database using the same account as in Step 1.a.ii:

SELECT tiatq.ServerTaskID
     , tatm.TaskName AS 'Task Name'
     , tiatq.CustomerGUID
     , tc.Name AS 'Customer Name'
     , tiatq.CaseID AS 'Customer CaseID'
     , tiatq.CurrentStatus
     , ts.Name AS 'Status Name'
     , tiatq.StartedAt AS 'Started Date/Time'
     , tiatq.CompletedAt AS 'Completed Date/Time'
FROM tblIntAnalyticsTasksQueue AS tiatq
INNER JOIN tblAnalyticsTaskMaster AS tatm
  ON tiatq.ServerTaskID = tatm.Id
INNER JOIN tblCustomer AS tc
  ON tiatq.CustomerGUID = tc.CustomerGUID
INNER JOIN tblStatus AS ts
  ON tiatq.CurrentStatus = ts.StatusID
ORDER BY tiatq.CustomerGUID
     , tiatq.CaseID

vi.   Review the output of the above query as it will list all Analytics Tasks associated with all Analytics enabled cases.


vii.  Each Analytics enabled case should have the following Tasks.  Note that cases can have multiple rows with these Tasks if those cases have had Analytics enabled and disabled multiple times.  For such cases, review the latest StartedAt date / time entries.

AddItemsJob - runs when adding items to the listing of items to process.
IngestionTask - runs when ingesting items into the Analytics tables.
RuleEngineJob - runs when applying rules to the items.
ConversationAnalysisTask - runs when analyzing conversation threads.

viii.  Each Analytics Task can have any of the following status entries.

300 - Not Started - denotes the task has not started processing.
301 - Started - denotes the task has started processing.
302 - Completed - denotes the task has completed processing.
303 - Stopped - denotes the task has stopped processing.
304 - Stop - denotes the task is to stop processing.
310 - Failed - denotes the task has failed processing.

ix.  Run the following SQL query against the DA Customer database to obtain the Analytics status of all Cases:

SELECT tc.CaseID
     , tc.Name AS 'Case Name'
     , tc.StatusID AS 'Case StatusID'
     , ts1.Name AS 'Case Status Name'
     , tc.AnalyticsStatusID
     , ts2.Name AS 'Current Analytics Status Name'
     , tc.LastAnalyticsStatusID
     , ts3.Name AS 'Previous Analytics Status Name'
FROM tblCase AS tc
INNER JOIN tblStatus AS ts1
  ON tc.StatusID = ts1.StatusID
INNER JOIN tblStatus AS ts2
  ON tc.AnalyticsStatusID = ts2.StatusID
INNER JOIN tblStatus AS ts3
  ON tc.LastAnalyticsStatusID = ts3.StatusID

x.  Review the output of the above query to note the current Analytics status.  Possible Analytics status entries (found in the tblStatus table of the DA Customer database) are:

850 - Enabling Analytics, denotes the case is in the process of creating the Analytics Tasks entries in the configuration database and the Analytics tables for the case in the customer database.
851 - Analytics Enabled, denotes the case has completed the processing to create the Analytics Task entries and Analytics tables.
852 - Pausing Analytics, denotes the Analytics processing of the case is pausing all actions.
853 - Analytics Paused, denotes the Analytics processing of the case has paused all actions.
854 - Resuming Analytics, denotes the Analytics processing of the case is starting where it left off when paused.
855 - Disabling Analytics, denotes the case is being disabled from Analytics processing.
856 - Analytics Disabled, denotes the case has finished being disabled from Analytics processing.  The Analytics table created for the case may remain in the customer database until after the next backup of the database's transaction log.
857 - Disable Analytics Failed, denotes the processing to disable the case from Analytics has failed.

xi.  Review the DA Customer database for the presence of any Analytics tables.

A. In the SQL Server Management Studio application where the above SQL queries have been run, expand the DA Customer database folder, then the Tables sub-folder.
B. Look through the table names for any tables that end in an underscore and a number (i.e., _4).  The number after the underscore is the CaseID for the case that is or was Analytics enabled.

I. The names of the Analytics tables for each case should be as follows, with # replaced by the CaseID of the case:

tblContacts_#
tblContentHash_#
tblConversations_#
tblHashToAnalysedItem_#
tblHashToAnalysedItem_Attachment_Content_#
tblIntAnalysedItems_#
tblIntConversationItems_#
tblIntItemContacts_#
tblIntItemCustomAttributes_#

xii.   Look in the DA installation folder (default on 64-bit OS is 'C:\Program Files (x86)\Enterprise Vault Business Accelerator) for any file names that end in an underscore and number before a '.sql' extension.  Such files are left over from a case enabling or disabling processing activity.  Specifically, look for a file with the name like 'AcceleratorDatabaseDynamicDropSchemaTemplate_DACustomer1_4.sql', where "DACustomer1" is replaced by the name of the DA Customer and "4" is replaced by the CaseID of the DA Case that was attempting to be disabled from Analytics.  For example, if the DA Customer name is DAGeneralCounsel and CaseID 15 was selected to be disabled from Analytics, the file name would be 'AcceleratorDatabaseDynamicDropSchemaTemplate_DAGeneralCounsel_15.sql'.

Note for Step xii: If any DA Case is (1) marked in the tblCase table with the 'AnalyticsStatusID' of 856 (Analytics Disabled), (2) the 'LastAnalyticsStatusID' of anything other than 856, (3) there are Analytics tables left over in the database, and (4) there is no file in the DA installation folder to drop the schema for that case, then copy the file named "AcceleratorDatabaseDynamicDropSchemaTemplate.sq" and:
1. Rename the file to "AcceleratorDatabaserDynamicDropSchemaTemplate_CustomerName_CaseID.sql", where CustomerName is replaced by the DA Customer name and CaseID is replaced by the DA Case ID number.
2. Edit the renamed file to replace all instances of the Customer Name placeholder.
3. Edit the renamed file to also replace all instances of the CaseID placeholder (about 73 instances).
4. Save the edited file.
5. Use the contents of this file in the steps below where reference is made to the drop schema file.

xiii.  Review all of the above data to determine if any case is missing any Analytics tables or entries in the configuration database's tables referenced above.

xiv.  Run the following SQL query against the DA Configuration database, replacing the entry [CustomerDB] with the name of the DA Customer database:

SELECT tc.CaseID
     , tc.CreateDate AS 'Case Creation Date'
FROM tblCaseAnalyticsInfo AS tcai
RIGHT JOIN [CustomerDB]..tblCAse AS tc
  ON tc.CaseID = tcai.CaseID
WHERE tc.AnalyticsStatusID = 851
  AND tcai.CaseID IS NULL
  AND tcai.Version = ''     -- note the 2 single quotes here

xv.  Run the following SQL query against the DA Customer database:

SELECT tc.Name
     , tc.CaseID
     , COUNT(*)
FROM tblCase AS tc
INNER JOIN (
                        SELECT (RIGHT(so.name, (8-(charindex('_',RIGHT(so.name,8)))))) AS tblAN
                        FROM sys.objects AS so
                        WHERE so.[type] = 'U'
                          AND charindex('_',RIGHT(so.name,9)) > 0) AS tc2
  ON tc2.tblAn=tc.CaseID
GROUP BY tc.Name
     , tc.CaseID HAVING cOUNT(*) < 9

b.  A DA Case has failed Analytics enabling or disabling if any of, but not limited to, the following conditions exist:

i.    Any of the above Analytics tables do not exist - denotes either a failed Analytics enable or disable processing.
ii.   The AnalyticsStatusID is 857 - denotes a failed Analytics disable processing.
iii.  The AnalyticsStatusID and LastAnalyticsStatusID are both 850, 851, 855, or 856 - denotes either a failed Analytics enable or disable processing.
iv.  The AnalyticsStatusID is 850 and the LastAnalyticsStatusID is 851 or 855 - denotes a failed Analytics disable processing.
v.   The AnalyticsStatusID is 855 and the LastAnalyticsStatusID is 856 or 850 - denotes either a failed Analytics enable or disable processing.
vi.  The tblCaseAnalyticsInfo table has no row for a case with the AnalyticsStatusID of 850 or 851 - denotes a failed Analytics enable processing.
vii. The tblIntAnalyticsTasksQueue table has no rows for a DA Case that has a row in the tblCaseAnalyticsInfo table - denotes a failed Analytics enable processing.
viii.  The query from Steps xiv or xv above return any results.

2.  If cases are found to not be fully Analytics enabled (i.e., missing tables or database entries), or fully disabled (i.e., a file exists in the DA installation folder with an underscore and number followed by the '.sql' extension), then the following manual intervention will be required.

a.  For all work to be done in the SQL databases, stop the Enterprise Vault Accelerator Manager Service (EVAMS) on all DA servers referenced in the DA Configuration database's tblCustomer table.  Refer to the IIS column entry in the table for each Customer's DA server name.

b.  For all DA Cases that failed the Analytics enabling processing:

i.    Disable Analytics.
ii.   Stop EVAMS on all DA servers with DA Customers sharing the DA Configuration database.
iii.  Backup the DA Customer database's transaction log.

c.  Ensure no cases have failed the Analytics disabling processing.

i.    If no cases have failed the Analytics disabling processing, proceed to Step e below.
ii.   If any case / cases has / have failed the Analytics disabling processing, clear those cases before attempting to enable any case for Analytics.

d.  For all DA cases that failed the Analytics disabling processing:

i.     Look in the DA installation folder for any file with the underscore and a number followed by the '.sql' extension as in Step 1.a.xii above and, if one exists, then
ii.    Ensure EVAMS is still stopped on all DA Servers sharing the DA Configuration database.
iii.   If the file referenced in Step 2.d.i exists:

A.    Ensure the number after the underscore represents a DA Case that is supposed to be Analytics disabled (i.e., check the AnalyticsStatusID from the SQL query in Step 1.a.ix above for the case to see if it is 855 or 856).
B.    If the DA Case has been disabled for Analytics, copy the contents of the '.sql' file into a SQL Query window focused on the DA Customer database.
C.    Execute the contents of the SQL Query window to manually complete the disabling processing for that CaseID.
D.    If there are other DA Cases that have been disabled for Analytics but still have incorrect status entries or left over tables, edit the contents of the SQL Query window and replace all instances of the previous DA CaseID with the DA CaseID of another failed Analytics disable processing (i.e., do a Find and Replace for all instances of '_#' with '_##', where # is the previous CaseID and ## is the next CaseID).
E.    Execute the contents of the SQL Query window to manually complete the disabling processing for the CaseID.
F.    Repeat Steps 2.d.vi and 2.d.vii for each DA Case that failed the Analytics disabling processing.

iv.   When all of Steps 2.d.iii have been completed, ensure all entries for the Analytics disabled cases have been removed from the DA Configuration database's tblAnalyticsCaseInfo and tblIntAnalyticsTasksQueue tables.  Contact  Symantec Enterprise Vault Technical Support for assistance clearing these tables of any entries for Analytics disabled cases.
v.    When all failed Analytics disabling processing cases have been manually disabled using the above steps, backup the DA Customer database's transaction log.
vi.   Ensure the DA Customer database no longer has any tables with the underscore and the CaseID of the Analytics disabled cases.

e.  After all failed Analytics enable or disable cases have been cleared in Steps b and d above, ensure all DA Customer databases and their transaction logs are backed up before starting EVAMS.

f. After EVAMS has been started, enable Analytics on only 1 or 2 cases per DA Customer and allow them to complete their processing before enabling another 1 or 2 cases per DA Customer.

 


Solution



Symantec Enterprise Vault 11.0.0 Release Details
http://www.symantec.com/docs/DOC7401

This issue has been fixed in Discovery Accelerator (DA) 11.0.0 for new DA installations.  For installations upgraded to DA 11.0.0 where this issue occurs, additional action must be taken as the fix is in a file that is not replaced during the upgrade process.  To implement the fix in and upgraded DA 11.0.0 installation, follow these steps:

  1. Log onto the DA server with an administrative account, such as the Vault Service Account (VSA).
  2. Open in a plain text editor, such as Notepad, the file AnalyticsServerApp.exe.config, located in the DA installation folder (default location is 'C:\Program Files (x86)\Enterprise Vault Business Accelerator').
  3. Locate the section terminator </appSettings>.
  4. Immediately above this terminator line, insert the following lines:
    1.  <add key="AutoPauseAnalyticsCaseWhoseTasksAreLoopingInFailure" value="true"/>
    2. <add key="LookForAnalyticsCasesToAutoPauseDurationSeconds" value ="45"/>
    3. <add key="NumberOfFailedTaskCountDefiningContinuousFailure" value="5"/>
  5. Save and close the file.
  6. Restart the Enterprise Vault Accelerator Manager Service (EVAMS).
  7. Log off of the DA server.

 


Supplemental Materials

SourceETrack
Value2966474
Description

Analytics errors for a specific case stops Analytics processing for all cases if inconsistencies between enabled/disabled cases exist across Config/Customer DB's




Article URL http://www.symantec.com/docs/TECH183565


Terms of use for this information are found in Legal Notices