When configuring NetWitness ODBC collection from Oracle 10g or 11g databases using the default template with extended logging enabled, an error similar to the example below is observed.
[OdbcCollection] [failure] [oracle_11g_auditing.dbanfs] [processing] [dbanfs] [processing] Data query failed; dataQuery: SELECT "SYS"."V_$INSTANCE"."VERSION", '-', "SYS"."V_$INSTANCE"."HOST_NAME", '-', "SYS"."V_$INSTANCE"."INSTANCE_NAME", '-', '-', "SYS"."AUD$"."SESSIONID", "SYS"."AUD$"."ENTRYID", "SYS"."AUD$"."STATEMENT", CASE "SYS"."AUD$"."ACTION#" WHEN 101 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') WHEN 102 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') ELSE TO_CHAR(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date),'yyyy-mm-dd hh24:mi:ss') END "timestamp", "SYS"."AUD$"."USERID", "SYS"."AUD$"."USERHOST", "SYS"."AUD$"."TERMINAL", "SYS"."AUD$"."ACTION#", "SYS"."AUD$"."RETURNCODE", "SYS"."AUD$"."OBJ$CREATOR", "SYS"."AUD$"."OBJ$NAME", "SYS"."AUD$"."AUTH$PRIVILEGES", "SYS"."AUD$"."AUTH$GRANTEE", "SYS"."AUD$"."NEW$OWNER", "SYS"."AUD$"."NEW$NAME", "SYS"."AUD$"."SES$ACTIONS", "SYS"."AUD$"."SES$TID", "SYS"."AUD$"."LOGOFF$LREAD", "SYS"."AUD$"."LOGOFF$PREAD", "SYS"."AUD$"."LOGOFF$LWRITE", "SYS"."AUD$"."LOGOFF$DEAD", COALESCE(TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss'),' ') as LOGOFF$TIME, "SYS"."AUD$"."COMMENT$TEXT", "SYS"."AUD$"."CLIENTID", "SYS"."AUD$"."SPARE1", "SYS"."AUD$"."SPARE2", "SYS"."AUD$"."OBJ$LABEL", "SYS"."AUD$"."SES$LABEL", "SYS"."AUD$"."PRIV$USED", "SYS"."AUD$"."SESSIONCPU" , cast("SYS"."AUD$"."SQLBIND" as varchar(2000)) as SQL_BIND, cast("SYS"."AUD$"."SQLTEXT" as varchar(2000)) as SQL_TEXT FROM "SYS"."AUD$", "SYS"."V_$INSTANCE" WHERE (cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND SYSDATE-(2/(3600*24)g] Error finding any new events. Reason: SQLFetch failed: Statement: "SELECT "SYS"."V_$INSTANCE"."VERSION", '-', "SYS"."V_$INSTANCE"."HOST_NAME", '-', "SYS"."V_$INSTANCE"."INSTANCE_NAME", '-', '-', "SYS"."AUD$"."SESSIONID", "SYS"."AUD$"."ENTRYID", "SYS"."AUD$"."STATEMENT", CASE "SYS"."AUD$"."ACTION#" WHEN 101 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') WHEN 102 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') ELSE TO_CHAR(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date),'yyyy-mm-dd hh24:mi:ss') END "timestamp", "SYS"."AUD$"."USERID", "SYS"."AUD$"."USERHOST", "SYS"."AUD$"."TERMINAL", "SYS"."AUD$"."ACTION#", "SYS"."AUD$"."RETURNCODE", "SYS"."AUD$"."OBJ$CREATOR", "SYS"."AUD$"."OBJ$NAME", "SYS"."AUD$"."AUTH$PRIVILEGES", "SYS"."AUD$"."AUTH$GRANTEE", "SYS"."AUD$"."NEW$OWNER", "SYS"."AUD$"."NEW$NAME", "SYS"."AUD$"."SES$ACTIONS", "SYS"."AUD$"."SES$TID", "SYS"."AUD$"."LOGOFF$LREAD", "SYS"."AUD$"."LOGOFF$PREAD", "SYS"."AUD$"."LOGOFF$LWRITE", "SYS"."AUD$"."LOGOFF$DEAD", COALESCE(TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss'),' ') as LOGOFF$TIME, "SYS"."AUD$"."COMMENT$TEXT", "SYS"."AUD$"."CLIENTID", "SYS"."AUD$"."SPARE1", "SYS"."AUD$"."SPARE2", "SYS"."AUD$"."OBJ$LABEL", "SYS"."AUD$"."SES$LABEL", "SYS"."AUD$"."PRIV$USED", "SYS"."AUD$"."SESSIONCPU" , cast("SYS"."AUD$"."SQLBIND" as varchar(2000)) as SQL_BIND, cast("SYS"."AUD$"."SQLTEXT" as varchar(2000)) as SQL_TEXT FROM "SYS"."AUD$", "SYS"."V_$INSTANCE" WHERE (cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND SYSDATE-(2/(3600*24)) > cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) AND "SYS"."AUD$"."ACTION#" NOT IN (101,102)) OR ("SYS"."AUD$"."LOGOFF$TIME" > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND "SYS"."AUD$"."ACTION#" IN (101,102) AND SYSDATE-(2/(3600*24)) > "SYS"."AUD$"."LOGOFF$TIME" ) ORDER BY 11 ASC"; Reason: state: S1000; error-code: 22835; description: [RSA][ODBC Oracle Wire Protocol driver][Oracle]ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 25318, maximum: 4000)
The error means the SQL query is selecting data from a field that is defined as an Oracle LOB (Large OBjects) data type.
Where CLOB is Character Large OBject.
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)
DB_BLOCK_SIZE specifies (in bytes) the size of Oracle database blocks. Typical values are 4096 and 8192 (range 2048 to 16384 for 32-bit OS & 32768 for 64-bit OS).
The data returned is too long to fit into the target data type, VARCHAR2 (maximum size 4,000 bytes).
CAST does not directly support any of the LOB datatypes. When using CAST to convert a CLOB value into a character datatype, the database implicitly converts the LOB value to characters, and then explicitly casts the resulting value into the target datatype. If the resulting value is larger than the target type, then the database returns an error.
The SQL query will work for returned data less than equal to 4,000 characters. In the above example the returned data is 25,318 characters, hence the error.
To resolve the issue, download the XML files attached to this article (
Oracle-auditing.zip), and follow the steps below for each Log Collector appliance.
1. SSH to the Log Collector appliance as the root user, and stop the nwlogcollector service.
systemctl stop nwlogcollector
2. On the Log Collector, navigate to the /etc/netwitness/ng/logcollection/content/collection/odbc directory, and create a backup of the original oracle_10g_auditing.xml and oracle_11g_auditing.xml files.
cp -p /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_10g_auditing.xml /root/oracle_10g_auditing.xml.bkup
rm -f /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_10g_auditing.xml
cp -p /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_11g_auditing.xml /root/oracle_11g_auditing.xml.bkup
rm -f /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_11g_auditing.xml
3. Replace these files with those attached to this article.
Warning: Do not keep a backup of the .xml files in the /etc/netwitness/ng/logcollection/content/collection/odbc/ directory, SA will use the first oracle_10g_auditing*, and the first oracle_11g_auditing* file it finds in this directory.
4. Start the nwlogcollector service again.
systemctl start nwlogcollector
5. Check whether or not the ODBC collection has started.
6. Check whether or not ODBC logs are being collected for the given event sources.
Warning: The above workaround will be reverted if a new "Oracle Log Collector Configuration" is deployed from RSA Live to the Log Collector appliances.
Addendum:
A. After making the above change, there may occur an error relating to :basic_string::_S_create
Image descriptionThis error is due to the Max Cell Size default setting is 2048 characters, and now the above change truncates the CLOB fields at 4,000 characters.
To increase the Max Cell Size in the NetWitness UI, Administration > Services > {Log Collector} > Config
In the Event Sources tab.
- Choose ODBC and Config in the dropdowns.
- Edit each oracle_10g_auditing and oracle_11g_auditing Source.
- Open Advanced.
- Change the "Max Cell Size" value to 4096 (default setting is 2048).
Image description
- Click OK.
- Restart the nwlogcollector service.
systemctl restart nwlogcollector
B. If the new oracle_10g_auditing.xml and oracle_11g_auditing.xml files
fail with the error ORA-00904: : invalid identifier then see the following RSA Knowledgebase article,
Running revised NetWitness oracle auditing xml files fails with error ORA-00904: : invalid identifierIf you are unsure of any of the steps above or experience any issues, contact RSA Support and quote this article number for further assistance.