Sql_Last_Value date format seems to be irrelevant

[Mohan_vel]

Hi All i am using logstash to index document from MSSQL server to elasticsearch, i am using below config file in order to do an incremental indexing, for that i am using using column called modified_date but having problem with dateformat.

input {
jdbc {
jdbc_driver_library => "D:/Users/xxxxx/Desktop/driver/mssql-jdbc-7.4.1.jre12-shaded.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxx"
jdbc_password => "xxxx"
jdbc_paging_enabled => true
tracking_column => modified_date
use_column_value => true
clean_run => true
tracking_column_type => "timestamp"
schedule => "*/1 * * * *"
statement => "Select pl.policynumber,pl.policyholdername,pl.dob,pl.age,pl.client_address clientaddress,cl.claimnumber,Cl.claimtype,cl.modified_date modified_date,Cl.is_active from policy pl
inner join claim Cl on Cl.policynumber=pl.policynumber where cl.modified_date >:sql_last_value"
}
}
filter {
if [is_active] {
        mutate {    
            add_field => {
                "[@metadata][elasticsearch_action]" => "index"
            }
        }
        mutate {
            remove_field => [ "is_active","@version","@timestamp" ]
        }
    } else {
        mutate {    
            add_field => {
                "[@metadata][elasticsearch_action]" => "delete"
            }
        }
        mutate {
            remove_field => [ "is_active","@version","@timestamp" ]
        }
} 
}
output {
elasticsearch {
hosts => "https://e5a4a4a4de7940d9b12674d62eac9762.eastus2.azure.elastic-cloud.com:9243"
user => "elastic"
password => "xxxxx"
index => "xxxx"
action => "%{[@metadata][elasticsearch_action]}"
document_type => "_doc"
document_id => "%{claimnumber}"

}
stdout { codec => rubydebug }
}

Attached screenshot for reference

Date format seems to be wrong due to that each time it is picking all the documents instead of modified one refer attached screenshot for more clarity. Should some one provide insight on this issue?

[Badger]

SQL Server supports ISO 8601 date formats. What makes you think this is not working?

[Mohan_vel]

Hi @Badger
I have upgraded my logstash version from 7.2.0 to 7.5.2
In my SQL table last updated value is highlighted below.

when i was checking in .jdbc_last_run file after running logstash, modified_date column value is seems to be irrelevant one attached screenshot for your reference

Date has updated correctly but time looks like Evening 5 PM 2020-02-04 17:24:08.165000000 Z but actually it should be like this 2020-02-04 22:44:29.673000000 Z

i was unable to find the reason why time getting updated wrongly. In my previous version 7.2.0 it was working fine once i upgrade this to latest version this problem got arised due to this i was unable to perform incremental indexing.

Config file has already given. Kindly suggest how to resolve this issue?

[shekarkola]

@Mohan_vel

Is the variance between your source data (in sql table) and the sql_last_value reflects same as the variance between your timezone and the UTC?

if so, as workaround you can consider that within statement. or you may use the additional parameter jdbc_default_timezone

[Mohan_vel]

Hi @shekarkola

As suggested i have added default timezone it's working fine. Thanks a lot config file below for your reference.

input {
jdbc {
jdbc_driver_library => ""
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxxx"
jdbc_password => "xxx"
jdbc_paging_enabled => true
tracking_column => modified_date
use_column_value => true
clean_run => true
tracking_column_type => "timestamp"
schedule => "*/1 * * * *"
statement => "Select* from claim where modified_date >:sql_last_value"
last_run_metadata_path => "D:\Users\xxxx\Desktop\logstash-7.2.0\jdbc_lastrun\jdbc_last_run.txt"
jdbc_default_timezone => "UTC" 
}
}
filter {
mutate {
   remove_field => ["@version","@timestamp"]
 }
}
output {

stdout { codec => rubydebug }
}