Logstash mapping sql xml column to elasticsearch index properties

Elastic Stack Logstash
1

Im creating "my_events" index in elasticsearch

>     PUT http://localhost:9200/my_events
>     {
>     	 "settings" : {
>             "number_of_shards" : 1
>         },
>         "mappings" : {
>             	"properties" : {
>     	            "event_id" : { "type" : "integer" },
>     	            "event_type" : { "type" : "text" },
>     	            "event_date" : { "type" : "date" },
>     	            "case_number" : { "type" : "text" },
>     	            "form_name" : { "type" : "text" },	            
>     	            "user" : { "type" : "text" }
>             	}
>         }
>     }

my logstash.conf file

input {
      jdbc {
          ..... settings
         statement => "select event_id, event_xml from [dbo].[Event]"
    }
}
filter {  
  xml {   
    namespaces => {
      "xsl" => "http://www.w3.org/1999/XSL/Transform"
      "xhtml" => "http://www.w3.org/1999/xhtml"
      "xml" => "http://www.w3.org/2001/XMLSchema"
      "xmli" => "http://www.w3.org/2001/XMLSchema-instance"
    }     
    source => "event_xml"              
    store_xml => "false"
    xpath => [ "Event/EventType/text()", "event_type"]         
    xpath => [ "Event/EventDate/text()", "event_date" ]
    xpath => [ "Event/CaseNumber/text()", "case_number" ]
    xpath => [ "Event/FormName/text()", "form_name" ]
    xpath => [ "Event/User/text()", "user" ]
  }
}

output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "my_events"

  }
}

when i run the cmd to get the data to elastic search my index shows the documents as the event_xml as a string, how do I break up the xpaths as part of the document

below is document

    {
        "_index": "my_events",
        "_type": "_doc",
        "_id": "k7V6p24BCHghMWQk9gBH",
        "_version": 1,
        "_seq_no": 0,
        "_primary_term": 1,
        "found": true,
        "_source": {
            "@version": "1",
            "event_xml": "<.... XML ......>",
            "@timestamp": "2019-11-26T11:31:22.726Z",
            "event_id": 2007
        }
    }

<Event xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://vsin-mon/NoteEvent/v1/schemas">
  <EventType xmlns="">RECEIVE_DOCUMENT</EventType>
  <EventDate xmlns="">07/04/2019 09:41:49</EventDate>
  <CaseNumber xmlns="">5Test</CaseNumber>
  <FormName xmlns="">P5000</FormName>
  <User xmlns="">System</User>
</Event>
2

It sounds like your xpath references are wrong, but without seeing the value of [event_xml] we are not going to be able to suggest improvements.

3

I have added the xml

4

adding remove_namespaces => true to xml filter mapped the properties

5

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