Hello everybody!
Help me please with @timestamp, i have jdbc input with mssql server, and in my output i have variable - datetime, which include time of created table. How i can use it instead @timestamp?
this is my conf file:
input {
jdbc {
jdbc_driver_library => "/etc/logstash/drivers/sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://server:1433;databasename=db"
jdbc_user => "login"
jdbc_password => "pass"
statement => "SELECT m.*
, mc.nameRu AS CategoryNameRu
, mt.nameRu AS TypeNameRu
, ms.nameRu AS SourceNameRu
, p.Fio_Ru
, s.RowName AS LoginName
, pos.FullNameRu AS PositionNameRu
FROM LOG_Messages m
JOIN DIC_LOG_MESSAGE_SOURCE_TO_TYPE mst ON mst.id = m.refMessageSourceType
JOIN DIC_LOG_MESSAGE_CATEGORY mc ON mc.id = mst.refMessageCategory
JOIN DIC_LOG_MESSAGE_TYPE mt ON mt.id = mst.refMessageType
JOIN DIC_LOG_MESSAGE_SOURCE ms ON ms.id = mst.refMessageSource
LEFT JOIN ULS_Persons p ON p.id = m.refRecordCard
LEFT JOIN LOG_SidIdentification s ON s.id = m.refSid
LEFT JOIN ULS_SubdivisionPositions pos ON pos.id = m.refPosition
ORDER BY id DESC"
}
}
filter {
mutate {
add_field => { "message" => "%{typenameru}" }
convert => [ "datetime", "string" ]
copy => { "datetime" => "@timestamp" }
}
}
output {
gelf{
host => "0.0.0.0"
port => 12231
short_message => 'short_message'
}
stdout { codec => rubydebug}
}
You can use the plug-in "date" inside your filter. When you use it to parse your custom date field, it automatically generates the @timestamp field for your events. You don't need to add, convert or copy your original field like the example you posted.
Hi Anuar,
I have the same problem with Cassandra:
this is the query in statement:
statement => "select * from mydatabase.products_to_elastic WHERE lastmodified > :sql_last_value"
and I obtain this error:
[2018-01-30T12:01:00,702][ERROR][logstash.inputs.jdbc ] Java::ComDatastaxDriverCoreExceptions::InvalidQueryException: Unable to coerce '2018-01-30 11:59:02.709784' to a formatted date (long): select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709784' ALLOW FILTERING
I used this filter:
filter {
date {
match => [ "lastmodified", "yyyy-MM-dd HH:mm:ss.SSS" ]
}
}
but I don't understand if the filter works with input or only with data in output.
I assume your original field looks like this: 2018-01-29 22:16:59.537
The date filter takes in consideration your platform locale. If you want to see the events as it comes, you can use the time zone setting. And you don't need to set the target to the @timestamp. This is the plugin default.
filter {
date {
match => [ "message", "YYYY-MM-dd HH:mm:ss.SSS" ] #2018-01-29 22:16:59.537
timezone => "Etc/UTC"
}
}
You have to decide if you want to leave this config as is or not, depending on how and where you are visualizing your data. For me, Kibana sets the timestamp on screen by the users browser's locale, so I never set the time zone on Logstash.
I check logstash conf file with debug and i saw that, i have two variables with time, first datetime - it is time from DB and second @timestamp. Can i replase timestamp value with datetime values?
I didn't quite understand your question. Isn't it what we have been doing on the messages above?
All the examples were already given. What is exactly the problem?
When you use the date filter plugin you have to give it the field where your date is, so you can parse it. Logstash will automatically put the parsed date on the @timestamp field. You can use target if you want to send it to another field though, that is optional.
After you have done that, if you don't need your "datetime" field anymore, (because you send it parsed to the @timestamp field), you can remove it.
Would you mind opening a new thread for your issue? It is important to post also a sample of the data you are trying to process in Logstash. And check the answer for Anuar. It might work for you too. If not, open a new thread please.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.