topic Extract key value pair from xml in Dashboards & Visualizations

Extract key value pair from xml

KarunK — Thu, 07 Nov 2013 04:08:31 GMT

Hi All,

I have an interesting problem. I have an xml which has a certain number of key value pairs which I need to extract it.

<record>
    <from>customerservice</from>
    <customer>Dan</customer>
    <heading>Reminder</heading>
    <body>Payment Overdue.Amount=176.Discount=16.Pay=160</body>
</record>

I need to extract the following key value pairs
Amount=176.Amount=16.Pay=160

And turn them into a following table

customer | Amount | Amount | Pay
------------------------------
Dan      | 176    | 16     | 160

How can I do it ?

Appreciate your help.

Thanks

Re: Extract key value pair from xml

ShaneNewman — Thu, 07 Nov 2013 04:14:47 GMT

You can use xmllv

xmlkv link

If that doesn't work for you, | rex field=_raw "Amount\=(?<amount>\d+)\.Discount\=(?<discount>\d+)\.Pay\=(?<pay>\d+)"

Re: Extract key value pair from xml

KarunK — Thu, 07 Nov 2013 05:33:05 GMT

Hi Shane,
Thanks for the quick response. I am already doing xmlkv and extracting the XML values for me.

Eg:

body=Payment Overdue.Amount=176.Discount=16.Pay=160

I can see rex command will be usefull. But i am looking for a generic rule which will extract every thing in "body" automatically.

Regards

kkn

Re: Extract key value pair from xml

gkanapathy — Thu, 07 Nov 2013 08:14:18 GMT

You can use:

... | extract pairdelim="." kvdelim="="

Re: Extract key value pair from xml

KarunK — Thu, 07 Nov 2013 11:20:16 GMT

Hi,

I have tried that too..Didn't work. It may be because, the data source is xml. Just guessing...

thx mate ..

kkn

Re: Extract key value pair from xml

ShaneNewman — Tue, 12 Nov 2013 00:19:30 GMT

I don't know of anything to help you, other than what @gkanapathy suggested. You can always setup what he suggested in your props.conf and transforms.conf. This should automatically extract any values that are preceded by an "=" sign as a value of the field before the "=" sign for the sourcetype you specify.