Logstash-jdbc-input Oracle SQL issue

Elastic Stack Logstash
1

I'm having trouble trying to figure out why my SQL statement is not working. I've tested enough combinations that I have it narrowed down to a single line causing issue.
"AND collection_system_date >= SYSDATE - 300/86400"

But I can't see why the line is causing issue as I can use it in normal SQL client or other JDBC client just fine.

Below is the complete SQL statement I'm testing. If I comment out "--" the one line above it will run fine and return data. I've tried all combinations of paraphrases around the numbers and sysdate, but nothing works.

SELECT
collection_system_date
, ne_name
, collector_name
, collector_rpu_host_name
, collection_status
FROM
collector_statuses
WHERE
ne_model like 'cyan%'
AND collection_system_date >= SYSDATE - 300/86400
AND collection_system_date <= SYSDATE

I've tried setting the sql_log_level to "debug" but then I get nothing at all in the /var/log/logstash/logstash-plain.log file. If it goes someplace else in debug mode I'm not finding it and could help knowing where.

Thanks

2

does it work from SQLPLus?

well since SYSTEM date is the current time, and I presume I understand what your doing, You can't ever collect data in to the future unless your system time is not synchronized

just having collection_system_data >= SYSDATE -300/86400 will give you all records from 5 minutes ago to now and in to the future which should never happen unless you have out of sync system clocks.

3

There is no problem with my original SQL as I use it many other places. However....

Your reply got me to thinking that maybe something about the dates is not correct.
I did a quick test with logstash to see what dates would return in a query of Oracle's builtin functions. I have noticed that everything is coming back in UTC time, but according to my query it should be in America/New_York.

SELECT current_date, current_timestamp, dbtimezone, localtimestamp, sessiontimezone, sysdate, systimestamp FROM dual;

{"sysdate":"2017-04-13T14:43:00.000Z","@timestamp":"2017-04-13T14:43:00.462Z","current_timestamp":"2017-04-13T14:43:00.427Z","@version":"1","sessiontimezone":"America/New_York","localtimestamp":"2017-04-13T14:43:00.427Z"}

However even adjusting my query for the difference (subtract another 4 hours) doesn't return anything. I even went ahead and just passed it "- 1" to subtract whole date and still doesn't work.

I figured out turning on debug in logstash I could then also turn on debug in jdbc input filter. Below is the output from the log file.

[2017-04-13T11:06:59,195][DEBUG][logstash.agent           ] no configuration change for pipeline {:pipeline=>"main"}
[2017-04-13T11:07:00,359][DEBUG][logstash.inputs.jdbc     ] (0.143000s) SELECT * FROM (SELECT count(*) "COUNT" FROM (SELECT
to_char(collection_system_date,'MM/DD/YY HH24:MI:SS') collection_system_date
, ne_name
, collector_name
, collector_rpu_host_name
, collection_status
FROM
collector_statuses
WHERE
ne_model like 'cyan%'
AND collection_system_date >= sysdate - 1
AND collection_system_date <= sysdate
) "T1") "T1" WHERE (ROWNUM <= 1)
[2017-04-13T11:07:00,366][DEBUG][logstash.inputs.jdbc     ] Executing JDBC query {:statement=>"SELECT\nto_char(collection_system_date,'MM/DD/YY HH24:MI:SS') collection_system_date\n, ne_name\n, collector_name\n, collector_rpu_host_name\n, collection_status\nFROM\ncollector_statuses\nWHERE\nne_model like 'cyan%'\nAND collection_system_date >= sysdate - 1\nAND collection_system_date <= sysdate\n", :parameters=>{:sql_last_value=>1970-01-01 00:00:00 UTC}, :count=>0}
[2017-04-13T11:07:00,466][DEBUG][logstash.inputs.jdbc     ] (0.098000s) SELECT
to_char(collection_system_date,'MM/DD/YY HH24:MI:SS') collection_system_date
, ne_name
, collector_name
, collector_rpu_host_name
, collection_status
FROM
collector_statuses
WHERE
ne_model like 'cyan%'
AND collection_system_date >= sysdate - 1
AND collection_system_date <= sysdate

[2017-04-13T11:07:02,180][DEBUG][logstash.pipeline        ] Pushing flush onto pipeline
4

Closing topic as this was user error.

After further investigation I forgotten the test DB is always 2 days behind production. Once I moved the WHERE clause to compare back to 2 days plus it all worked.

1 Like
5

sometimes, it is good to just "voice" out the problem and that provides a solution as you talk it out.

Glad I could be a sounding board for you

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.