Hello,
I have following XML loaded into filed 'data'.
<Step>
<stepEventName>Strin2</stepEventName>
<nextEventName>String5</nextEventName>
</Step>
<Step>
<stepEventName>String7</stepEventName>
</Step>
I am able to extract the 'stepEventName', 'nextEventNam' element values using below spath:
spath input=data output=StepEvent path=SummaryEvent.StepEvent.stepEventName |
spath input=data output=NextEvent path=SummaryEvent.StepEvent.nextEventName |
eval x=mvzip(NextEvent,StepEvent) | mvexpand x| eval x=split(x,",") | eval NextEvent=mvindex(x,0) | eval StepEvent=mvindex(x,1) | fillnull value="NULL" |table NextEvent,StepEvent
But when i try to display event list i do get below since nextEventName occured once in 'Step'
timestamp String5 String2
But the below is what i want to display.
timestamp String5 String2
timestamp NULL String5
How to get the NULL when nextEventName is not occured in 'Step'
Thanks in Advance
@anilkchepuri, please try the following (first two pipe i.e. | makeresults
and | eval data
generate mock data as per question)
| makeresults
| eval data="<Step>
<stepEventName>Strin2</stepEventName>
<nextEventName>String5</nextEventName>
</Step>
<Step>
<stepEventName>String7</stepEventName>
</Step>"
| eval data=replace(data,"</stepEventName>(\s+)</Step>","</stepEventName><nextEventName>NULL</nextEventName></Step>")
| spath input=data
| rename Step.* as *
| eval x=mvzip(nextEventName,stepEventName)
| mvexpand x
| eval x=split(x,",")
| eval nextEventName=mvindex(x,0)
| eval stepEventName=mvindex(x,1)
| fillnull value="NULL"
| table nextEventName,stepEventName
The replace()
command is used to find missing XML node and adds a dummy nextEventName
with value NULL
.
Like this:
| makeresults
| eval data="<Step>
<stepEventName>Strin2</stepEventName>
<nextEventName>String5</nextEventName>
</Step>
<Step>
<stepEventName>String7</stepEventName>
</Step>"
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| rex max_match=0 field=data "(?ms)<Step>[\r\n\s]+(?<Step>.*?)[\r\n\s]+<\/Step>"
| fields Step
| mvexpand Step
| rename Step AS _raw
| spath
| fields - _raw
| fillnull value="NULL"
What happens if you eval the null?
| eval NextEvent=if(
isnull(mvindex(x,0)),”NULL”,
mvindex(x,0)
)
No impact. At this step field 'x' already has 'nextEventName'='String2' at index '0'. It does not know what to replace with "NULL"
@anilkchepuri, please try the following (first two pipe i.e. | makeresults
and | eval data
generate mock data as per question)
| makeresults
| eval data="<Step>
<stepEventName>Strin2</stepEventName>
<nextEventName>String5</nextEventName>
</Step>
<Step>
<stepEventName>String7</stepEventName>
</Step>"
| eval data=replace(data,"</stepEventName>(\s+)</Step>","</stepEventName><nextEventName>NULL</nextEventName></Step>")
| spath input=data
| rename Step.* as *
| eval x=mvzip(nextEventName,stepEventName)
| mvexpand x
| eval x=split(x,",")
| eval nextEventName=mvindex(x,0)
| eval stepEventName=mvindex(x,1)
| fillnull value="NULL"
| table nextEventName,stepEventName
The replace()
command is used to find missing XML node and adds a dummy nextEventName
with value NULL
.
Thanks niketnilay. I see it replacing for the first occurrence but what if i want to replace multiple times.
<stepEventName>Strin1</stepEventName>
<nextEventName>String2</nextEventName>
<stepEventName>String3</stepEventName>
<stepEventName>String4</stepEventName>
<nextEventName>String5</nextEventName>
<stepEventName>String6</stepEventName>
Thank you very much Niket. It was my bad. It works.
It should do multiple time replace as well. In your original query you have stepEventName
and nextEventName
inside <step>
node, however, in the above example you dont. Can you please confirm which one it is. I tested your above sample data inside <step>
node and it works fine by filling in multiple occurences.
| makeresults
| eval data="<Step>
<stepEventName>String1</stepEventName>
<nextEventName>String2</nextEventName>
</Step>
<Step>
<stepEventName>String3</stepEventName>
</Step>
<Step>
<stepEventName>String4</stepEventName>
<nextEventName>String5</nextEventName>
</Step>
<Step>
<stepEventName>String6</stepEventName>
</Step>
<Step>
<stepEventName>String7</stepEventName>
</Step>"
| eval data=replace(data,"</stepEventName>(\s+)</Step>","</stepEventName><nextEventName>NULL</nextEventName></Step>")
| spath input=data
| rename Step.* as *