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"
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>