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 Security Content for Threat Detection & Response, Q1 Roundup

Join Principal Threat Researcher, Michael Haag, as he walks through:An introduction to the Splunk Threat ...

Splunk Life | Happy Pride Month!

Happy Pride Month, Splunk Community! &#x1f308; In the United States, as well as many countries around the ...

SplunkTrust | Where Are They Now - Michael Uschmann

The Background Five years ago, Splunk published several videos showcasing members of the SplunkTrust to share ...