Get json output from xml file input in logstash

Elastic Stack Logstash
1

Hi Everyone,

i want to convert xml input to json data. The xml format is shown below

<PurchaseOrders>  
  <PurchaseOrder PurchaseOrderNumber="99503" OrderDate="1999-10-20">  
    <Address Type="Shipping">  
      <Name>Ellen Adams</Name>  
      <Street>123 Maple Street</Street>  
      <City>Mill Valley</City>  
      <State>CA</State>  
      <Zip>10999</Zip>  
      <Country>USA</Country>  
    </Address>  
    <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>  
    <Items>  
      <Item PartNumber="872-AA">  
        <ProductName>Lawnmower</ProductName>  
        <Quantity>1</Quantity>  
        <USPrice>148.95</USPrice>  
        <Comment>Confirm this is electric</Comment>  
      </Item>  
      <Item PartNumber="926-AA">  
        <ProductName>Baby Monitor</ProductName>  
        <Quantity>2</Quantity>  
        <USPrice>39.98</USPrice>  
        <ShipDate>1999-05-21</ShipDate>  
      </Item>  
    </Items>  
  </PurchaseOrder>  
  <PurchaseOrder PurchaseOrderNumber="99505" OrderDate="1999-10-22">  
    <Address Type="Shipping">  
      <Name>Cristian Osorio</Name>  
      <Street>456 Main Street</Street>  
      <City>Buffalo</City>  
      <State>NY</State>  
      <Zip>98112</Zip>  
      <Country>USA</Country>  
    </Address>  
    <Address Type="Billing">  
      <Name>Cristian Osorio</Name>  
      <Street>456 Main Street</Street>  
      <City>Buffalo</City>  
      <State>NY</State>  
      <Zip>98112</Zip>  
      <Country>USA</Country>  
    </Address>  
    <DeliveryNotes>Please notify me before shipping.</DeliveryNotes>  
    <Items>  
      <Item PartNumber="456-NM">  
        <ProductName>Power Supply</ProductName>  
        <Quantity>1</Quantity>  
        <USPrice>45.99</USPrice>  
      </Item>  
    </Items>  
  </PurchaseOrder>  
  <PurchaseOrder PurchaseOrderNumber="99504" OrderDate="1999-10-22">  
    <Address Type="Shipping">  
      <Name>Jessica Arnold</Name>  
      <Street>4055 Madison Ave</Street>  
      <City>Seattle</City>  
      <State>WA</State>  
      <Zip>98112</Zip>  
      <Country>USA</Country>  
    </Address>  
    <Address Type="Billing">  
      <Name>Jessica Arnold</Name>  
      <Street>4055 Madison Ave</Street>  
      <City>Buffalo</City>  
      <State>NY</State>  
      <Zip>98112</Zip>  
      <Country>USA</Country>  
    </Address>  
    <Items>  
      <Item PartNumber="898-AZ">  
        <ProductName>Computer Keyboard</ProductName>  
        <Quantity>1</Quantity>  
        <USPrice>29.99</USPrice>  
      </Item>  
      <Item PartNumber="898-AM">  
        <ProductName>Wireless Mouse</ProductName>  
        <Quantity>1</Quantity>  
        <USPrice>14.99</USPrice>  
      </Item>  
    </Items>  
  </PurchaseOrder>  
</PurchaseOrders>

i want json output as

  • [ { "@PurchaseOrderNumber": "99503", "@OrderDate": "1999-10-20", "Address": [ { "Name": "Ellen Adams", "Street": "123 Maple Street", "City": "Mill Valley", "State": "CA", "Zip": "10999", "Country": "USA" }, { "Name": "Tai Yee", "Street": "8 Oak Avenue", "City": "Old Town", "State": "PA", "Zip": "95819", "Country": "USA" } ], "DeliveryNotes": "Please leave packages in shed by driveway.", "Items": [ { "@PartNumber": "872-AA", "ProductName": "Lawnmower", "Quantity": "1", "USPrice": "148.95", "Comment": "Confirm this is electric" }, { "@PartNumber": "926-AA", "ProductName": "Baby Monitor", "Quantity": "2", "USPrice": "39.98", "ShipDate": "1999-05-21" } ] }, { "@PurchaseOrderNumber": "99505", "@OrderDate": "1999-10-22", "Address": [ { "Name": "Cristian Osorio", "Street": "456 Main Street", "City": "Buffalo", "State": "NY", "Zip": "98112", "Country": "USA" }, { "Name": "Cristian Osorio", "Street": "456 Main Street", "City": "Buffalo", "State": "NY", "Zip": "98112", "Country": "USA" } ], "DeliveryNotes": "Please notify me before shipping.", "Items": { "Item": { "@PartNumber": "456-NM", "ProductName": "Power Supply", "Quantity": "1", "USPrice": "45.99" } } }, { "@PurchaseOrderNumber": "99504", "@OrderDate": "1999-10-22", "Address": [ { "Name": "Jessica Arnold", "Street": "4055 Madison Ave", "City": "Seattle", "State": "WA", "Zip": "98112", "Country": "USA" }, { "Name": "Jessica Arnold", "Street": "4055 Madison Ave", "City": "Buffalo", "State": "NY", "Zip": "98112", "Country": "USA" } ], "Items": [ { "@PartNumber": "898-AZ", "ProductName": "Computer Keyboard", "Quantity": "1", "USPrice": "29.99" }, { "@PartNumber": "898-AM", "ProductName": "Wireless Mouse", "Quantity": "1", "USPrice": "14.99" } ] } ]

