Identifying when logstash jdbc SQL statement has completed execution

Question

I've set up a logstash pipeline watching a directory for logstash.confs with a jdbc input and elasticsearch output.

I enabled .logstash_jdbc_test_last_run, which logs the time when query is executed:

--- 2019-08-23 15:26:42.847349000 Z

When running logstash on the command line, my pipeline successfully processes a logstash conf file:

[2019-08-23T15:26:48,168][INFO ][logstash.inputs.jdbc     ] (5.250782s) select S0.* from mytable S0

I'm trying to have my application notified that logstash has finished processing a logstash conf and the data is available in ES. Is there a way to write the timestamp when the query has completed to a file (for use as a flag in my application)?

score 1 · Answer 1 · answered Aug 25 '19 at 07:50

I am not aware that Logstash logs the timestamp when it has actually completed an SQL query. What you can do anyway is to implement an additional pipeline where you read the .logstash_jdbc_test_last_run file. From my experience, Logstash will not update the timestamp until it has fetched all records.

So this pipeline would look something like that (untested):

input{
  file{
    path => ["/your/absolute/path/to/.logstash_jdbc_test_last_run"]
    file_completed_action => "log"
    file_completed_log_path => "/path/where/logfile/should/be/stored"
    mode => "read"
    codec => "plain" # default, you could do json as well
  }
}
output{
  file{
    path => "/foo/bar/logstash_runs.txt"
  }
}

This pipeline would recognize every new/updated timestamp of the file and append it to the specified file you configured in the output plugin.

Take a look at the file input plugin as well as the file output plugin for all configuration settings.

Dan Stark · Accepted Answer · 2019-08-27T17:33:40.800

Ultimately I was able to get the desired result by leveraging multiple pipelines Thanks @apt-get_install_skill, you led me down the right path!

pipelines.yml:

- pipeline.id: logstash
  pipeline.workers: 1
  path.config: "/tmp/logstash*.conf"
  queue.type: persisted
- pipeline.id: postprocessing
  pipeline.workers: 1
  path.config: "/tmp/postprocessing*.conf"

logstash.conf:

input {
   jdbc {
     ...
   }
}
output   {
    elasticsearch {
      ...
    }
    pipeline { send_to => [postProcessing] }
}

postprocessing.conf, which uses the output from the logstash pipeline:

input {
    pipeline { address => postProcessing }
}
output {
    file{
        path => "/tmp/finishedflag"
        codec => "dots"
    }
}

Codec dots as I don't care about the data itself.

When it runs, it will first run the logstash pipeline, and when it completes it runs the postprocessing pipeline.

Identifying when logstash jdbc SQL statement has completed execution

2 Answers2