Issue in Reading xml data

[karunagaraprabh]

I have similar XML data stated below and output of my query as shared in snapshot

<root>
<Jobs>
<job>
<id>Customer-1</id>
<startrange>
<sideA>
<serialNumber>1</serialNumber>
<timestramp>2021-03-23T07:14:09.906-0700</timestramp>
</sideA>
<sideB>
<serialNumber>2</serialNumber>
<timestramp>2021-03-23T07:15:29.906-0700</timestramp>
</sideB>
</startrange>
</job>
<job>
<id>Customer-2</id>
<startrange>
<sideA>
<serialNumber>3</serialNumber>
<timestramp>2021-03-23T07:14:29.906-0700</timestramp>
</sideA>
<sideB>
<serialNumber>4</serialNumber>
<timestramp>2021-03-23T07:15:39.906-0700</timestramp>
</sideB>
</startrange>
</job>
<job>
<id>Customer-3</id>
<startrange>
<sideA>
<serialNumber>5</serialNumber>
<timestramp>2021-03-23T07:14:49.906-0700</timestramp>
</sideA>
</startrange>
</job>
<job>
<id>Customer-4</id>
<startrange>
<sideA>
<serialNumber>7</serialNumber>
<timestramp>2021-03-23T07:14:29.906-0700</timestramp>
</sideA>
<sideB>
<serialNumber>8</serialNumber>
<timestramp>2021-03-23T07:15:39.906-0700</timestramp>
</sideB>
</startrange>
</job>
</Jobs>
</root>

Sample Query:

samplerquery.png

My output:

sampleoutput.png

for the customer-3 Side-B is un-available so Splunk is taking customer-4 -side B data and displaying for Customer-3, i need blank values for the customer B

Required output:

sampleoutput-1.png

Could any one help me how can i achieve it.

[manjunathmeti]

hi @karunagaraprabh,
You can extract each job and then use spath to extract key-value pairs. See if this works for you.

| rex max_match=0 "(?<event>\<job\>[^\?]+?\<\/job\>)" 
| mvexpand event 
| spath input=event 
| rename job.startrange.* as *, *.* as *_* 
| table job_id, sideA*, sideB*

If this reply helps you, a like would be appreciated.

[richgalloway]

We'll need to see the SPL that produced the output (the "Sample Query" is not a query), but I'm guessing it uses the appendcols command.  This is an example of why I discourage the use of appendcols.  Use append, instead, and follow it with stats values(*) as * by ID to re-group the results.