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!

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

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...