Issue/Introduction:
This KB article contains fixes for the following issues.
- Multiple SEP policy cubes fail to process.
- Error when processing the SEP Agent Security Events
- Failure to add a SEP database connection to IT Analytics.
The following errors correspond with the issues stated above:
Multiple SEP policy cubes fail to process with the following error:
System error: Invalid procedure call or argument error in the back-end database access module. OLE DB was unable to convert to the data type requested for the 5-column value.
Errors in the OLAP storage engine: An error occurred while processing the 'computer' partition of the 'computer' measure group for the 'Sep Antivirus Policies' cube from the IT Analytics database.
- Error when processing the
Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: Invalid length parameter passed to the LEFT or SUBSTRING function.. Errors in the OLAP storage engine: An error occurred while the 'IPS Detection Event - Name' attribute of the 'EP IPS Detection Event Name' dimension from the 'IT Analytics' database was being processed
Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: Invalid length parameter passed to the LEFT or SUBSTRING function.. Errors in the OLAP storage engine: An error occurred while the 'IPS Detection Event - Name' attribute of the 'EP IPS Detection Event Name' dimension from the 'TRW IT Analytics' database was being processed
- The following error is returned in the event viewer when attempting to add a SEP database connection to IT Analytics.
Exception: Statement(s) could not be prepared. Invalid object name ‘SEM_SVA’. OLE DB provider “SQLNCLI10” for linked server “<linked server name>” returned message “Deferred prepare could not be completed”
Cause:
1. When processing the policy cubes a field in the function is of the wrong type. This field comes from a function used to read data from the SEM5 database. In that function the Last Checkin Date for the client computer is of character type rather than a datetime type.
2. When parsing the 'IPS Detection Event - Name' the script fails if it encounters a custom message that does not contain the expected keyword.
The stored procedure spITAnalytics_SEP_Createviews runs each time you add a SEP database connection to create the various views required to populate the cubes from the SEP databases. It makes a check to verify the SEM_SVA table that is listed in the error message exists. If that table exists then it should create two views that read data from that table. The error message occurs when it attempts to add the two views and fails to find the SEM_SVA table
Resolution:
- The accompanying SQL script spITAnalytics_SEP_CreateViews_Updates_12_1_2 changes all instances of the Last Checkin Date to a datetime type.
- Changes the IPS parser so that it takes everything to the right of the ‘]’ in the event message if found, otherwise just uses the whole string.
- The accompanying SQL script corrected the code that looks for the SEM_SVA table.
To implement the fix:
- Open SQL Server Management Studio
- Connect to your Database Engine
- Open the accompanying file in a SQL Query window.
- Verify that the first line contains the correct name of your Symantec_CMDB database. You may have chosen a different name when you installed the Symantec Installation Manager.
- Click the execute button in the toolbar to execute the script. This modifies the existing spITAnalytics_SEP_CreateViews script.
- Locate the modified script under Symantec_CMDB | Programmability |Stored Procedures.
- Right click and select Execute Stored Procedure.
- Click Okay. The script should execute and return a ‘0’ return code.
You should now be able to process cubes without the above errors.
Applies To
- IT Analytics 7.1 SP2
- IT Analytics for SEP 12.1.2
Note: This issue has been resolved in newer versions of this software and this solution should not be applied to these newer versions.