Orcale (XMLTYPE) attribute not indexing in elasticsearch, logstash filter not working


(Shamim Ahmad) #1

I have been trying to parse and index this (XMLTYPE) attribute (ilmd) which I am getting from a Oracle DB when I fire this query,
SELECT event_event_dt, event_event_tm, ilmd FROM event

Here is the xml document which is stored in DB at ilmd :

                    <ilmd><e:expirationDate xmlns:e="http://epcis.myComp.com/extension" type="xs:date">2290-04-17</e:expirationDate>
                    <e:lotNumber xmlns:e="http://epcis.myComp.com/extension">LOT-100000</e:lotNumber>
                    <e:quantity1 xmlns:e="http://epcis.myComp.com/extension" type="xs:integer">100000</e:quantity1>
                    <e:productionDate xmlns:e="http://epcis.myComp.com/extension" type="xs:dateTime">2016-10-11T13:55:10.000000010</e:productionDate>
                    <e:EXT1 xmlns:e="http://epcis.myComp.com/extension">GHI-38</e:EXT1>
                    <e:EXT13 xmlns:e="http://epcis.myComp.com/extension">
                        <e:EXT xmlns:e="http://epcis.myComp.extension/sublabel">GHI-52</e:EXT>
                    </e:EXT13>
                </ilmd>

This is logstash config file :

input {
    jdbc {
        jdbc_validate_connection => true
        jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521/orcl"
        jdbc_user => "abc"
        jdbc_password => "abc"
        jdbc_driver_library => "/home/myname/ES/ojdbc6-11.2.0.4.0.jar"
        jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
        statement => "SELECT event_event_dt, event_event_tm, ilmd FROM event"
	#statement_filepath => "query.sql"
	#last_run_metadata_path => "/tmp/logstash-oradb.lastrun"
        #record_last_run => true
	#schedule => "*/1 * * * *"
	#jdbc_paging_enabled => "true"
	#jdbc_page_size => "20"
       }
}
filter {
    mutate { convert => [ "sample_time" , "string" ]}
    date { match => ["sample_time", "ISO8601"]}
   
    xml {
     source => "%{ilmd}"
     store_xml => false
     target => "xmldata"
     xpath => ["/ilmd","ILMD"]
     xpath => ["/ilmd/e:EXT13[@xmlns:e="http://epcis.mycomp.com/extension"]/e:EXT[@xmlns:e="http://epcis.mycomp.extension/sublabel"]","EXT13"]
     xpath => ["/ilmd/e:EXT13[@xmlns:e="http://epcis.mycomp.com/extension"]","EXT13"]
     xpath => ["/ilmd/e:EXT1[@xmlns:e="http://epcis.mycomp.com/extension"]","EXT1"]
     xpath => ["/ilmd/e:productionDate[@xmlns:e="http://epcis.mycomp.com/extension"]","productionDate"]
     xpath => ["/ilmd/e:quantity1[@xmlns:e="http://epcis.mycomp.com/extension"]","quantity1"]
     xpath => ["/ilmd/e:lotNumber[@xmlns:e="http://epcis.mycomp.com/extension"]","lotNumber"]
     xpath => ["/ilmd/e:expirationDate[@xmlns:e="http://epcis.mycomp.com/extension"]","expirationDate"]
     }
    }
}
output   {
    elasticsearch {
      index => "event"
      document_type => "event_data"
      workers => 2
    }
}

Please help me on this, I am really desperate to get the data indexed in ElasticSearch.


(Magnus B├Ąck) #2
 source => "%{ilmd}"

The source option should contain the name of the field, not the contents of it. Change to:

 source => "ilmd"

(system) #3

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