QualysGuard import job data type conversion failure

Products

• Information Centric Analytics
• Data Loss Prevention Core Package

Issue/Introduction

After upgrading to Information Centric Analytics (ICA) version 6.5.4, QualysGuard import jobs fail and return the following errors:

[1:ERROR] VulnerabilityKnowledgeBase.Stage() Encountered error.  Aborting Qualys data import...

[1:ERROR] VulnerabilityKnowledgeBase.Stage() Issue encountered inserting Qualys Vulnerability data into staging table...
System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.
   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
   --- End of inner exception stack trace ---

Environment

Release : 6.5.4

Component : Qualys Importer

Cause

Querying the Title field in the Qualys Vulnerability Knowledgebase returns a value with a string length greater than 255 characters. The corresponding fields in the stg_Vulnerabilities and Vulnerabilities tables in the QualysGuardDW and RiskFabric databases are each defined as 255 characters, resulting in a string length mismatch.

Resolution

The datatype and string length definitions of the Title field in both the stg_Vulnerabilities and Vulnerabilities tables in the QualysGuradeDW database must be updated to nvarchar(512). The stored procedure spUpdateStg_Qualys must also be updated to select a substring of the Title field.

To make these changes, follow this procedure:

1. Download the script to update spUpdateStg_Qualys [attached to this KB article as spUpdateStg_Qualys_1601317652359.sql]
2. Open SQL Server Management Studio (SSMS)
3. In the Connect to Server window, select the following:
   • Server type: Database Engine
   • Server name: RiskFabric database hostname
   • Authentication: account credentials to alter the RiskFabric database
   • Click Connect
4. In Object Explorer, expand the Databases folder
5. Right-click on the QualysGuardDW database and select New Query
6. Copy and paste the following query into the query editor window:

   ALTER TABLE stg_Vulnerabilities ALTER COLUMN [Title] NVARCHAR(512) NULL;

7. Open the Query menu and select Execute, or press the F5 function key to execute the statement
8. Right-click on the QualysGuardDW database and select New Query
9. Copy and paste the following query into the query editor window:

   ALTER TABLE Vulnerabilities ALTER COLUMN [Title] NVARCHAR(512) NULL;

10. Open the Query menu and select Execute, or press the F5 function key to execute the statement
11. Open the File menu and select Open > File...
12. Locate and select the script downloaded from this article in step 1 and click Open
13. Select the RiskFabric database in the drop-down list of databases in the Standard toolbar (RiskFabric is the default name of the ICA database but may differ in your environment)

    

14. Open the Query menu and select Execute, or press the F5 function key to execute the script