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 }
}