Using MVZip and MVExpand on MultiValue fields where one node sometimes exists

[blairmd]

Hello friendly Splunk community,

May I ask your assistance in dealing with a multivalue field that sometimes contains one item and sometimes does not contain that item.

For example - the JSON data looks like this:

VerificationItems: [
  {
    Description: Description1   
  } 
  {
    Description: Description2
  } 
  {
    Description: Description3
    ErrorMessage: ErrorMessage3
  } 
]

Notice that the "ErrorMessage" field does not appear in every item.

What I would LIKE the output to look like is:

Description    ErrorMessage
----------------------------
Description1   
Description2   
Description3   ErrorMessage3

The problem is that the "ErrorMessage" field doesn't exist in every subitem of VerificationItems.

I've attempted to use mvzip to combine all Descriptions into a single multivalue field, and do the same with all ErrorMessages, then recombine them using mvindex, as shown in the query below. This works well if the "ErrorMessage" field exists in every subitem. However, in my case it only appears when it has a value.

This is the query that almost works, but it mis-aligns the values:

spath VerificationItems{}.Description | rename VerificationItems{}.Description AS D 
| spath VerificationItems{}.ErrorMessage | rename VerificationItems{}.ErrorMessage as E 
| eval x=mvzip(D, E, ";;") | mvexpand x 
| eval x=split(x,";;") 
| eval Descr=mvindex(x,0) 
| eval ErrorMessage=mvindex(x,1)
| table _time, Descr, ErrorMessage

The current (and incorrect) end result is:

Description    ErrorMessage
----------------------------
Description1   ErrorMessage3   <-- ErrorMessage3 shows up incorrectly on the first line
Description2   
Description3   

...which you can see incorrectly aligns ErrorMessage3 with Description1.

I've tried using regex with max_match=0 on the 'VerificationItems' multivalue field without success. (I get zero results). I also attempted a FillNull solution, which also doesn't work as I expected it to.

Does the community have any suggestions on how to tweak this search to work correctly in the absence of that ErrorMessage field on every item?

Thank you in advance for your consideration of assistance.

[niketn · Accepted solution]

@blairmd, as @kamlesh_vaghela has mentioned,
1) You should better fix empty values in JSON data being logged rather than at search time mocking dummy value (enrichment of data after stats command is better than cooking up _raw data).
2) Also, you JSON does not seem to be formatted correctly in the question. Ideally Key Names and their corresponding String Values should be in double quotes and each KV pair should be comma separated.

Following is a run anywhere search based on corrected JSON data based on what you have provided. Double quotes have been escaped to dummy required data. You would need to replace first two pipes i.e. | makeresults and |eval _raw with your actual base search.

The first replace command is identifying nodes with only Description but no ErrorMessage (PS: In such situations the Description will be followed by end curly brace } instead of comma ",". Replace uses regular expression to find such instances and introduce dummy node , ErrorMessage: "NA". PS: I have used simple regex \w+ for identifying Descriptions however it has to be corrected by you based on your actual description pattern in your data. Once dummy ErrorDescription NA gets added your remaining search should work fine. I have added a final replace before displaying the field values of Description and ErrorMessage to convert NA to blank i.e. ""

|  makeresults
|  eval _raw="{ \"VerificationItems\": [
   {
     \"Description\": \"Description1\"    
   },
   {
     \"Description\": \"Description2\"
   },
   {
     \"Description\": \"Description3\",
     \"ErrorMessage\": \"ErrorMessage3\"
   }
 ]}
"
| eval _raw=replace(_raw,"(\"Description\":\s\"\w+\"\s+)(\})","\1,\"ErrorMessage\": \"NA\"\2")
| spath
| rename VerificationItems{}.Description AS D 
| rename VerificationItems{}.ErrorMessage AS E 
| eval x=mvzip(D, E, ";;") 
| mvexpand x 
| eval x=split(x,";;") 
| eval Description=mvindex(x,0) 
| eval ErrorMessage=mvindex(x,1)
| replace "NA" with "" in ErrorMessage
| table _time, Description, ErrorMessage

Please try out and confirm. Use https://regex101.com/ to test the Regular Expression to identify Description field and value.

[blairmd]

This is basically the approach I took. I do not have the ability to correct the JSON format - that is being generated by the Application Developers, who prioritize any adjustment of log data to be very low. So instead, I created a new regex to capture the value of any "ErrorMessage" node that exists alongside of the Description. Thanks for your time on this.

[kamlesh_vaghela]

Hi

I'm not sure but I think data showing incorrect due to "ErrorMessage" field not available with all "Description" fields.

I have tried with below sample json in which "ErrorMessage" field has BLANK (NO) value. In this case values extracted properly.

{"VerificationItems": [{"Description": "Description1","ErrorMessage": ""},{"Description": "Description2","ErrorMessage": ""},{"Description": "Description3","ErrorMessage": "ErrorMessage3"} ]}

I have tried with below search.

YOUR_SEARCH
| spath VerificationItems{}.Description 
| rename VerificationItems{}.Description AS Description 
| spath VerificationItems{}.ErrorMessage 
| rename VerificationItems{}.ErrorMessage as ErrorMessage 
| eval temp = mvzip(Description,ErrorMessage) | stats count by _time temp
| eval Description = mvindex(split(temp,","),0), ErrorMessage = mvindex(split(temp,","),1) 
| table _time Description,ErrorMessage

Is It possible to make a change in JSON data we received and add ErrorMessage BLANK when it is not available? Do you able to change and try above search?

Thanks

[maciep]

without dissecting the search too much, can you just create E if it doesn't exist after the spath and before the mvzip?

   | . . .
   | spath VerificationItems{}.ErrorMessage | rename VerificationItems{}.ErrorMessage as E 
   | eval E = coalesce(E,"")
   | eval x=mvzip(D, E, ";;") | mvexpand x | eval E = coalesce(E,"")
   | .....