how to fill NULL in dashboard when element is not occurred in XML

[anilkchepuri]

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'

---

_time NextEvent StepEvent

timestamp String5 String2

But the below is what i want to display.

---

_time NextEvent StepEvent

timestamp String5 String2
timestamp NULL String5

How to get the NULL when nextEventName is not occured in 'Step'

Thanks in Advance

[woodcock]

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"

[jkat54]

What happens if you eval the null?

| eval NextEvent=if(
isnull(mvindex(x,0)),”NULL”,
mvindex(x,0)
)

[anilkchepuri]

No impact. At this step field 'x' already has 'nextEventName'='String2' at index '0'. It does not know what to replace with "NULL"

[niketn · Accepted solution]

@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.

[anilkchepuri]

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>        

[anilkchepuri]

Thank you very much Niket. It was my bad. It works.

[niketn]

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 *