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!

Splunk Observability Cloud | Customer Survey!

If you use Splunk Observability Cloud, we invite you to share your valuable insights with us through a brief ...

Happy CX Day, Splunk Community!

Happy CX Day, Splunk Community! CX stands for Customer Experience, and today, October 3rd, is CX Day — a ...

.conf23 | Get Your Cybersecurity Defense Analyst Certification in Vegas

We’re excited to announce a new Splunk certification exam being released at .conf23! If you’re going to Las ...