Problem with oracle dates though logstasg

Elastic Stack Logstash
1

Hi all,

We are migrating some queries used on logstash on a previous DB2 pipeline to a new oracle database pipeline.

We have checked them on db clients like Dbvisualizer or dbviewer and running these converted queries on those clients they run ok and we get the results we expect to see...

But when we run exactly the same queries through logstash on the pipeline we get this error on 4 of them:

[2025-02-19T13:48:00,365][ERROR][logstash.inputs.jdbc     ][pasarelas][74271d402d8c348a7bb2902d60c97784c82a7b2ce1db74c13e541df17bc84b28] Java::JavaSql::SQLDataException: ORA-01830: date format picture ends before converting entire input string: select FEC_REGISTRO,
NOM_WS as SERVICIO,
IDE_USU_EMP,
HOR_INI_REGISTRO,
HOR_FIN_REGISTRO,
TO_DATE(CONCAT(CONCAT(TO_CHAR(CAST(FEC_REGISTRO as date),'YYYY-MM-DD'),' '),TO_CHAR(HOR_INI_REGISTRO,'TS')),'YYYY-MM-DD HH24:MI:SS') as FECHATMST,
NUM_INVOCACIONES,
NUM_EJE_CORREC,
NUM_EJE_ERROR,
NUM_NO_EJE_POOL,
NUM_TMP_TOT_ESP,
NUM_TMP_MED_ESP_P,
NUM_TMP_MAX_ESP_P,
NUM_TMP_MIN_ESP_P,
NUM_EJE_SIN_ESP,
NOM_NODO,
NUM_MAX_ESP_R_IN,
NUM_MED_ESP_R_IN,
NUM_TOT_ESP_R_IN
from DB2H.HCTBF1W where 
FEC_REGISTRO >= SYSDATE - interval '1' day   and
TO_DATE(CONCAT(CONCAT(TO_CHAR(CAST(FEC_REGISTRO as date),'YYYY-MM-DD'),' '),TO_CHAR(HOR_INI_REGISTRO,'TS')),'YYYY-MM-DD HH24:MI:SS') >= CURRENT_TIMESTAMP - interval '120'  minute and
substr(TO_CHAR(HOR_INI_REGISTRO,'TS'),1,2) = substr(TO_CHAR(sysdate - interval '60' minute,'TS'),1,2)

Any idea what we are missing??

best regards

Borja

2

I suspect those date manipulations. I wonder if one of the formats is not what you expect due to locale or langauge issues. Perhaps try examining exactly what the TO_CHAR calls are returning in logstash. Can you do things like

SELECT TO_CHAR(HOR_INI_REGISTRO,'TS') from DB2H.HCTBF1W

etc.? And I mean do that in a jdbc input, not in a DB CLI.

3

Hi Badger,

I was able to test what you mentioned and you were right. The TS format eliminates the zeros on the left of the time field and that caused it to fail.

I have changed the TS option to TO_CHAR(HOR_INI_REGISTRO,'HH24:MI:SS') and logstash has ingested those queries ok.

Thanks!!

Borja