Security

How to parse and extract a json tag wrapped inside a XML?

besa0903
New Member

I need to extract Json elements wrapped in the XML node. For example, in the below code, i want to extract the json elements ServiceName, messageBody, environment, documentID.`

<?xml version="1.0" encoding="UTF-8"?>
0 Karma
1 Solution

niketn
Legend

@besa0903, following is a run anywhere search based on sample data:

|  makeresults
|  eval _raw=" <?xml version=\"1.0\" encoding=\"UTF-8\"?>
 <MessageViewer>
    <System>datapower</System>
    <Layer>internal</Layer>
    <ServiceName>SC_GLOBAL_B2B_GenericSplunkLogger_MPGW</ServiceName>
    <TransactionMessageID>ece94fe8-7120-413b-827f-ade3bb8082ba</TransactionMessageID>
    <SystemMessageID>250053189</SystemMessageID>
    <MessageDateTime>2018-06-01T14:18:30-05:00</MessageDateTime>
    <PartnerName />
    <PartnerID />
    <ConversationID />
    <DocumentID />
    <LogType>request</LogType>
    <MessageBody><![CDATA[{ \"value_schema_id\":1070, \"records\":[    {   \"value\":{      \"environment\":\"nonProd\",      \"system\":\"DataInsights-LayersAPII\",      \"serviceName\":\"ACS2-Pricing\",      \"messageDateTime\":2017914103820,      \"transactionMessageID\":\"7147051835464951\",      \"systemMessageID\":\"5334091380487482\",      \"partnerName\":\"PartnerName\",      \"partnerID\":\"PartnerId\",      \"conversationID\":\"sdfsdf\",      \"documentID\":\"46051784092251646\",      \"logType\":\"response\",      \"messageBody\":\"This is the message body\"      }} ]}]]></MessageBody>
    <DataPower>
       <DomainName>POC</DomainName>
       <RuleName>I_SplunkLogger_Req</RuleName>
       <UrlIn>http://datapower-local:6032/SplunkLogger/generic</UrlIn>
       <UrlOut>http://datapower-local:6032/SplunkLogger/generic</UrlOut>
    </DataPower>
 </MessageViewer>"
| spath
| fields "MessageViewer.MessageBody" 
| eval _raw= 'MessageViewer.MessageBody'
| fields - "MessageViewer.MessageBody" 
| spath

This is a simple approach for example. You can read about the spath command and apply it to specific input and extract the fields you are interested in.

However, if you are only interested in JSON data, you can also think about indexing only JSON data within XML or may be re routing JSON Data using props.conf and transforms.conf.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@besa0903, following is a run anywhere search based on sample data:

|  makeresults
|  eval _raw=" <?xml version=\"1.0\" encoding=\"UTF-8\"?>
 <MessageViewer>
    <System>datapower</System>
    <Layer>internal</Layer>
    <ServiceName>SC_GLOBAL_B2B_GenericSplunkLogger_MPGW</ServiceName>
    <TransactionMessageID>ece94fe8-7120-413b-827f-ade3bb8082ba</TransactionMessageID>
    <SystemMessageID>250053189</SystemMessageID>
    <MessageDateTime>2018-06-01T14:18:30-05:00</MessageDateTime>
    <PartnerName />
    <PartnerID />
    <ConversationID />
    <DocumentID />
    <LogType>request</LogType>
    <MessageBody><![CDATA[{ \"value_schema_id\":1070, \"records\":[    {   \"value\":{      \"environment\":\"nonProd\",      \"system\":\"DataInsights-LayersAPII\",      \"serviceName\":\"ACS2-Pricing\",      \"messageDateTime\":2017914103820,      \"transactionMessageID\":\"7147051835464951\",      \"systemMessageID\":\"5334091380487482\",      \"partnerName\":\"PartnerName\",      \"partnerID\":\"PartnerId\",      \"conversationID\":\"sdfsdf\",      \"documentID\":\"46051784092251646\",      \"logType\":\"response\",      \"messageBody\":\"This is the message body\"      }} ]}]]></MessageBody>
    <DataPower>
       <DomainName>POC</DomainName>
       <RuleName>I_SplunkLogger_Req</RuleName>
       <UrlIn>http://datapower-local:6032/SplunkLogger/generic</UrlIn>
       <UrlOut>http://datapower-local:6032/SplunkLogger/generic</UrlOut>
    </DataPower>
 </MessageViewer>"
| spath
| fields "MessageViewer.MessageBody" 
| eval _raw= 'MessageViewer.MessageBody'
| fields - "MessageViewer.MessageBody" 
| spath

This is a simple approach for example. You can read about the spath command and apply it to specific input and extract the fields you are interested in.

However, if you are only interested in JSON data, you can also think about indexing only JSON data within XML or may be re routing JSON Data using props.conf and transforms.conf.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Aligning Observability Costs with Business Value: Practical Strategies

 Join us for an engaging Tech Talk on Aligning Observability Costs with Business Value: Practical ...

Mastering Data Pipelines: Unlocking Value with Splunk

 In today's AI-driven world, organizations must balance the challenges of managing the explosion of data with ...

Splunk Up Your Game: Why It's Time to Embrace Python 3.9+ and OpenSSL 3.0

Did you know that for Splunk Enterprise 9.4, Python 3.9 is the default interpreter? This shift is not just a ...