Archive

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

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

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

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