Last run reflect in db is possible or not?


(Gokul Kathirvel) #1

i have created config file in that i have coded such that for every 2mins my db is called ,it works perfectly...
and i have have column name "date(TImestamp) in db : eg:2018-07-06 11:11:19.754371",
i have to overwrite r update the value of timestamp with new timestamp..
any suggestion??
my code is:
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://localhost:5432/postgres"
jdbc_user => "postgres"
jdbc_password =>"xxx"
jdbc_driver_library => "C:\logstash-6.3.0\postgresql-42.2.2.jar"
jdbc_driver_class => "org.postgresql.Driver"
last_run_metadata_path => 'C:\xx\xx.logstash_jdbc_last_run'
record_last_run => true
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
schedule =>'*/2 * * * *'

	 statement => "SELECT * from contacts where date > :sql_last_value"   
	  }

}
output{
stdout{ }
}


(Magnus Bäck) #2

You want to update the timestamp column in the database each time Logstash has run? There's no trivial support for that, unless you perhaps can call a stored procedure that performs the update but also returns the result set of a select query.


(Gokul Kathirvel) #3

is it possible to save records in file rather than procedure??if so?? how?? shall v use template??


(Magnus Bäck) #4

Save which records? The timestamp? Did you read everything that's said about the sql_last_value query parameter in the jdbc input documentation?


(Gokul Kathirvel) #5

output{
file{
path=>"C:\logstash-6.3.0\map.log"
}
} i have done like this,but data is not posted on the specific map.log file


(Gokul Kathirvel) #6

output{
file{
path=>"C:\logstash-6.3.0\map.log"
}
} i have done like this,but data is not posted on the specific map.log file


(Magnus Bäck) #7

If you replace the file output with a stdout { codec => rubydebug } output, are you getting anything in the Logstash log?


(Gokul Kathirvel) #8

yes..for every 2 mins it call db
[2018-07-06T18:18:00,359][INFO ][logstash.inputs.jdbc ] (0.001542s) SELECT count() AS "count" FROM (SELECT * from contacts where date > '2018-07-06 18:16:00.080000+0530') AS "t1" LIMIT 1
[2018-07-06T18:20:00,313][INFO ][logstash.inputs.jdbc ] (0.000884s) SELECT CAST(current_setting('server_version_num') AS integer) AS v
[2018-07-06T18:20:00,319][INFO ][logstash.inputs.jdbc ] (0.001579s) SELECT count(
) AS "count" FROM (SELECT * from contacts where date > '2018-07-06 18:18:00.353000+0530') AS "t1" LIMIT 1
[2018-07-06T18:22:00,270][INFO ][logstash.inputs.jdbc ] (0.000853s) SELECT CAST(current_setting('server_version_num') AS integer) AS v


(Magnus Bäck) #9

Sure, but is that query producing any events in Logstash? If not then nothing will end up in the output file, obviously.


(Gokul Kathirvel) #11

only generation these :slightly_smiling_face:
[2018-07-06T18:20:00,319][INFO ][logstash.inputs.jdbc ] (0.001579s) SELECT count() AS "count" FROM (SELECT * from contacts where date > '2018-07-06 18:18:00.353000+0530') AS "t1" LIMIT 1
[2018-07-06T18:22:00,270][INFO ][logstash.inputs.jdbc ] (0.000853s) SELECT CAST(current_setting('server_version_num') AS integer) AS v


(Magnus Bäck) #12

Should that query return anything, then? What if you run it from a query console of the database server?


(system) #13

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