JDBC Importer for Logstash is adding timezone to date time field


(Shantanu Sen) #1

Hello
I am using jdbc importer to extract data from SQL Server to ElasticSearch. I am sharing the structure and i got configuration OK with logstash configtest.

Now i am getting an weird issue there is an UsageDate field in my sql query and when I run the sql query on query analyzer i get proper date format as 2015-05-28 20:00:00.000 now when i upload the data using logstash it is converting the date to 2015-05-28 20:00:00 -0600.

I am quite confused from where -0600 is concatenating. Also due to the issue when i map the index in kibana it is showing UsageDate as String. Please help i am stuck

input {
jdbc {
jdbc_connection_string => "jdbc:sqlserver://XXXXXXXXXXX:1433;databaseName=XXXX"
jdbc_user => "XXXXX"
jdbc_password => "XXXXXXXX"
jdbc_validate_connection => true
jdbc_driver_library => "XXXXXXXXXXX/sqljdbc4-3.0.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.sqlserverdriver"
schedule => "0 * * * *"
statement => "declare @date datetime; declare @startdate datetime; declare @enddate datetime; set @date=getdate(); set @enddate = DATEADD(hh, DATEPART(HOUR, @date),Cast(Min(CONVERT(date, @date))as Datetime)); set @startdate=DATEADD(hh, -1, @enddate); Select DATEADD(hh, DATEPART(HOUR, @startdate), Cast(Min(CONVERT(date, @startdate))as Datetime)) UsageDate,'Tutoring' Module,'NoOfAvailablity' ModuleKey, (select Count(distinct timeslotid) UsageValue from [Tutoring].[tblAppointmentExtendedProperties] with (nolock) where OccurenceStartDate between @startdate and @enddate)"
}
}
output {
elasticsearch {
protocol => http
index => "epssql"
document_type => "epssql"
host => "XXXXXXXXXXXX"
port => "9200"
}
stdout { codec => rubydebug }
}


(Krushnat Khawale) #2

Yes, I'm haveing same issue, Has anyone here fixed this?


(Shantanu Sen) #3

Any Pointers !! Please Assist


#4

Hello,

Having field in UTC in Kibana is mandatory, the issue is how the jdbc input is interpreting the date field when reading from the database and converting for Kibana.
your issue seems similar to the reported issue on the plugin https://github.com/logstash-plugins/logstash-input-jdbc/issues/89

where the database is using UTC but the logstash agent interpret is in a different timezone and reconvert it to UTC causing the timelapse.

Updating the plugin and configuring the timezone to use when reading from the database should help you in resolving this I hope.


(system) #5