Splunk Search

Help with Spath for Nested Json

neerajs_81
Builder

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 ?

neerajs_81_1-1660719516964.png

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

 

Labels (2)
Tags (1)
0 Karma
1 Solution

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

View solution in original post

0 Karma

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

bowesmana
SplunkTrust
SplunkTrust

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
Builder

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

| eval RecipField=mvindex(Value, index)

 

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Builder

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 :

neerajs_81_0-1660737823388.png

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
Builder

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

0 Karma

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

neerajs_81
Builder

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Builder

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...