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

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

sushmitha_mj — Thu, 26 May 2016 18:34:28 GMT

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?

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

richgalloway — Thu, 26 May 2016 19:39:55 GMT

Please share some sample data.

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

sushmitha_mj — Tue, 29 Sep 2020 09:49:05 GMT

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.

Field: a
Values Count

0 ------ 96
250 ------ 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
1. Field: Sent Values Count
  0 ------ 192
  251 ------ 192
2. Field : z Values Count
  0 ------ 96
  251 ------ 96
  Mgr_CA ------ 96
  Mgr_DO ------ 96

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

sundareshr — Thu, 26 May 2016 20:40:02 GMT

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

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

somesoni2 — Thu, 26 May 2016 21:13:07 GMT

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

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

sushmitha_mj — Fri, 27 May 2016 14:49:43 GMT

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

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

sushmitha_mj — Fri, 27 May 2016 14:52:08 GMT

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

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

somesoni2 — Fri, 27 May 2016 14:56:45 GMT

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

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

sushmitha_mj — Tue, 29 Sep 2020 09:49:29 GMT

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

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

somesoni2 — Fri, 27 May 2016 18:11:05 GMT

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

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

sushmitha_mj — Fri, 27 May 2016 19:54:10 GMT

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

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

somesoni2 — Fri, 27 May 2016 20:03:37 GMT

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

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

sushmitha_mj — Mon, 30 May 2016 20:24:54 GMT

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