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.
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
Do you know of anything that can replace the mvmap command? It’s not in my version of Splunk.
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.
| spath BLAdets.Bladetsmeta.Metadata output=Metadata |mvexpand Metadata | spath input=Metadata |eval {Key}=Value |fields - Key Value Metadata
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.