Re: Extract key value pair from xml

KarunK · ‎11-06-2013

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

kk

gkanapathy · ‎11-07-2013

You can use:

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

KarunK · ‎11-07-2013

Hi,

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

thx mate ..

kkn

ShaneNewman · ‎11-06-2013

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+)"

ShaneNewman · ‎11-11-2013

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.

KarunK · ‎11-06-2013

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

Extract key value pair from xml

Introducing the 2024 SplunkTrust!

Introducing the 2024 Splunk MVPs!

Splunk Custom Visualizations App End of Life