Jdbc input logstash


(hudojnick) #1

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

(Jochen) #2

You might want to post your question to the Logstash discussion forums:


(hudojnick) #3

No, i use logstash with graylog)


(Jochen) #4

Until now I haven’t seen Graylog-specific in your first post.

Please elaborate on what you want to achieve.


(hudojnick) #5

Can i change timestamp in graylog with other datetime?


(Jochen) #6

Yes, that’s possible.


(hudojnick) #7

And, how i can do it?


(Jochen) #8

You can replace the “timestamp” field with another valid DateTime instance.

http://docs.graylog.org/en/2.4/pages/pipelines/functions.html#parse-date


(hudojnick) #9

Ok, can are you please help me and check my pipeline? I have field “datetime”, but i want replace in @timestamp field my value.

rule "datetime"
when
    has_field("datetime")
then
    let new_date = parse_date(to_string($message.datetime), "yyyy-MM-dd HH:mm:ss.SSS");
    set_field("@timestamp", new_date.year);
end

this is from my stream


(Jan Doberstein) #10

why not

rule "datetime"
when
    has_field("datetime")
then
   // parse_date default to UTC if no timezone is given, would need the information what
   // timezone the timestamp is located in if not UTC
    let new_date = parse_date(to_string($message.datetime), "yyyy-MM-dd HH:mm:ss.SSS");
    set_field("timestamp", new_date);
end

Or really simple (if no parsing is needed)

why not

rule "datetime"
when
    has_field("datetime")
then
    set_field("timestamp", to_string($message.datetime);
end

(hudojnick) #11

I don’t know why, but it is didn’t work…can are you help me please and can we chating in some messenger, like skype or telegram?)) My Skype - anuar_mukatov


(hudojnick) #12

And when i try simulate it, i haven’t changes…and i have this message in right box - Original message would be not be modified during processing. Does it matter that I use Gelf input?


(Jochen) #13

Because that doesn’t work since the “timestamp” field has to be a proper DateTime object. :wink:


(Jochen) #14

Graylog is using the “timestamp” field as the message timestamp. You’re trying to set the “@timestamp” field which isn’t used by Graylog.


(system) #15

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