topic Re: How to extract fields from json wrapped inside a XML data in Security

How to extract fields from json wrapped inside a XML data

besa0903 — Mon, 11 Jun 2018 14:54:00 GMT

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

I tried spath and rex but to no luck.

Re: How to extract fields from json wrapped inside a XML data

jluo_splunk — Mon, 11 Jun 2018 16:24:56 GMT

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

Re: How to extract fields from json wrapped inside a XML data

besa0903 — Mon, 11 Jun 2018 16:54:47 GMT

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]

Re: How to extract fields from json wrapped inside a XML data

jluo_splunk — Mon, 11 Jun 2018 21:46:11 GMT

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

Re: How to extract fields from json wrapped inside a XML data

besa0903 — Tue, 12 Jun 2018 14:59:07 GMT

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

Re: How to extract fields from json wrapped inside a XML data

jluo_splunk — Tue, 12 Jun 2018 16:04:35 GMT

\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?