Dashboards & Visualizations

Spath or Xpath or regex to extract multiple values in an xml format log message

asharmaeqfx
Path Finder

Hi All,

 

I have a query to extract the from the xml log data

--------------

2020-10-22 11:29:23,712 INFO (default-workqueue-73) [com.xyz.uyt.eds.building.documents.btw.house] ID-apprdhgr001-co-dmz-33780-17w876834676-5-w87434 building-documents-vic:pull-order-orchestration-v1 pull Order Response : <v1:SubmitOrderResponse xmlns:v1="http://www.xyz.com/services/building/documents/btw/v1"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<v1:context>
<v1:chnnlcd>06</v1:chnnlcd>
<v1:user>
<v1:memCode>5436</v1:memCode>
<v1:brCode>7654</v1:brCode>
<v1:usrCode>7634B</v1:usrCode>
</v1:user>
<v1:clientReferences>
<v1:clientReference type="type0">1234 - amit</v1:clientReference>
</v1:clientReferences>
<v1:transaction>
<v1:transactionDateTime>2020-10-22T11:29:00Z</v1:transactionDateTime>
</v1:transaction>
<v1:conversationId>2</v1:conversationId>
</v1:context>
<v1:documents>
<v1:document sequenceNo="1">
<v1:identifer>67</v1:identifer>
<v1:name>Reg Src Statement (Title)</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>320</v1:identifier>
<v1:name/>
</v1:authority>
</v1:organisations>
<v1:fee>5.9</v1:fee>
<v1:gst>0</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>5.9</v1:recoupmentFee>
<v1:totalFeeExclGst>15.9</v1:totalFeeExclGst>
<v1:totalGst>1</v1:totalGst>
</v1:document>
<v1:document sequenceNo="8">
<v1:identifer>75</v1:identifer>
<v1:name>Index Search</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>320</v1:identifier>
<v1:name>Index Registry</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>6.91</v1:fee>
<v1:gst>0.63</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>16.28</v1:totalFeeExclGst>
<v1:totalGst>1.63</v1:totalGst>
</v1:document>
<v1:document sequenceNo="9">
<v1:identifer>238</v1:identifer>
<v1:name>Plan Copy</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>320</v1:identifier>
<v1:name>Plan 8T6543</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>5.84</v1:fee>
<v1:gst>0</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>5.84</v1:recoupmentFee>
<v1:totalFeeExclGst>15.84</v1:totalFeeExclGst>
<v1:totalGst>1</v1:totalGst>
</v1:document>
<v1:document sequenceNo="10">
<v1:identifer>1</v1:identifer>
<v1:name>=Tax Certificate</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>34</v1:identifier>
<v1:name>Revenue Cert</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>20.27</v1:fee>
<v1:gst>1.84</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>28.43</v1:totalFeeExclGst>
<v1:totalGst>2.84</v1:totalGst>
<v1:serviceLevel>
<v1:period>10</v1:period>
</v1:serviceLevel>
</v1:document>
<v1:document sequenceNo="89">
<v1:identifer>80</v1:identifer>
<v1:name>Information Certificate</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>249</v1:identifier>
<v1:name>Check</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>35.690002</v1:fee>
<v1:gst>3.24</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>42.45</v1:totalFeeExclGst>
<v1:totalGst>4.24</v1:totalGst>
<v1:serviceLevel>
<v1:period>5</v1:period>
</v1:serviceLevel>
</v1:document>
<v1:document sequenceNo="26">
<v1:identifer>23</v1:identifer>
<v1:name>Other Information</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>698</v1:identifier>
<v1:name>RIVER WATER</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>87.04</v1:fee>
<v1:gst>7.91</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>89.13</v1:totalFeeExclGst>
<v1:totalGst>8.91</v1:totalGst>
<v1:serviceLevel>
<v1:period>7</v1:period>
</v1:serviceLevel>
</v1:document>
</v1:documents>
<v1:details>
</v1:details>
</v1:SubmitOrderResponse>

-----------------

Similar lines are there in the log files but the entries vary as per the record. Hence, I need to output to extract

<v1:fee>97.99</v1:fee>
<v1:gst>8.9</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>99.09</v1:totalFeeExclGst>
<v1:totalGst>9.9</v1:totalGst>

 

And show them in tabular format for each memcode, brcode and usrcode. I tried using spath

and xpath and xmlkv but none of them are working as expected.

With xmlkv, it shows only the last value or one value.

The xpath and spath are not extracting any data as shown in my below queries

 

<main search> | xpath outfield=memCode "/v1:SubmitOrderResponse/v1:context/v1:user/v1:memCode" | table outfield, _time

<main search> | spath outfield=v1:memCode path=v1:SubmitOrderResponse.v1:context.v1:user.v1:memCode | xmlkv| table outfield, _time

 

Can you please advise.

 

Thanks,

Amit

 

 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Namespace aren't currently supported - fortunately, all your elements are in the same namespace, so you can just remove the aliasing

| eval _raw=replace(_raw,"v1:","") 
| xpath outfield=memCode "/SubmitOrderResponse/context/user/memCode"

View solution in original post

asharmaeqfx
Path Finder

Thanks ITWhisperer  and to4kawa 

 

I used the combination of both and it works fine

