NetWitness Log Collector/Log Decoder: Oracle 12c: Failed to onboard the SYS.UNIFIED_AUDIT_TRAIL

[#1]

Article Number

000003168

Applies To

RSA Product Set: NetWitness Platform
RSA Product/Service Type: Log Collector, VLC
RSA Version/Condition: 12.x
Platform: CentOS
Platform (Other): ODBC, Oracle 12c
O/S Version: EL7

Issue

When using the Oracle 12c ODBC event source on a Log Collector, an error occurs in /var/log/messages due to invalid timestamp formatting:

NwLogCollector[17121]: [OdbcCollection] [failure] [oracle_12c_auditing.xxxxxx] [processing] [xxxxxx] [processing] Error finding any new events. Reason: Unable to execute statement: Statement: "select audit_type, "SYS"."V_$INSTANCE"."VERSION", "SYS"."V_$INSTANCE"."HOST_NAME", "SYS"."V_$INSTANCE"."INSTANCE_NAME", sessionid, proxy_sessionid, entry_id, statement_id, TO_CHAR(cast(("SYS"."UNIFIED_AUDIT_TRAIL"."EVENT_TIMESTAMP" at local) as date),'yyyy-mm-dd hh24:mi:ss') "timestamp", os_username, userhost, terminal, dbid, authentication_type, dbusername, dbproxy_username, external_userid, global_userid, client_program_name, action_name, return_code, os_process, object_schema, object_name, system_privilege, system_privilege_used, new_schema, new_name, sql_text, sql_binds, system_privilege_used, audit_option, role, target_user, unified_audit_policies, fga_policy_name, rman_operation, rman_object_type, rman_device_type from "SYS"."UNIFIED_AUDIT_TRAIL" LEFT JOIN "SYS"."V_$INSTANCE" ON instance_id=INSTANCE_NUMBER WHERE (cast(("SYS"."UNIFIED_AUDIT_TRAIL"."EVENT_TIMESTAMP" at local) as date)) > to_date('2024-02-15 13:04:49','yyyy-mm-dd hh24:mi:ss') order by timestamp ASC"; Reason: state: 57; error-code: 904; description: [RSA][ODBC 20101 driver][Oracle]ORA-00904: "TIMESTAMP": invalid identifier

Resolution

The file /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_unified_audit_12c.xml needs to be modified to update timestamp to the correct format.

1. Make a backup of the file:

cp -i oracle_unified_audit_12c.xml /root/oracle_unified_audit_12c.bkp

2. Then, about ten lines from the bottom of the file is this line:

WHERE (cast(("SYS"."UNIFIED_AUDIT_TRAIL"."EVENT_TIMESTAMP" at local) as date)) >  to_date('%TRACKING%','yyyy-mm-dd hh24:mi:ss') order by "timestamp" ASC

3. "timestamp" needs to be changed to SYS.UNIFIED_AUDIT_TRAIL.EVENT_TIMESTAMP.
NOTE: The timestamp ASC line may not have the quotes, an older fix was to add quotes around timestamp in this file.

vi oracle_unified_audit_12c.xml

4. Use / to search or Shift G to go to the end of the file, then use x to remove "timestamp", i to insert and right click to paste the correctly formatted event, it should look like the below:

WHERE (cast(("SYS"."UNIFIED_AUDIT_TRAIL"."EVENT_TIMESTAMP" at local) as date)) >  to_date('%TRACKING%','yyyy-mm-dd hh24:mi:ss') order by SYS.UNIFIED_AUDIT_TRAIL.EVENT_TIMESTAMP ASC

5. Use ESC to exit insert mode, then :zq or Shift ZZ to save and exit. Restart the nwlogcollector service, and the error in /var/log/messages will be resolved.

systemctl restart nwlogcollector && tailf /var/log/messages