Timing issue caused by UTC in last_run_metadata_path?

Elastic Stack Logstash
1

We have a setup where SLQ rows are shipped to a logserver every couple of minutes. The SQL is picked up, filtered by adding fields and replacing timestamp with the last election date of the SQL (from db), and sent on. Config as follows:

input {
	jdbc {
		jdbc_driver_library => "/opt/sqljdbc/sqljdbc42.jar"
		jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
		jdbc_connection_string => "jdbc:<sqls.erver>://sql.server:1433;databaseName=<dbname>;integratedSecurity=false"
		jdbc_user => "nn"
		jdbc_password => "nn"
		schedule => "*/5 * * * *"
		statement => "SELECT text, convert (nvarchar, last_execution_time, 21)
			FROM sys.dm_exec_query_stats AS deqs
			CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
			WHERE dest.text LIKE 'insert%'
        		OR dest.text LIKE 'update%'
        		OR dest.text LIKE 'delete%'
			OR dest.text LIKE 'select%'
			"
	  }
}

filter {
	mutate {
		rename => { "text" => "short_message" }
		add_field => { "host" => "sql.server" }
	}
	date {
		match => [ "untitled", "YYYY-MM-dd HH:mm:ss.SSS" ]
		remove_field => [ "untitled" ]
	}
}

The machine is on Europe/Berlin time zone. When run, the last_run_metadata_path file is updated with the UTC time stamp (ie. -1hr as compared to machine time). This, unfortunately leads to a re-processing of all db entries that are within this -1hr difference between UTC and CET, which are thousands. So for the chosen 5min schedule, we end up with 20 times the same entries in the log server.

The check closer, I have manually changed the timestamp in .logstash_jdbc_last_run to match CET (just added 1hr) and this avoided the reprocessing of all messages in that 1hr gap.

Any suggestion how this could be prevented? I have tried all sorts of workarounds using jdbc_default_timezone, tracking_column, use_column_value, adding sql_last_value to the search query, etc. without success.

If the above seems to be a terribly stupid question, please forgive me; I am somewhat new to the world of logstash but think it's a great tool that I will hopefully master soon... Thanks a lot for any help resolving.

2

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