Search query | rex mode=sed "s/.*(\<.*)/\1/ s/\<v1:document /#<v1:document /g" | eval _raw=replace(_raw,"v1:","")
| xpath outfield=fee "/SubmitOrderResponse/documents/document/fee" | xpath outfield=gst "/SubmitOrderResponse/documents/document/gst"| xpath outfield=deliveryFee "/SubmitOrderResponse/documents/document/deliveryFee"| xpath outfield=memCode "/SubmitOrderResponse/context/user/memCode"| table fee,gst,deliveryFee,memCode, _time

 

Now my table is merging the multiple values in one row. And currently working on it.

 

Thanks for your help.

Regards,

Amit

0 Karma

to4kawa
Ultra Champion
index=_internal | head 1 | fields _time _raw | eval _raw="2020-10-22 11:29:23,712 INFO (default-workqueue-73) [com.xyz.uyt.eds.building.documents.btw.house] ID-apprdhgr001-co-dmz-33780-17w876834676-5-w87434 building-documents-vic:pull-order-orchestration-v1 pull Order Response : <v1:SubmitOrderResponse xmlns:v1=\"http://www.xyz.com/services/building/documents/btw/v1\"
xmlns:xs=\"http://www.w3.org/2001/XMLSchema\">
<v1:context>
<v1:chnnlcd>06</v1:chnnlcd>
<v1:user>
<v1:memCode>5436</v1:memCode>
<v1:brCode>7654</v1:brCode>
<v1:usrCode>7634B</v1:usrCode>
</v1:user>
<v1:clientReferences>
<v1:clientReference type=\"type0\">1234 - amit</v1:clientReference>
</v1:clientReferences>
<v1:transaction>
<v1:transactionDateTime>2020-10-22T11:29:00Z</v1:transactionDateTime>
</v1:transaction>
<v1:conversationId>2</v1:conversationId>
</v1:context>
<v1:documents>
<v1:document sequenceNo=\"1\">
<v1:identifer>67</v1:identifer>
<v1:name>Reg Src Statement (Title)</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>320</v1:identifier>
<v1:name/>
</v1:authority>
</v1:organisations>
<v1:fee>5.9</v1:fee>
<v1:gst>0</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>5.9</v1:recoupmentFee>
<v1:totalFeeExclGst>15.9</v1:totalFeeExclGst>
<v1:totalGst>1</v1:totalGst>
</v1:document>
<v1:document sequenceNo=\"8\">
<v1:identifer>75</v1:identifer>
<v1:name>Index Search</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>320</v1:identifier>
<v1:name>Index Registry</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>6.91</v1:fee>
<v1:gst>0.63</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>16.28</v1:totalFeeExclGst>
<v1:totalGst>1.63</v1:totalGst>
</v1:document>
<v1:document sequenceNo=\"9\">
<v1:identifer>238</v1:identifer>
<v1:name>Plan Copy</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>320</v1:identifier>
<v1:name>Plan 8T6543</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>5.84</v1:fee>
<v1:gst>0</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>5.84</v1:recoupmentFee>
<v1:totalFeeExclGst>15.84</v1:totalFeeExclGst>
<v1:totalGst>1</v1:totalGst>
</v1:document>
<v1:document sequenceNo=\"10\">
<v1:identifer>1</v1:identifer>
<v1:name>=Tax Certificate</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>34</v1:identifier>
<v1:name>Revenue Cert</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>20.27</v1:fee>
<v1:gst>1.84</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>28.43</v1:totalFeeExclGst>
<v1:totalGst>2.84</v1:totalGst>
<v1:serviceLevel>
<v1:period>10</v1:period>
</v1:serviceLevel>
</v1:document>
<v1:document sequenceNo=\"89\">
<v1:identifer>80</v1:identifer>
<v1:name>Information Certificate</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>249</v1:identifier>
<v1:name>Check</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>35.690002</v1:fee>
<v1:gst>3.24</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>42.45</v1:totalFeeExclGst>
<v1:totalGst>4.24</v1:totalGst>
<v1:serviceLevel>
<v1:period>5</v1:period>
</v1:serviceLevel>
</v1:document>
<v1:document sequenceNo=\"26\">
<v1:identifer>23</v1:identifer>
<v1:name>Other Information</v1:name>
<v1:description/>
<v1:organisations>
<v1:authority>
<v1:identifier>698</v1:identifier>
<v1:name>RIVER WATER</v1:name>
</v1:authority>
</v1:organisations>
<v1:fee>87.04</v1:fee>
<v1:gst>7.91</v1:gst>
<v1:deliveryFee>11</v1:deliveryFee>
<v1:deliveryGst>1</v1:deliveryGst>
<v1:recoupmentFee>0</v1:recoupmentFee>
<v1:totalFeeExclGst>89.13</v1:totalFeeExclGst>
<v1:totalGst>8.91</v1:totalGst>
<v1:serviceLevel>
<v1:period>7</v1:period>
</v1:serviceLevel>
</v1:document>
</v1:documents>
<v1:details>
</v1:details>
</v1:SubmitOrderResponse>"
| rex mode=sed "s/.*(\<.*)/\1/ s/\<v1:document /#<v1:document /g"
| makemv delim="#" _raw
| stats count by _raw
| xmlkv

ITWhisperer
SplunkTrust
SplunkTrust

Namespace aren't currently supported - fortunately, all your elements are in the same namespace, so you can just remove the aliasing

| eval _raw=replace(_raw,"v1:","") 
| xpath outfield=memCode "/SubmitOrderResponse/context/user/memCode"
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...