Security

How to extract fields from json wrapped inside a XML data

besa0903
New Member

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

I tried spath and rex but to no luck.

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

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

0 Karma

besa0903
New Member

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]

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

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

0 Karma

besa0903
New Member

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

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee
\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?

0 Karma
Get Updates on the Splunk Community!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...