Solved: Re: How to break the XML file into multiple events...

nasrinmulani · ‎07-26-2018

Hi All,

I have indexed the XML file without breaking it into events, I need to break the events using on tag. Hence can i achieve this using the search head?
Example

I need to break the events by tag, so that i can fetch the details in each tag as each row.

Thanks in advance!

niketn · ‎07-30-2018

@nasrinmulani, please try the following two options with run anywhere examples:

Option 1: Using spath

| makeresults
| eval _raw="<Head>
 <Detail>
 <id>123</id>
 <Name>x</Name>
 </Detail>
 </Head>
 <Head>
 <Detail>
 <id>1234</id>
 <Name>y</Name>
 </Detail>
 </Head>
 <Head>
 <Detail>
 <id>12347</id>
 <Name>y</Name>
 </Detail>
 </Head>"
| spath
| fields - _*
| rename "Head.Detail.*" as "*"
| eval data=mvzip(id,Name)
| fields data
| mvexpand data
| makemv data delim=","
| eval id=mvindex(data,0), Name=mvindex(data,1)
| fields - data

Option 2: Using rex command

| makeresults
| eval _raw="<Head>
 <Detail>
 <id>123</id>
 <Name>x</Name>
 </Detail>
 </Head>
 <Head>
 <Detail>
 <id>1234</id>
 <Name>y</Name>
 </Detail>
 </Head>
 <Head>
 <Detail>
 <id>12347</id>
 <Name>y</Name>
 </Detail>
 </Head>"
| rex "(?ms)(?<Detail>\<Detail\>.*?\<\/Detail\>)" max_match=0
| fields - _*
| mvexpand Detail
| rename Detail as _raw
| spath

PS: First two pipes i.e. | makeresults and | eval _raw .... generate sample event as per the question. You should use your own base search instead of these to test with your data.

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

View solution in original post

niketn · ‎07-30-2018

@nasrinmulani, please try the following two options with run anywhere examples:

Option 1: Using spath

| makeresults
| eval _raw="<Head>
 <Detail>
 <id>123</id>
 <Name>x</Name>
 </Detail>
 </Head>
 <Head>
 <Detail>
 <id>1234</id>
 <Name>y</Name>
 </Detail>
 </Head>
 <Head>
 <Detail>
 <id>12347</id>
 <Name>y</Name>
 </Detail>
 </Head>"
| spath
| fields - _*
| rename "Head.Detail.*" as "*"
| eval data=mvzip(id,Name)
| fields data
| mvexpand data
| makemv data delim=","
| eval id=mvindex(data,0), Name=mvindex(data,1)
| fields - data

Option 2: Using rex command

| makeresults
| eval _raw="<Head>
 <Detail>
 <id>123</id>
 <Name>x</Name>
 </Detail>
 </Head>
 <Head>
 <Detail>
 <id>1234</id>
 <Name>y</Name>
 </Detail>
 </Head>
 <Head>
 <Detail>
 <id>12347</id>
 <Name>y</Name>
 </Detail>
 </Head>"
| rex "(?ms)(?<Detail>\<Detail\>.*?\<\/Detail\>)" max_match=0
| fields - _*
| mvexpand Detail
| rename Detail as _raw
| spath

PS: First two pipes i.e. | makeresults and | eval _raw .... generate sample event as per the question. You should use your own base search instead of these to test with your data.

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

nasrinmulani · ‎08-02-2018

Thanks!! It was helpful for me, but now i am thinking to break it via props.conf itself.
I got one issue that my log events are breaking in between, may it's because of larger size of records so it's truncating it.

Any one know about the breaking of file. I am trying with
BREAK_ONLY_BEFORE=
MUST_BREAK_AFTER=<\/Detail>

let's see if it works.

niketn · ‎08-04-2018

Change the TRUNCATE limit as per your data refer more options for Event Breaking: https://docs.splunk.com/Documentation/Splunk/latest/Data/Configureeventlinebreaking

Test with some of the xml data for which it is not working in Preview mode with sample data.

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

jplumsdaine22 · ‎07-31-2018

As an extra note, unless your XML will always have the same structure, use | spath (IE Option 1) instead of rex to extract the fields.

efavreau · ‎07-31-2018

Look into using spath within an eval statement, if you find the spath command is slow. We found that rewriting spath to using spath within an eval, and the occasional rex, is much faster. Use the job inspector to measure your results.

Note it was a lot of work to rewrite the command into eval and rex, but for as often as we needed it, for the amount of data we needed it for, it was worth the effort. YMMV.

The Doc for Eval: http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/CommonEvalFunctions

From that page:

spath(X,Y) Extracts a value from a
structured data type (XML or JSON) in
X based on a location path in Y.

###

If this reply helps you, an upvote would be appreciated.

niketn · ‎07-26-2018

@nasrinmulani I think you have missed the example. Please mock/anonymize any sensitive data before posting.

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

nasrinmulani · ‎07-27-2018

I don't know it's getting replaced by blank place

jplumsdaine22 · ‎07-27-2018

In the text editor for putting your question theres a little picture of some 1s and zeroes. It will open a code box you can paste your xml in there

nasrinmulani · ‎07-30-2018

<Head>
<Detail>
<id>123</id>
<Name>x</Name>
</Detail>
</Head>
<Head>
<Detail>
<id>1234</id>
<Name>y</Name>
</Detail>
</Head>
<Head>
<Detail>
<id>12347</id>
<Name>y</Name>
</Detail>
</Head>

nasrinmulani · ‎07-30-2018

I want to break this into multiple events, it is not a single event.
I want break it through tag but in search head.

Is there any SPL command to break the events from search head?

nasrinmulani · ‎07-27-2018

Thanks Niket,

I don't it's getting replacing with blank place.
But Example is -
t1 tag start
t1 tag end
t2 tag start
t2 tag end
t2 tag start
t2 tag end
t2 tag start
t2 tag end
t3 tag start
t3 tag end

How to break the XML file into multiple events from search head?

Introducing the 2024 SplunkTrust!

Introducing the 2024 Splunk MVPs!

Splunk Custom Visualizations App End of Life