Help with Spath for Nested Json

[neerajs_81]

Hi All, Can someone pls assist me in extracting the different Recipients out this nested Json ?  This is from O365 logs.    I have followed https://community.splunk.com/t5/Getting-Data-In/Extract-nested-json/m-p/496227#M84641  but unable to get it work against my data.

Raw events:

OperationProperties: [ [-]
     { [+]
     }
     { [-]
       Name: RuleId
       Value: 3623734839020093442
     }
     { [-]
       Name: RuleName
       Value: ForwardingRule01
      } 
     { [+]
     }
     { [-]
       Name: RuleActions
       Value: [{"ActionType":"Forward","Recipients":["WADRIANL@domain.com","WENDYLIM@domain.com", Forward Flags":"None"}]
     }
   ]

Note, Splunk is able to extract the field OperationProperties{}.Value as shown below but how to further extract the list of Recipients within it ?

I am trying below searches but no luck 

| spath output=Recipients path=OperationProperties{}.Value.Recipients

OR

| spath output=Recipients path=OperationProperties{}.Value{}.Recipients{}

i am +ve i am making a mistake in the path variable above. 


Thanks in advance

[ITWhisperer · Accepted solution]

| spath OperationProperties{} output=OP
| mvexpand OP
| spath input=OP
| where Name="RuleActions"
| spath input=Value
| rename {}.* as *

[bowesmana]

Not totally sure about your data, but this search, which uses your example data extracts Recipients. That field is a string value of the Value field of the RuleActions array element, so I've done this

| makeresults
| eval _raw="{
    \"OperationProperties\": [
        {
            \"Name\": \"RuleId\",
            \"Value\": \"3623734839020093442\"
        },
        {
            \"Name\": \"RuleName\",
            \"Value\": \"ForwardingRule01\"
        },
        {
            \"Name\": \"RuleActions\",
            \"Value\": \"[{\\\"ActionType\\\":\\\"Forward\\\",\\\"Recipients\\\":[\\\"WADRIANL@domain.com\\\",\\\"WENDYLIM@domain.com\\\"], \\\"Forward Flags\\\":\\\"None\\\"}]\"
        }
    ]
}"
| spath
| rename OperationProperties{}.Value as Value, OperationProperties{}.Name as Name
| eval index=mvfind(Name, "RuleActions")
| eval RecipField=mvindex(Value, index)
| spath input=RecipField
| rename {}.Recipients{} as Recipients

The mvfind looks for the array offset for the RuleActions in the Name field and then graps the corresponding array element of the Value field and spaths that array. Then it finally grabs the Recipients.

Hope this is useful

[neerajs_81]

Not sure why, but this line fails to create a new field RecipField .  Checking further.

| eval RecipField=mvindex(Value, index)

[ITWhisperer]

This didn't work because in the real data one of the OperationProperties has a Name but no Value, which throws out the indexing.

[bowesmana]

Check what comes back from the mvfind - if it's null, it means that the text could not be found in the multivalue extracted data.

Best is to show _raw data, as the pretty printing of JSON will be hiding all the quotes - that nested data is probably not part of the JSON itself, so you will have to parse the whole Value string to JSON to then get the real recipients out and presumably that data will appear as _one_ of the array elements with the RuleActions name.

[neerajs_81]

As clarified by @ITWhisperer , the mvindex didn't work due to indexing order issue. So If i explicitly use something like below , the RecipField gets created

| eval RecipField=mvindex('OperationProperties{}.Value',5)

 Value :

i now  need to come up with a  rex using mode=sed to remove all those spl characters above  and make it display those recipient email addresses only

[bowesmana]

Do you mean that the RecipField now contains the full encapsulated JSON.

You would be able to use the 

| spath input=RecipField

to then get out the array elements of that encapsulated JSON.

An alternative to using a fixed array element offset in the mvindex, you could use this to 'find' the Recipients JSON data from the Value using mvmap, which will return the contents of the Value field if it contains the word Recipients

...
| rename OperationProperties{}.Value as Value, OperationProperties{}.Name as Name
| eval RuleActionJSON=mvmap(Value, if(match(Value, "Recipients"), Value, RuleActionJSON))
| spath input=RuleActionJSON 
| rename {}.Recipients{} as Recipients

[neerajs_81]

Thank you. Awarded you karma points for all your replies.

[ITWhisperer]

| spath output=Recipients path=OperationProperties{}.Value{}.Recipients{}

[neerajs_81]

Thank you for responding. Even this is not working although it does seem to be correct given that both Value and Recipients are nested arrays. Really odd.

[ITWhisperer]

Can you share your raw event rather than the formatted version?

[yuanliu]

Use single quote around field names containing special characters, e.g.,

| spath output=Recipients path='OperationProperties{}.Value.Recipients'

Recent SPL versions also include a group of JSON functions such as json_array_to_mv(), e.g.,

| eval Recipients=json_array_to_mv('OperationProperties{}.Value.Recipients'​)

[neerajs_81]

Thank you for responding.  Neither of the 2 options are working. Don't see my output field Recipients getting created. I also tried 

| spath output=Recipients path='OperationProperties{}.Value{}.Recipients{}'

I will play around with the function you suggested.