my conf file

> input {
>  beats {
>     port => 5049
> 	codec => multiline {
>       pattern => "^\MetasploitV5"
> 	  }
>   }
>  }
> filter
> {
> 
> xml {
> source => "message"
> target => "json"
> }
> }
> output{
> stdout{}
> file {
>         path => "E:/xmltest.csv"
>  codec => line { format => ["%{json},%{message}"] } 	
> 	}
> }

`

but iam not getting correct output that xml is getting splited by every row like

json":"124","offset":173,"prospector":{"type":"log"},"message":" 124"

"json":"2019-01-28 13:23:12 UTC","offset":190,"prospector":{"type":"log"},"message":" 2019-01-28 13:23:12 UTC"

but i want whole xml in one message as json. please help me to solve this issue :confused:

2

Hi,

From your example I got the following output (the output generates more then 7000 caracters, the limit to post here is 7000), the result is nested json inside PurchaseOrder

The config I used it this:

input {
    stdin {}
}
filter {
    xml {
      source => "message"
      target => "doc"
    }
}
output {
  stdout {
    codec => rubydebug
  }
}
3

i think we need to use multiline to solve this

4

The example xml you posted I modified it to be on 1 single line. You need to check your input to see how it looks. I think there are several way to do that.

  1. change the input to beat, remove all multiline stuff and see what happens.
  2. tcpdump the beats port and look at the raw data.

Once you know how the data looks you can create a multiline filter and process it.

5

yeah but i need that output in file. i got every xml line as one message. but i need whole xml file input as single message

6

Hi manasapp,

with the input I cannot help you as I do not know how that looks. All I have is your example and like I said I modified that to reflect 1 single line like this:

<PurchaseOrders><PurchaseOrder PurchaseOrderNumber="99503" OrderDate="1999-10-20"><Address Type="Shipping"><Name>Ellen Adams</Name><Street>123 Maple Street</Street><City>Mill Valley</City><State>CA</State><Zip>10999</Zip><Country>USA</Country></Address><DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><Items><Item PartNumber="872-AA"><ProductName>Lawnmower</ProductName><Quantity>1</Quantity><USPrice>148.95</USPrice><Comment>Confirm this is electric</Comment></Item><Item PartNumber="926-AA"><ProductName>Baby Monitor</ProductName><Quantity>2</Quantity><USPrice>39.98</USPrice><ShipDate>1999-05-21</ShipDate></Item></Items></PurchaseOrder><PurchaseOrder PurchaseOrderNumber="99505" OrderDate="1999-10-22"><Address Type="Shipping"><Name>Cristian Osorio</Name><Street>456 Main Street</Street><City>Buffalo</City><State>NY</State><Zip>98112</Zip><Country>USA</Country></Address><Address Type="Billing"><Name>Cristian Osorio</Name><Street>456 Main Street</Street><City>Buffalo</City><State>NY</State><Zip>98112</Zip><Country>USA</Country></Address><DeliveryNotes>Please notify me before shipping.</DeliveryNotes><Items><Item PartNumber="456-NM"><ProductName>Power Supply</ProductName><Quantity>1</Quantity><USPrice>45.99</USPrice></Item></Items></PurchaseOrder><PurchaseOrder PurchaseOrderNumber="99504" OrderDate="1999-10-22"><Address Type="Shipping"><Name>Jessica Arnold</Name><Street>4055 Madison Ave</Street><City>Seattle</City><State>WA</State><Zip>98112</Zip><Country>USA</Country></Address><Address Type="Billing"><Name>Jessica Arnold</Name><Street>4055 Madison Ave</Street><City>Buffalo</City><State>NY</State><Zip>98112</Zip><Country>USA</Country></Address><Items><Item PartNumber="898-AZ"><ProductName>Computer Keyboard</ProductName><Quantity>1</Quantity><USPrice>29.99</USPrice></Item><Item PartNumber="898-AM"><ProductName>Wireless Mouse</ProductName><Quantity>1</Quantity><USPrice>14.99</USPrice></Item></Items></PurchaseOrder></PurchaseOrders>

When I change my output from:

output {
  stdout {
    codec => rubydebug
  }
}

To this:

output {
  file {
    path => "output.json"
  }
}

I am getting the output in that file.

It occurs to me that it is possible to have all data between:

<PurchaseOrder PurchaseOrderNumber="99504" OrderDate="1999-10-22">

and

</PurchaseOrder>

in seperate json events and write that to file.. Is that what you need?

7

i got output like this

<PurchaseOrder PurchaseOrderNumber="99504" OrderDate="1999-10-22">

but iam expecting output as json format in a file like key value pair same as the link u shared

8

Is that really what your input looks like? I would expect you to be getting a configuration error if you are using that.

If you are sending the XML one line at a time then this might work.

input {
    beats {
        port => 5049
        codec => multiline {
            pattern => "^<PurchaseOrders>"
            what => previous
            negate => true
        }
    }
}
9

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