How to extract fields from json wrapped inside a XML data

[besa0903]

how to display fields in a table from the json embedded in the xml.

I tried spath and rex but to no luck.

[jluo_splunk]

Hi Besa0903,

I brought your data into my own splunk env and used spath - I can see serviceName extracted properly, while conversationID is set to null. If you run your search like this, do you see serviceName and other fields extracted from the json blob?

index=datapower host=mq-tst sourcetype=mq ServiceName=SC_GLOBAL_B2B_GenericSplunkLogger_MPGW *error*
| spath

[besa0903]

Thanks for working on it, It was not able to extract the serviceName and conversationID from MessageBody into the table...if you did, can you send me the query. [ make sure the serviceName and conversationID is extracted form the MesageBody]

[jluo_splunk]

Upon further inspection - there's some inconsistency to the formatting of the data (which is why spath won't quite work in this case.) I put together a janky search string that gets you the conversationId and serviceName - but it won't pick up field names/values out of the second half of your messageBody until the formatting is fixed..

index=datapower host=mq-tst sourcetype=mq ServiceName=SC_GLOBAL_B2B_GenericSplunkLogger_MPGW *error*
|spath
| spath input="MessageViewer.MessageBody"
| table conversationID, serviceName

[besa0903]

hello jluo, can you help me undestand how to fix the format or point to where the inconistency is.

[jluo_splunk]

\nhttps://acs2-dealer-orders-api.mcf-np.local/orderChangeLog?soldToPartnerId=0001662715&soldToPartnerIdType=SAP&seedYear=2018\n{\"headers\":{},\"body\":{\"dealerOrderChangeLogResponseEntityList\":[{\"soldToPartnerEBID\":\"1100027381904\",\"soldToPartnerGLNID\":\"1100027381904\",\"soldToPartnerSAPID\":\"0001662715\",\"soldToPartnerName\":\"MFA BOONVILLE MO\",\"soldToPartnerAddress\":\"1605 RADIO HILL ROAD\",\"sellerPartnerId\":\"0062668030000\",\"sellerPartnerIdType\":\"EBID\",\"requestingSource\":\"\",\"seedYear\":\"2018\",\"requestType\":\"LOG\",\"buyerId\":\"0001662715\",\"sellerId\":\"0062668030000\",\"dealerOrderChangeLogLineItemList\":[]}],\"apiErrors\":[],\"apiMessages\":[],\"apiResponseTimes\":{},\"requestId\":\"f325f348-48f2-4dba-5f40-a8a3f264c247\"},\"statusCode\":\"OK\",\"statusCodeValue\":200}

Starting from this section, you begin to include backslashes in the field names whereas previously you did not have them. Every now and then there's a line break (\n) but it's not quite clear when/why they are sometimes included?

"messageBody":"https://acs2-dealer-orders-api.mcf-np.local/orderChangeLog?soldToPartnerId=0001662715&soldToPartnerIdType=SAP&seedYear=2018host = acs2-dealer-orders-api.mcf-np.local

In this snippet, there are missing ampersands so the host actually isn't extracted properly because it's viewed as part of the seedYear.

Besa0903, if you were able to get the query working, could you accept this as your answer?