topic Re: Spath for Nested Json in Splunk Search

Help with Spath for Nested Json

neerajs_81 — Wed, 17 Aug 2022 14:42:25 GMT

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

Re: Spath for Nested Json

yuanliu — Wed, 17 Aug 2022 07:23:57 GMT

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')

Re: Spath for Nested Json

ITWhisperer — Wed, 17 Aug 2022 07:26:41 GMT

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

Re: Spath for Nested Json

neerajs_81 — Wed, 17 Aug 2022 07:30:07 GMT

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.

Re: Spath for Nested Json

bowesmana — Wed, 17 Aug 2022 07:31:51 GMT

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

Re: Spath for Nested Json

neerajs_81 — Wed, 17 Aug 2022 07:33:35 GMT

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.

Re: Spath for Nested Json

ITWhisperer — Wed, 17 Aug 2022 07:39:03 GMT

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

Re: Spath for Nested Json

neerajs_81 — Wed, 17 Aug 2022 07:50:38 GMT

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

| eval RecipField=mvindex(Value, index)

Re: Spath for Nested Json

bowesmana — Wed, 17 Aug 2022 08:49:01 GMT

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.

Re: Spath for Nested Json

ITWhisperer — Wed, 17 Aug 2022 09:02:32 GMT

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

Re: Spath for Nested Json

ITWhisperer — Wed, 17 Aug 2022 09:04:22 GMT

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

Re: Spath for Nested Json

neerajs_81 — Wed, 17 Aug 2022 12:26:53 GMT

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

Re: Spath for Nested Json

bowesmana — Wed, 17 Aug 2022 23:58:45 GMT

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

Re: Spath for Nested Json

neerajs_81 — Thu, 18 Aug 2022 07:12:26 GMT

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