Dashboards & Visualizations

Issue in Reading xml data

karunagaraprabh
Explorer

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.

Labels (1)
0 Karma

manjunathmeti
Champion

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...