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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...