Importing XPS Audit log to MSSQL is failing.

Products

• CA Single Sign On Secure Proxy Server (SiteMinder)
• CA Single Sign On SOA Security Manager (SiteMinder)
• CA Single Sign-On

Issue/Introduction

Description:

Following the manual to import the XPS Audit log files (tmp files generated by HARVEST script) fail to get imported to MSSQL SM Audit DB with the following error.

SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

Solution:

The error means there is a value that exceeds the pre-defined column size.

The tmp files generated by the Harvest script (precisely the ProcessAudit.pl script) records the "sm_objoid" data by duplicating the "sm_objname".

It will truncate the value if it exceeds 64 characters.

But the value is also being wrapped with double quotes resulting in 66 characters.

These double quotes actually cause these errors and get saved in the Audit DB as well.

These double quotes need to be handled in the format.txt file so that they get dropped before being imported to the DB.

Following sample format.txt file demonstrates how you can drop those double quotes so that only the actual values can be imported.

format.txt file:

9.0
15
1       SQLCHAR       0       0      "\""       0     ""                   ""
2       SQLCHAR       0       24    "\""        1     sm_timestamp                   ""
3       SQLCHAR       0       0      ","        0     ""                   ""
4       SQLCHAR       0       12    ","         2     sm_categoryid                  ""
5       SQLCHAR       0       12    ","         3     sm_eventid                     ""
6       SQLCHAR       0       0      "\""       0     ""                   ""
7       SQLCHAR       0       255   "\""        4     sm_hostname                    ""
8       SQLCHAR       0       0      ",\""      0     ""                   ""
9       SQLCHAR       0       255     "\""      5     sm_sessionid                   ""
10      SQLCHAR       0       0      ",\""      0     ""                   ""
11      SQLCHAR       0       512     "\""      6     sm_username                    ""
12      SQLCHAR       0       0      ",\""      0     ""                   ""
13      SQLCHAR       0       512     "\""      7     sm_objname                     ""
14      SQLCHAR       0       0      ",\""      0     ""                   ""
15      SQLCHAR       0       64      "\"\r\n"  8     sm_objoid                      ""

Environment

Release:
Component: SMPLC