Splunk Search
Highlighted

Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

Communicator

I am using mvexpand for getting multiple fields from an XML and grouping them. Here is my search:

spath output=Manager path=env:Envelope.env:Body.dp:response.dp:status.Manager | spath output=Received path=env:Envelope.env:Body.dp:response.dp:status.Messages | spath output=Sent path=env:Envelope.env:Body.dp:response.dp:status.MQQMstatus.Sent |  fields Manager,Received,Sent |  eval a=mvzip(Manager,Sent,":") | mvexpand a|eval a=split(a,":")|eval Manager=mvindex(a,0) | eval Sent=mvindex(a,1) | eval z=mvzip(Manager,Received,":") | mvexpand z|eval z=split(z,":")|eval Manager=mvindex(z,0) | eval Received=mvindex(z,1) |stats max(Sent) as "Sent ", max(Received) as "Received" by Manager

When I run this search, it gives me values. However, the value for Sent is right, but Received is wrong. When I reverse the order of the eval and stats, the value in received is right and value of sent is wrong.

Am I using the mvexpand wrong? How do I make this search work?

0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

SplunkTrust
SplunkTrust

Please share some sample data.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

Communicator

In my Case we have 5 fields. Sample data as follows:
Values are the values in the field, count is the number of rows/entries of data.

  1. Field: a
    Values Count

    0 ------ 96
    250 ------ 96

    MgrCA ------ 96
    Mgr
    DO ------ 96

  2. Field: Manager

Values Count

MgrCA ------ 192

Mgr
DO ------ 192

  1. Field: Recieved
    Values Count

    0 ------ 96

    251 ------ 96

    1. Field: Sent Values Count
      0 ------ 192
      251 ------ 192
    2. Field : z Values Count
      0 ------ 96
      251 ------ 96
      MgrCA ------ 96
      Mgr
      DO ------ 96
0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

Legend

Try this

spath output=Manager path=env:Envelope.env:Body.dp:response.dp:status.Manager | spath output=Received path=env:Envelope.env:Body.dp:response.dp:status.Messages | spath output=Sent path=env:Envelope.env:Body.dp:response.dp:status.MQQMstatus.Sent |  fields Manager,Received,Sent |  eval a=mvzip(Manager,mvzip(Sent, Received, ":"), ":") | mvexpand a|eval a=split(a,":")|eval Manager=mvindex(a,0) | eval Sent=mvindex(a,1) | eval Received=mvindex(z,2) |stats max(Sent) as "Sent ", max(Received) as "Received" by Manager
0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

Communicator

Thanks.
I understand the logic you are trying to use, but I am getting an error
"Error in 'eval' command: The expression is malformed. Expected )." on this line
mvzip(Manager,mvzip(Sent, Received, ":"), ":")

0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

SplunkTrust
SplunkTrust

Not sure you need the mvexpand at all. Just give this a try

spath output=Manager path=env:Envelope.env:Body.dp:response.dp:status.Manager | spath output=Received path=env:Envelope.env:Body.dp:response.dp:status.Messages | spath output=Sent path=env:Envelope.env:Body.dp:response.dp:status.MQQMstatus.Sent |stats max(Sent) as "Sent ", max(Received) as "Received" by Manager

View solution in original post

0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

Communicator

Thanks...
This query works but the output it is giving is wrong.
It is showing 250 for both managers and for both sent and received. It should have been zero for one manager and 250 for the other manager. It is probably getting the max on both, not grouping properly

0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

SplunkTrust
SplunkTrust

Can you provide sample raw events, to see how the actual data looks? Mask any sensitive data while posting.

0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

Communicator

In my Case we have 5 fields. Sample data as follows: (Based on my initial query using 2 mvzip "a" and "z" )
Values are the values in the field, count is the number of rows/entries of data.

Field: a
Values Count
0 ------ 96
251 ------ 96
MgrCA ------ 96
Mgr
DO ------ 96

Field: Manager

Values Count
MgrCA ------ 192
Mgr
DO ------ 192

Field: Recieved
Values Count
0 ------ 96
251 ------ 96

Field: Sent
Values Count
0 ------ 192
251 ------ 192

Field : z
Values Count
0 ------ 96
251 ------ 96
MgrCA ------ 96
Mgr
DO ------ 96

The output I get for your query without using mv command is
Manager --------- Sent --------- Received
MgrCA --------- 251 --------- 251
Mgr
DO --------- 251 --------- 251

What it actually should be :

Manager --------- Sent --------- Received
MgrCA --------- 0 --------- 0
Mgr
DO --------- 251 --------- 251

0 Karma
Highlighted

Re: Using mvexpand to get multiple fields from XML data, why am I getting incorrect values for a field?

SplunkTrust
SplunkTrust

Would be better if you just provide a sample raw data from below query. How many (OR whether) mv operations are required will depend on that.

spath output=Manager path=env:Envelope.env:Body.dp:response.dp:status.Manager
0 Karma