How do I create a DB query with variable parameters?

Elastic Stack Logstash
1

Hi,
I'd like to set up a parameterized query. I configured my logstash.conf to query my Oracle database. My table has a REF_DATE field valued with a date CHAR(10 BYTE).
I need to make the following query:

`SELECT COUNT (*) FROM MY_TABLE WHERE REF_DATE = <TODAY_DATE>`

This query will be executed one or more time every day and <TODAY_DATE> will be change every day.
I tried to use the :sql_last_value parameter but I did not reach my goal.

Is it possibile to configure <TODAY_DATE> into logstash.conf or sending this parameter from kibana tool?

Could you help me please?
Thank you

2

Could this be done with the Oracle CURRENT_DATE?

SELECT COUNT (*) FROM MY_TABLE WHERE REF_DATE = TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')
3

Thanks! It is useful but in my question I forgot to write that I need to query even for past dates, like for example:

SELECT COUNT (*) FROM MY_TABLE WHERE REF_DATE = TO_CHAR(PAST_DATE, 'YYYY-MM-DD')

How can I pass a variable past date as input to my query?
Thank you.

4

That could be done with a GROUP BY clause, which would emit one event per REF_DATE value:

SELECT REF_DATE, COUNT(*) FROM MY_TABLE GROUP BY REF_DATE
1 Like
5

Thanks yaauie! I understood how to do it.

6

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