Splunk Search

Multivalued field mapping issue from an nested XML source

sundarrajan
Path Finder

Hello Splunkers!

I have an issue in grouping multivalued field after extracting fields from nested xml. The sample is as follows,

 

 <WorkstationMetrics xmlns=“xxxxxxxx”>
                    <WorkstationMetricData TypeCode="None" WorkstationID="0">
                      <SequenceNumberValue Timestamp="2022-05-1" TypeCode="First">15704</SequenceNumberValue>
                      <SequenceNumberValue Timestamp="2022-05-1" TypeCode="Last">15710</SequenceNumberValue>
                    </WorkstationMetricData>
                    <WorkstationMetricData TypeCode="Manual" WorkstationID="03">
                      <SequenceNumberValue Timestamp="2022-05-1" TypeCode="First">9395</SequenceNumberValue>
                      <SequenceNumberValue Timestamp="2022-05-1" TypeCode="Last">9463</SequenceNumberValue>
                    </WorkstationMetricData>
                    <WorkstationMetricData TypeCode="Manual" WorkstationID="05">
                      <SequenceNumberValue Timestamp="2022-05-1" TypeCode="First">62</SequenceNumberValue>
                      <SequenceNumberValue Timestamp="2022-05-1" TypeCode="Last">297</SequenceNumberValue>
                    </WorkstationMetricData>
                  </WorkstationMetrics>

 

I tried with following search query to  extract field. But the fields extracted are multivalued with varying cardinality and hence some of my mvzip commands are not working as expected. Please find below my search query for your reference.

 

index=... sourcetype=...
| spath output=workstationNumber path=WorkstationMetrics.WorkstationMetricData{@WorkstationID}
| spath output=sequenceType path=WorkstationMetrics.WorkstationMetricData.SequenceNumberValue{@TypeCode}
| spath output=sequenceNumber path=WorkstationMetrics.WorkstationMetricData.SequenceNumberValue
| eval consolidate=mvzip(sequenceType,sequenceNumber)
| mvexpand consolidate
| eval temp=split(consolidate,","), type=mvindex(temp,0), seqno=mvindex(temp,1)
| table workstationNumber type seqno

 

I expect to present this data in following format, could some one 

Sl.noWorkstationIDTypeCodeSequenceNumberValue
10First15704
20Last15710

Any help in mapping 2 multivalued fields with varying cardinality would resolve this issue. Or do we need think out of box?

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| spath output=workstationNumber path=WorkstationMetrics.WorkstationMetricData{@WorkstationID}
| spath output=workstationData path=WorkstationMetrics.WorkstationMetricData
| fields - _raw
| eval workstation=mvzip(workstationNumber, workstationData,"|")
| mvexpand workstation
| eval workstationNumber=mvindex(split(workstation,"|"),0)
| eval workstationData=mvindex(split(workstation,"|"),1)
| spath output=sequenceType input=workstationData path=SequenceNumberValue{@TypeCode}
| spath output=sequenceNumber input=workstationData path=SequenceNumberValue
| eval typevalue=mvzip(sequenceType, sequenceNumber,"|")
| mvexpand typevalue
| eval sequenceType=mvindex(split(typevalue,"|"),0)
| eval sequenceNumber=mvindex(split(typevalue,"|"),1)
| fields - workstation workstationData typevalue

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| spath output=workstationNumber path=WorkstationMetrics.WorkstationMetricData{@WorkstationID}
| spath output=workstationData path=WorkstationMetrics.WorkstationMetricData
| fields - _raw
| eval workstation=mvzip(workstationNumber, workstationData,"|")
| mvexpand workstation
| eval workstationNumber=mvindex(split(workstation,"|"),0)
| eval workstationData=mvindex(split(workstation,"|"),1)
| spath output=sequenceType input=workstationData path=SequenceNumberValue{@TypeCode}
| spath output=sequenceNumber input=workstationData path=SequenceNumberValue
| eval typevalue=mvzip(sequenceType, sequenceNumber,"|")
| mvexpand typevalue
| eval sequenceType=mvindex(split(typevalue,"|"),0)
| eval sequenceNumber=mvindex(split(typevalue,"|"),1)
| fields - workstation workstationData typevalue

sundarrajan
Path Finder

Thanks @ITWhisperer ! It even works over a large dataset with multiple lines. A clear out-of box view, of taking the portion of event as a field and piping to rex  the relevant field. 

Thanks again!

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...