Hi,
I am struggling with xml data in splunk and need help in mvzip /mvexpand command to store multi value pairs with different cardinality and expand them as value pair correctly.
I have a xml which looks as below .
<xml>
<Order>1-23445565 </Order>
<SubOrder></SubOrder>
<Product>Phone</Product>
<AppointmentID>1234</AppointmentID>
<SubOrder></SubOrder>
<Product>Mobile</Product>
<AppointmentID>12345</AppointmentID>
<SubOrder></SubOrder>
<Product>TV</Product>
<SubOrder></SubOrder>
<Product>Internet</Product>
<AppointmentID>123456</AppointmentID>
<xml>
I am trying to extract all the multivalue pair values ( Product, AppointmentID) and storing them using mvzip command, but when there is a null value it does not handle it well. I also tried with fillnull command which does not seem to work pretty well here .
My search looks like this at this moment to extract the key value pairs.
index="*"
|rex "<Order>(?P<OrderId>[^\<]+)"
|rex max_match=0 "<Product>(?P<tmpProduct>[^\<]+)"
|rex max_match=0 "<AppointmentID>(?P<tmpAppointmentID>[^\<]+)"
|eval field1=mvzip(tmpProduct,tmpAppointmentID)
| mvexpand field1
| rex field=field1 "(?<ProductName>[^,]+),(?<AppointmentID>[^,]+)"
| table OrderId,field1, ProductName,AppointmentID
In output I am getting below results which is incorrect.
OrderId field1 ProductName AppointmentID
1-23445565 Phone,1234 Phone 1234
1-23445565 Mobile,12345 Mobile 12345
1-23445565 TV,123456 TV 123456
I am missing one record with Product called "Internet" and the AppointmentID is wrongly getting appended to Product "TV" as the same product does not have any AppointmentID in the raw xml data.
Ideally My ouput should look like as below.
OrderId field1 ProductName AppointmentID
1-23445565 Phone,1234 Phone 1234
1-23445565 Mobile,12345 Mobile 12345
1-23445565 TV,"N/A" TV "N/A"
1-23445565 Internet, 123456
Any help here is much appreciated.
... View more