Getting Data In

Need help extracting fields from an XML dataset.

adzeh
Engager

Afternoon all,

I have an XML dataset that I am struggling to extract fields from. What I need is for the <key> value to be the field name and the  <value> to be the value of that field. For example: BLAdets.Bladetsmeta.FIELD_1="this is the value of field 1":

<BLAdets>
<Bladetsmeta>
<Metadata><Key>FIELD_1</Key><Label>FIELD 1 test</Label><Value>this is the value of field 1</Value></Metadata>
<Metadata><Key>FIELD_2</Key><Label>FIELD 2 test</Label><Value>this is the value of field 2</Value></Metadata>
<Metadata><Key>FIELD_3</Key><Label>FIELD 3 test </Label><Value>this is the value of field 3</Value></Metadata>
</Bladetsmeta>
</BLAdets>

I have tried xmlkv but it creates a key field with value FIELD_1. Any ideas would be much appreciated.

Thanks.

Labels (1)
Tags (3)
0 Karma

ITWhisperer
Ultra Champion

How about something along these lines:

index=_internal | head 1
| eval _raw="<BLAdets>
<Bladetsmeta>
<Metadata><Key>FIELD_1</Key><Label>FIELD 1 test</Label><Value>this is the value of field 1</Value></Metadata>
<Metadata><Key>FIELD_2</Key><Label>FIELD 2 test</Label><Value>this is the value of field 2</Value></Metadata>
<Metadata><Key>FIELD_3</Key><Label>FIELD 3 test </Label><Value>this is the value of field 3</Value></Metadata>
</Bladetsmeta>
</BLAdets>"
| spath BLAdets.Bladetsmeta.Metadata output=Metadata
| rex field=Metadata "\<Key>(?<key>[^\<]*)\</Key>.*\<Value>(?<value>[^\<]*)\</Value>"
| eval meta=mvzip(mvzip(key,value,">"),key,"</")
| eval meta=mvmap(meta,"<"+meta+">")
| spath input=meta

adzeh
Engager

Do you know of anything that can replace the mvmap command? It’s not in my version of Splunk.

0 Karma

adzeh
Engager

Wouldn’t this approach mean after the spath each field name would have all the whole heiarchy in the field name? That would be huge after a lot of values and if a field exists in one log that doesn’t exist in another then that changes thr heiarchy and the field names won’t match.

0 Karma

to4kawa
SplunkTrust
SplunkTrust

| spath BLAdets.Bladetsmeta.Metadata output=Metadata |mvexpand Metadata | spath input=Metadata |eval {Key}=Value |fields - Key Value Metadata

0 Karma

adzeh
Engager

This approach works but it creates a massive amount of events with no relation to each other, Eg tabling will have null on other fields as they are no longer in the same event.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!