Logstash MSSQL JDBC connection troubleshooting

Elastic Stack Logstash
1

Hello, sorry to bother you, once again I have a question about Logstash input...
I just got back from a 9month medical leave, so im a bit lost i must admit...

anyway, heres the config :

stripped input and output to keep only the jdbc part ( that is not working )

jdbc {
      jdbc_driver_library => "/var/lib/logstash/sqljdbc42.jar"
      jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
      jdbc_connection_string => "jdbc:sqlserver://SERVER_IP_ADDRESS:1433;databaseName=DATABASE_NAME"
      jdbc_user => "******"
      jdbc_password => "******"
      # or jdbc_password_filepath => "/path/to/my/password_file"
      jdbc_validate_connection => true
      statement => "SELECT TBL_VIRUS_LOG.FOUND_DATE, TBL_VIRUS_LOG.VIRUSNAME, TBL_VIRUS_LOG.LOGIN, TBL_VIRUS_LOG.INFECTFILE, TBL_VIRUS_LOG.FILEDIR, TBL_VIRUS_LOG.INFECT_SRC, TBL_CLIENT_INFO.COMP_NAME, TBL_CLIENT_INFO.IP, TBL_CLIENT_INFO.DOMAIN, TBL_CLIENT_INFO.MAC, TBL_CLIENT_INFO.LOGON_USER FROM TBL_VIRUS_LOG INNER JOIN TBL_CLIENT_INFO ON TBL_VIRUS_LOG.UID = TBL_CLIENT_INFO.UID WHERE TBL_CLIENT_INFO.UID > :sql_last_value"
      # or statement_filepath => "query.sql"
      use_column_value => true
      tracking_column => "TBL_CLIENT_INFO.UID"
      clean_run => false
      record_last_run => true
      last_run_metadata_path => "/var/lib/logstash/.logstash_jdbc_last_run_tblviruslog"
      #tracking_column_type => "numeric"
      jdbc_paging_enabled => "true"
      jdbc_page_size => "50000"
      type => "trendmicro-tblviruslog"
      tags => "TBL_VLOG"
}


if "TBL_VLOG" in [tags] {
  elasticsearch {
       hosts => ["es-client:9200"]
       index => "logstash-trendmicro-%{+YYYY.MM.dd}"
       document_type => "tm11-tblviruslog"
  }
}

The goal here, is to index the virus alert on my trendmicro database ( Microsoft SQL Server 2012 ) without duplicate and such so i can do a "cute graphic for the boss"

anyway ... It is not working, and by not working i mean, Logstash simply wont run after that addition.

heres the log :

18/08/2017 14:47:57Pipeline main started
18/08/2017 14:48:00Java::ComMicrosoftSqlserverJdbc::SQLServerException: Operand type clash: uniqueidentifier is incompatible with smallint: SELECT TOP (1) count(*) AS [COUNT] FROM (SELECT TBL_VIRUS_LOG.FOUND_DATE, TBL_VIRUS_LOG.VIRUSNAME, TBL_VIRUS_LOG.LOGIN, TBL_VIRUS_LOG.INFECTFILE, TBL_VIRUS_LOG.FILEDIR, TBL_VIRUS_LOG.INFECT_SRC, TBL_CLIENT_INFO.COMP_NAME, TBL_CLIENT_INFO.IP, TBL_CLIENT_INFO.DOMAIN, TBL_CLIENT_INFO.MAC, TBL_CLIENT_INFO.LOGON_USER FROM TBL_VIRUS_LOG INNER JOIN TBL_CLIENT_INFO ON TBL_VIRUS_LOG.UID = TBL_CLIENT_INFO.UID WHERE TBL_CLIENT_INFO.UID > 8261) AS [T1] {:level=>:error}
18/08/2017 14:48:00Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerException: Operand type clash: uniqueidentifier is incompatible with smallint>, :level=>:warn}
18/08/2017 14:49:00Java::ComMicrosoftSqlserverJdbc::SQLServerException: Operand type clash: uniqueidentifier is incompatible with smallint: SELECT TOP (1) count(*) AS [COUNT] FROM (SELECT TBL_VIRUS_LOG.FOUND_DATE, TBL_VIRUS_LOG.VIRUSNAME, TBL_VIRUS_LOG.LOGIN, TBL_VIRUS_LOG.INFECTFILE, TBL_VIRUS_LOG.FILEDIR, TBL_VIRUS_LOG.INFECT_SRC, TBL_CLIENT_INFO.COMP_NAME, TBL_CLIENT_INFO.IP, TBL_CLIENT_INFO.DOMAIN, TBL_CLIENT_INFO.MAC, TBL_CLIENT_INFO.LOGON_USER FROM TBL_VIRUS_LOG INNER JOIN TBL_CLIENT_INFO ON TBL_VIRUS_LOG.UID = TBL_CLIENT_INFO.UID WHERE TBL_CLIENT_INFO.UID > 8261) AS [T1] {:level=>:error}
18/08/2017 14:49:00Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerException: Operand type clash: uniqueidentifier is incompatible with smallint>, :level=>:warn}

Thank you for future reply ! deeply appreciated as usual!

2

please.. little help here ?

3

no body to help ? no body ever experienced such behavior

23/08/2017 12:58:00tracking_column not found in dataset. {:tracking_column=>"ID", :level=>:warn}
23/08/2017 12:59:00tracking_column not found in dataset. {:tracking_column=>"ID", :level=>:warn}
23/08/2017 13:00:00tracking_column not found in dataset. {:tracking_column=>"ID", :level=>:warn}

4

41 view, not a single reply ?
please. I need help I wouldnt ask if that was not the case...

5

up post

6

Before you write the rest of the query, you should first get a few records (using TOP N) and output to stdout with rubydebug. Then you can look at the record as the JDBC Input would see it.

If you get errors about missing BiValue then you have a exotic datatype in one/some of the columns in the result. You will need to CAST these to types that are compatible with JSON: int, float, string or boolean as they will need to be serialized during indexing.

Look specifically at the field names and the values. Notice whether the field names are <table>.<column> or <column> and whether they are uppercase or lowercase.
Look for the field name that you want to be the tracking column - if its not there then you need to add it to the select clause of the query statement. You may need to AS alias some columns in the query to get better field names for display in Kibana.

Now look at the values, especially the value of the of the field you intend to use as the tracking column. Notice if it is numeric or text. Most people use a WHERE column > :last_sql_value clause. Ask yourself if your intended query is returning result in ascending order on the intended tracking column column. Decide whether you need to CAST this in the eventual query.

With all this preparation you should be able to build a query statement that works.

3 Likes
7

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