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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...