How to parse nested JSON in Office 365 logging

[korstiaan]

Hi All, I hope someone is able to help me resolve an issue that I have with some nested fields in JSON. I'd like to get the data out of the 'Parameters' field.

The data is in CSV format and the JSON is in the field AuditData. I use the spath command to get most of the fields with the following search:

index=auditlog |spath input=AuditData

Most of the fields get extracted, however there is nested json in the 'Parameters' field. An when I use the spath command it will create two new fields:

Parameters{}.Name
Parameters{}.Value

Parameters{}.Name contains, 'SentTo', 'ModerateMessageByUser' etc.
Parameters{}.Value contains the values belonging to the above names.

However what I'd like to get is a field with the Parameter Name and the accompanying Parameter Value e.g:
SentTo(Fieldname) this is a test (Value)
ModerateMessageByUser(Fieldname)John Doe (Value)

Hopefully this makes sense i've added an example event below:

"outlook.office365.com","7c06ff67-4425-4eb6-9df5-a7e9b0a07fa0","False","ExchangeAdmin","1/25/2019 1:54:50 PM","test@test.onmicrosoft.com","New-TransportRule","{""CreationTime"":""2019-01-25T13:54:50"",""Id"":""4f438b66-64b2-4291-0dec-08d682ccad1c"",""Operation"":""New-TransportRule"",""OrganizationId"":""f2b20553-47ca-41c0-9766-fba93daf6cf1"",""RecordType"":1,""ResultStatus"":""True"",""UserKey"":""1003200036269B2E"",""UserType"":2,""Version"":1,""Workload"":""Exchange"",""ClientIP"":""1.1.1:42796"",""ObjectId"":"""",""UserId"":""test@test.onmicrosoft.com"",""ExternalAccess"":false,""OrganizationName"":""test.onmicrosoft.com"",""OriginatingServer"":""AM6PR04MB6088 (15.20.1558.000)"",""Parameters"":[{""Name"":""SentTo"",""Value"":""this is a test""},{""Name"":""ModerateMessageByUser"",""Value"":""John Doe""},{""Name"":""Name"",""Value"":""Forward_Mails""},{""Name"":""StopRuleProcessing"",""Value"":""False""},{""Name"":""Mode"",""Value"":""Enforce""},{""Name"":""Comments"",""Value"":""""},{""Name"":""RuleErrorAction"",""Value"":""Ignore""},{""Name"":""SenderAddressLocation"",""Value"":""Header""}],""SessionId"":""8240fe03-0507-4794-99c3-e601796be84b""}","77","123","4f438b66-64b2-4291-0dec-08d682ccad1c","True","Unchanged"

[kamlesh_vaghela · Accepted solution]

@korstiaan

Can you please try this?

YOUR_SEARCH | rename Parameters{}.* as * |  eval temp=mvzip(Name,Value) | mvexpand temp | eval Name=mvindex(split(temp,","),0), Value=mvindex(split(temp,","),1) | table Name Value

I have created a sample search on the bases of Parameters JSON. It will help you to understand the search.

| makeresults | eval _raw="{\"Parameters\":[{\"Name\":\"SentTo\",\"Value\":\"this is a test\"},{\"Name\":\"ModerateMessageByUser\",\"Value\":\"John Doe\"},{\"Name\":\"Name\",\"Value\":\"Forward_Mails\"},{\"Name\":\"StopRuleProcessing\",\"Value\":\"False\"},{\"Name\":\"Mode\",\"Value\":\"Enforce\"},{\"Name\":\"Comments\",\"Value\":\"\"},{\"Name\":\"RuleErrorAction\",\"Value\":\"Ignore\"},{\"Name\":\"SenderAddressLocation\",\"Value\":\"Header\"}]}" | kv | rename Parameters{}.* as * |  eval temp=mvzip(Name,Value) | mvexpand temp | eval Name=mvindex(split(temp,","),0), Value=mvindex(split(temp,","),1) | table Name Value

UPDATED

YOUR_SEARCH | rename Parameters{}.* as * |  eval temp=mvzip(Name,Value) | mvexpand temp | eval Name=mvindex(split(temp,","),0), Value=mvindex(split(temp,","),1) | eval {Name}=Value | stats values(*) as * by _time Id

Sample:

| makeresults 
| eval _raw="{\"Id\":\"4f438b66-64b2-4291-0dec-08d682ccad1c\", \"Parameters\":[{\"Name\":\"SentTo\",\"Value\":\"this is a test\"},{\"Name\":\"ModerateMessageByUser\",\"Value\":\"John Doe\"},{\"Name\":\"Name\",\"Value\":\"Forward_Mails\"},{\"Name\":\"StopRuleProcessing\",\"Value\":\"False\"},{\"Name\":\"Mode\",\"Value\":\"Enforce\"},{\"Name\":\"Comments\",\"Value\":\"\"},{\"Name\":\"RuleErrorAction\",\"Value\":\"Ignore\"},{\"Name\":\"SenderAddressLocation\",\"Value\":\"Header\"}]}" 
| kv 
| rename Parameters{}.* as * 
| eval temp=mvzip(Name,Value) 
| mvexpand temp 
| eval Name=mvindex(split(temp,","),0), Value=mvindex(split(temp,","),1) 
| eval {Name}=Value
| stats values(*) as * by _time Id

Thanks

[korstiaan]

Hi, this is almost what I want thank you. So now you have a field called Name and Value with the correct information. However I'd like that the Paramater.{}Name will be the field name and the Value will be the value of that specific field e.g.

SentTo is a field and it contains the value 'this is a test'
ModerateMessageByUser is a field and it contains the value 'John Doe'

Does that make sense?

[kamlesh_vaghela]

@korstiaan

Please check the updated answer.

[korstiaan]

Thanks that will work! Can you explain to me how the mvzip and mvexpand work for the above search.

[kamlesh_vaghela]

@korstiaan

Here, your single event has below multivalued fields, Parameters{}.Name and Parameters{}.Value. If we want to get the related value of each value, means the first set of value from Parameters{}. JSON, then we use mvzip by passing the required fields. In our case, Parameters{}.Name and Parameters{}.Value.

mvzip: This function takes two multivalue fields, X and Y, and combines them by stitching together the first value of X with the first value of field Y, then the second with the second, and so on.

https://docs.splunk.com/Documentation/Splunk/7.2.5/SearchReference/MultivalueEvalFunctions#mvzip.28X...

After doing mvzip, now we have multi-valued filed temp. Here we are using mvexpand to separate them.

mvexpand: Expands the values of a multivalue field into separate events, one event for each value in the multivalue field.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/mvexpand

After mvexpand, your single event expanding with multiple events.

Here, I've used dynamic eval by using {}. | eval {Name}=Value is dynamic eval which is creating a field with the name which is available in Name filed as a value. In this case, SentTo will be created.

I suggest you execute my sample search step by step and you will get how it is working.

Happy Splunking

[cpaul8]

Hi @kamalesh,

Thanks. As we discussed in the slack channel, I tried the search, but it creates additional events for each Name:VAlue pairs. can this be addressed please?

[korstiaan]

Thank you Kamlesh, very helpful.