Logstash jdbc_streaming, sql statement

Elastic Stack Logstash
1

hi all,
I am using following logstash.conf file:

input {
jdbc {
    jdbc_driver_library => "F:\driver\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://local:3333;databaseName=TotalTXN"
    jdbc_user => "sss"
    jdbc_password => "sss"
    statement => "
	DECLARE @DDate1  CHAR(10)
   select @DDate1=REPLACE(MAX(Date),'/','') from TotalTXN.dbo.TotalTxn_Control
   select TOP 10 [BaseDate_Accept_Setel]
  ,[Financial_Date]
     from dbo.vw_TotalTXN where (BaseDate_Accept_Setel<=@DDate1) AND (BaseDate_Accept_Setel> :sql_last_value)
	"
use_column_value => "true"
tracking_column => "basedate_accept_setel"
}

}
filter {
         
jdbc_streaming {
 jdbc_driver_library => "F:\driver\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://local:3333;databaseName=TotalTXN"
    jdbc_user => "sss"
    jdbc_password => "sss"
    statement => "
	DECLARE @DDate2  CHAR(10)
	set @DDate2='%{Financial_Date}'
SELECT TotalTXN.dbo.d2md(@DDate2) as gdate"
target => "gdate"
}
mutate { replace => { "gdate" => "%{[gdate][0][gdate]}" } }
    mutate { gsub => [ "gdate", "/", "-" ] }
}

output {
  elasticsearch { 
    hosts => ["http://192.168.170.153:9200"]
    index => "a10_%{gdate}"
    user => "logstash_internal25"
    password => "x-pack-test-password"
 }
  stdout { codec => rubydebug }
}

in the jdbc_streaming filter, I want to set the value of @DDate2 as the amount of "Financial_Date" field. when i used following statement, it works and is ok,

DECLARE @DDate2  CHAR(10)
	set @DDate2='2019/02/24'
SELECT TotalTXN_OnLine.dbo.shd2md(@DDate2) as gdate

I want to set @DDate based on the value of "Financial_Date",but (set @DDate2='%{Financial_Date}') doesn't work, how can i handle this issue? any advice will be so appreciated. many thanks.

2

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