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

[sushmitha_mj]

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?

[somesoni2 · Accepted solution]

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

[sushmitha_mj]

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

[somesoni2]

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

[sushmitha_mj]

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
Mgr_CA ------ 96
Mgr_DO ------ 96

Field: Manager

Values Count
Mgr_CA ------ 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
Mgr_CA ------ 96
Mgr_DO ------ 96

The output I get for your query without using mv command is
Manager --------- Sent --------- Received
Mgr_CA --------- 251 --------- 251
Mgr_DO --------- 251 --------- 251

What it actually should be :

Manager --------- Sent --------- Received
Mgr_CA --------- 0 --------- 0
Mgr_DO --------- 251 --------- 251

[somesoni2]

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

[sushmitha_mj]

This is the raw data. I have just put entered it manually.
because I could attach screenshots

[somesoni2]

Give this a try

your base 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 temp=mvzip(mvzip(Manager,Sent,"#"),Received,"#") | table temp  | mvexpand temp | rex field=temp "(?<Manager>.+)#(?<Sent>.+)#(?<Received>.+)"|
stats max(Sent) as "Sent ", max(Received) as "Received" by Manager

[sushmitha_mj]

Wow... It worked...
Thank you so much..

[sundareshr]

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

[sushmitha_mj]

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, ":"), ":")

[richgalloway]

Please share some sample data.

[sushmitha_mj]

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
   
   Mgr_CA ------ 96
   Mgr_DO ------ 96

2. Field: Manager

Values Count

Mgr_CA ------ 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
      Mgr_CA ------ 96
      Mgr_DO ------ 96