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
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"
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
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
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"