Unable to change format of sql_last_value

Elastic Stack Logstash
1

Hi,
I am using sql_last_value for incremented record using jdbc input plugin.but it is giving value like:

TIMESTAMP '2017-09-13 12:44:27.266000 +00:00'

I wanna remove Timestamp from this how can i do this please suggest proper solution.

code which i used:

input {
jdbc {
jdbc_validate_connection => true
jdbc_connection_string => "jdbc:oracle:thin:@MUMCHORA66.ad.crisil.com:1821/COA"
jdbc_user => "test"
jdbc_password => "test"
jdbc_driver_library => "/opt/logstash-5.5.0/ojdbc7.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
schedule => "* * * * *"

    statement => "SELECT  chd.RECORD_TYPE,chd.COALITION_ID,chd.COALITION_NAME,chd.BANK_ID,chd.TIMEPERIOD_ID,chd.BANK_CLIENT_ENTITY_NAME,chd.BANK_CLIENT_ENTITY_ID,

chd.BANK_CLIENT_ENTITY_COUNTRY,chd.BANK_CLIENT_ENTITY_REGION,chd.REGION_ID,chd.PRODUCT_ID,
chd.BANK_CLIENT_ENTITY_SECTOR,chd.PARENT1_ENTITY_NAME,chd.PARENT1_CLIENT_ENTITY_ID,chd.PARENT1_ENTITY_COUNTRY
,chd.PARENT1_ENTITY_REGION,chd.PARENT1_ENTITY_SECTOR,chd.PARENT2_ENTITY_NAME,chd.PARENT2_CLIENT_ENTITY_ID,chd.PARENT2_ENTITY_COUNTRY,chd.PARENT2_ENTITY_REGION
,chd.PARENT2_ENTITY_SECTOR,chd.PARENT3_ENTITY_NAME,chd.PARENT3_CLIENT_ENTITY_ID,chd.PARENT3_ENTITY_COUNTRY,chd.PARENT3_ENTITY_REGION,chd.PARENT3_ENTITY_SECTOR
,chd.PARENT4_ENTITY_NAME,chd.PARENT4_CLIENT_ENTITY_ID,chd.PARENT4_ENTITY_COUNTRY,chd.PARENT4_ENTITY_REGION,chd.PARENT4_ENTITY_SECTOR,chd.PARENT5_ENTITY_NAME
,chd.PARENT5_CLIENT_ENTITY_ID,chd.PARENT5_ENTITY_COUNTRY,chd.PARENT5_ENTITY_REGION,chd.PARENT5_ENTITY_SECTOR,chd.CIQ_NAME,chd.CIQ_ID,chd.CIQ_STATUS
,chd.ULTIMATE_PARENT,ULTIMATE_ID,chd.HDID,chd.ID,chd.cbcdid,chd.LISTSOURCE,chd.COMPANYCOUNTRY,chd.ULTIMATEPERCENT,chd.RA_COMMENTS,chd.RA_SOURCE,chd.QC_COMMENTS,chd.QC_SOURCE,
(select cd.client from clients_dim cd where chd.bank_id=cd.client_id) as BANK_NAME,
(select ts.name from timeperiod_syn ts where ts.id=chd.TIMEPERIOD_ID ) as PERIOD_NAME,
chd.USER_CONFIRMATION as USER_CONFIRMATION,chd.USER_VERIFIED_DATE as USER_VERIFIED_DATE ,
to_date(TO_CHAR(
case when chd.user_verified_date is not null then chd.user_verified_date
when chd.modified_date is not null then chd.modified_date
when chd.insert_date is not null then chd.insert_date end,'DD-MON-YYYY hh:mi:ss'),'DD-MON-YYYY hh:mi:ss') as Date_Status
FROM CEM_HISTORICAL_METADATA chd
where chd.active= 1 and NVL(modified_date, insert_date) >
to_timestamp(REPLACE(REPLACE(REPLACE(:sql_last_value, 'TIMESTAMP ''', ''), '+00:00', ''),'''',''),'yyyy-Mm-DD HH24:MI:SS.FF')" }
}

output {

elasticsearch {
	hosts => "test"
index => "hist_latest_5.5.0"        
}

}

2

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