Deployment Architecture

DB Connect: Why am I getting errors trying to specify EVENT_TIMESTAMP as the rising column to pull audit data from Oracle?

bojanz
Communicator

I'm trying to pull Oracle SYS.UNIFIED_AUDIT_TRAIL table with DB Connect. The rising column should be EVENT_TIMESTAMP, which has type TIMESTAMP(6) WITH LOCAL TIMEZONE.

However, when I try to specify this as the rising column in DB Connect, I get the following error:

Unable to fetch serializable value for column "EVENT_TIMESTAMP" with sql_type=EVENT_TIMESTAMP with query  ...

The query works perfectly fine from DB Connect's DB Query window, but I can't specify it as a dbmon tail input.
Any ideas? I tried various conversions, but DB Connect always complains about something (either the "$rising_column$" variable is missing or something else).

1 Solution

bojanz
Communicator

To answer my own question since I managed to figure this out.
So, DB Connect (or the Oracle JAR) is having some problems with this column type (TIMESTAMP). The workaround is to manually convert that column to something human readable and then to convert that back to the TIMESTAMP type in the WHERE clause.

Something like this works for the SQL query:

SELECT TO_CHAR(EVENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') AS EVENT_TIMESTAMP, ... (other columns) ... FROM SYS.UNIFIED_AUDIT_TRAIL {{ WHERE $rising_column$ > TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS') }}

Rising column must be set to EVENT_TIMESTAMP.
Additionally, in order for DB Connect to correctly parse time, the following must be set manually in inputs.conf:

output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss

View solution in original post

bojanz
Communicator

To answer my own question since I managed to figure this out.
So, DB Connect (or the Oracle JAR) is having some problems with this column type (TIMESTAMP). The workaround is to manually convert that column to something human readable and then to convert that back to the TIMESTAMP type in the WHERE clause.

Something like this works for the SQL query:

SELECT TO_CHAR(EVENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') AS EVENT_TIMESTAMP, ... (other columns) ... FROM SYS.UNIFIED_AUDIT_TRAIL {{ WHERE $rising_column$ > TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS') }}

Rising column must be set to EVENT_TIMESTAMP.
Additionally, in order for DB Connect to correctly parse time, the following must be set manually in inputs.conf:

output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...