Current date jdbc plugin

Elastic Stack Logstash
1

Hi,
is it possible to filter query inside jdbc input by CURRENT_DATE?
I have the following config:

input {
    jdbc {
      statement => "SELECT userId, timestamp FROM users;"
      schedule => "* * * * *"
      tags => ["kpi"]
    }
 }

I'd like to change it to:

input {
    jdbc {
      statement => "SELECT userId, timestamp FROM users WHERE timestamp > CURRENT_DATE"; 
      schedule => "* * * * *"
      tags => ["kpi"]
    }
 }

where
CURRENT_DATE -> current date in format like that 2021-01-01T00:00:00.000Z

Is it possible to obtain current date in Logstash?

Regards,
Kuba

2

It's a much better idea to do this in the SQL by using TRUNC(sysdate).

SELECT userId, timestamp FROM users WHERE timestamp >= TRUNC(sysdate)

1 Like
3

Big thanks for the hint and help.
Do you have idea what to do in case of http_poller?, here it seems like I'd need somehow to get current date in Logstash,

http_poller {
  urls => {
    executedsteps => {
      method => get
      url => "https://url/v2.0/data?$filter=timestamp gt CURRENT_DATE&apikey=APIKEY" 
      headers => {
        Accept => "application/json"
      } 
    }
  } 
}

Best,
Kuba

4

You can use environment variables as well.

You will have to set the environment variable before you start logstash to the value you want to input into the url.
This would technically work for the first JDBC input as well, but in that case it's much better to let SQL do the work.

In shell:

root@ubuntu-01:~# export CURRENT_DATE=`date --iso-8601`
root@ubuntu-01:~# echo $CURRENT_DATE
2021-07-21

Then in your config you can put:
url => "https://url/v2.0/data?$filter=timestamp gt ${CURRENT_DATE}&apikey=APIKEY"

5

Thanks for that.
Do you know how to make CURRENT_DATE variable to update itself periodically and automatically under Linux?

6

Since the CURRENT_DATE variable is actually running the date command it will always be up to date. HOWEVER, I'm not sure if it will update in logstash after logstash starts.
In the documentation it states:

  • At Logstash startup, each reference will be replaced by the value of the environment variable.

So it is entirely possible that once logstash starts it will stay at a fixed current time.

If you want the current timestamp to update every time in your input then you'll have to restart logstash every day.

7

export CURRENT_DATE=date --date="90 seconds ago" +%Y-%m-%dT%TZ

As I checked it seems like CURRENT_DATE is calling date function once. Since value of CURRENT_DATE in my case doesn't get updated

8

See also here.

9

Blockquote
You cannot do that with an http_poller input . However, you could use http_poller just for its scheduler (or exec, or something else that has a scheduler option), then use an http filter to make the request.

Could you expalin a bit more?
Maybe with some example?
Thanks,
Kuba

10

I cannot provide an example, but the idea is that you use a filter with a schedule option to create events periodically. These events need not have any fields of interest. You might even use a prune filter to delete any fields that the input creates.

You could use an exec input to run the date command, in which case obviously you would keep that field. Or else use a ruby filter to run some ruby code that generates a timestamp from 90 seconds ago and add it to the event after the prune.

Then use an http filter to make the call to "https://url/v2.0/data?$filter=timestamp gt CURRENT_DATE&apikey=APIKEY".

11

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