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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...