Jdbc_static and mssql server

Elastic Stack Logstash
1

Hi!
I trying to get some fields through jdbc_static:

if "grokked" not in [tags] {
  grok { 
    match => [ "message", "{%{DATESTAMP_EVENTLOG:Date},%{INT:ID}:%{GREEDYDATA:GUID} ]
    add_tag => [ "grokked"]
    tag_on_failure => ["3_failure"]
  }
  mutate {
    replace => {"GUID" => "0x%{GUID}"}
    }
  if [ID] ==  "1142" {
   jdbc_static {
     loaders => [ 
     {
       id => "1142"
       query => "SELECT _IDRRef,_Fld25177 FROM dbo._Document1142"
       local_table => "1142"
     }
     ]        
     local_db_objects => [ 
     {
      name => "1142"
      index_columns => ["_IDRRef"]
      columns => [
        ["_IDRRef", "BINARY(16)"],
        ["_Fld25177", "NVARCHAR(211)"]
        ]
     }
     ]
     local_lookups => [ 
      {
       id => "1142"
       query => "select _Fld25177 as doc from 1142 WHERE _IDRRef = :GUID_sql"
       parameters => {GUID_sql => "[GUID]"}
       target => "sql"
      }
      ]
      staging_directory => "c:\elk\data\import_data"		  
      loader_schedule => "*/30 * * * *"
      jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
      jdbc_driver_library =>  "c:\elk\jdbc\sqljdbc_7.2\rus\mssql-jdbc-7.2.2.jre8.jar"
      jdbc_connection_string => "jdbc:sqlserver://db2:1433;databaseName=s2019;integratedSecurity=true;user=login;password=RRRRRRRRRR"
    }
   }

and I geting error

[ERROR][logstash.filters.jdbc.readwritedatabase] Exception when initialising lookup db for db object
.....
Syntax error: Encountered \"(\" at line 1, column 38."......

also I tried to use jdbc 4.2 6.2, playaround with jdbc_user/password, change data type.
When I was change BINARY(16) to varchar file c:\elk\data\import_data\1142 was appeared with data from mssql and logstash truncation error:

CAST(X'80b5005056bda97911e7c54a20fff4b0' AS BLOB), 'target data'

so wat is wrong?

2

Binary type is not supported(
I was change binary(16) to varchar(64) and it works well

3

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