Hi,
Our system holds XML logs and the way it is structured, some of values are held inside a common set of name/value attribute pair which repeats number of times within the XML.
Index name is 'applogs'.
Example XML:
<RECORD>
<ORDER>
<OrderDate>21-11-2022</OrderDate>
<OrderRef>12345678></OrderRef>
<OrderAttributes>
<OrderAttributeName>Attribute1</OrderAttributeName>
<OrderAttributeValue>Value1<OrderAttributeValue>
<OrderAttributeName>Attribute2</OrderAttributeName>
<OrderAttributeValue>Value2<OrderAttributeValue>
<OrderAttributeName>Attribute3</OrderAttributeName>
<OrderAttributeValue>Value3<OrderAttributeValue>
</OrderAttributes>
</ORDER>
</RECORD>
I want to extract the individual attributes to display in a table something like this:
OrderDate | OrderRef | Attribute1 | Attribute2 | Attribute3 |
21-11-2022 | 12345678 | Value1 | Value2 | Value3 |
I have tried SPATH but not able to pull Attribute1 & Value1 pair as there are multiple instances of OrderAttributeName & OrderAttributeValue tags, so have hit the buffers.
Any suggestions on how can I make it work?
You can use spath then foreach to make the attribute/name values to their respective fields.
Note your original XML is malformed - you are missing "/" character on closing OrderAttributeValue and you have an extra > after your OrderRef.
| makeresults
| eval _raw="<RECORD>
<ORDER>
<OrderDate>21-11-2022</OrderDate>
<OrderRef>12345678</OrderRef>
<OrderAttributes>
<OrderAttributeName>Attribute1</OrderAttributeName>
<OrderAttributeValue>Value1</OrderAttributeValue>
<OrderAttributeName>Attribute2</OrderAttributeName>
<OrderAttributeValue>Value2</OrderAttributeValue>
<OrderAttributeName>Attribute3</OrderAttributeName>
<OrderAttributeValue>Value3</OrderAttributeValue>
</OrderAttributes>
</ORDER>
</RECORD>"
| spath
| rename RECORD.ORDER.* as *
| foreach 0 1 2 3 4 [ eval f=mvindex('OrderAttributes.OrderAttributeName', <<FIELD>>), {f}=mvindex('OrderAttributes.OrderAttributeValue', <<FIELD>>) ]
| table OrderDate OrderRef Attribute*
The numbers in the foreach will determine how many possible attributes this can handle
You can use spath then foreach to make the attribute/name values to their respective fields.
Note your original XML is malformed - you are missing "/" character on closing OrderAttributeValue and you have an extra > after your OrderRef.
| makeresults
| eval _raw="<RECORD>
<ORDER>
<OrderDate>21-11-2022</OrderDate>
<OrderRef>12345678</OrderRef>
<OrderAttributes>
<OrderAttributeName>Attribute1</OrderAttributeName>
<OrderAttributeValue>Value1</OrderAttributeValue>
<OrderAttributeName>Attribute2</OrderAttributeName>
<OrderAttributeValue>Value2</OrderAttributeValue>
<OrderAttributeName>Attribute3</OrderAttributeName>
<OrderAttributeValue>Value3</OrderAttributeValue>
</OrderAttributes>
</ORDER>
</RECORD>"
| spath
| rename RECORD.ORDER.* as *
| foreach 0 1 2 3 4 [ eval f=mvindex('OrderAttributes.OrderAttributeName', <<FIELD>>), {f}=mvindex('OrderAttributes.OrderAttributeValue', <<FIELD>>) ]
| table OrderDate OrderRef Attribute*
The numbers in the foreach will determine how many possible attributes this can handle