Why do results from extraction of multivalued fields contain fields missing in each statements?

[wvalente2]

Hi all,

I need your help with a query to extract the values of fields with multiple values.

The problem I'm facing is that not every JSON structure has the two values that I need to extract (Name and Value).

Below is an example of the log:

"OperationProperties": [{
"Name": "Actions",
"Value": "XX"
}, {
"Name": "Conditions",
"Value": "XX"
}, {
"Name": "Provider",
"Value": "XX"
}, {
"Name": "RemoveOutlookRuleBlob"
}, {
"Name": "Name",
"Value": "XX"
}, {
"Name": "IsNew"
}, {
"Name": "IsDirty",
"Value": "XX"
}, {
"Name": "RuleOperation",
"Value": "XX"
}, {
"Name": "ServerRule",
"Value": "XX"
}],

The fields 'Name: IsNew' and 'Name:RemoveOutlookRuleBlob' do not have the corresponding 'Value:' field.

I tried the following search, but I noticed that when the 'Value' field doesn't exist, it aggregates with the next available 'Value' field.

base search....
| spath path=OperationProperties{}.Name output=Name
| spath path=OperationProperties{}.Value output=Value
| eval temp=mvzip(Name, Value)
| table Name Value, temp

temp

Actions,ForwardToRecipientsAction

Conditions,SentToRecipientsCondition,FromRecipientsCondition

Provider,RuleOrganizer

RemoveOutlookRuleBlob,XXX

Name,True

IsNew,Delete

IsDirty, XX

*The 'IsNew' field does not have 'True' value, as you can see in the first image.

My final search will looks like this after I correct the Name=Value.

base search...
| spath path=OperationProperties{}.Name output=Name
| spath path=OperationProperties{}.Value output=Value
| eval temp=mvzip(Name, Value)
| mvexpand temp
| eval Name=mvindex(split(temp,","),0), Value=mvindex(split(temp,","),1), Value=mvindex(split(temp,","),2)
| eval {Name}=Value
| stats values(*) as * by _time Id

Can I have any solutions here?

Thank you.

[ITWhisperer · Accepted solution]

base search...
| spath OperationProperties{} output=OperationProperties
| mvexpand OperationProperties
| spath input=OperationProperties
| fields - OperationProperties
| fillnull value="true" Value
| rename Name as _Name Value as _Value
| eval {_Name}=_Value
| stats values(*) as * by _time Id

Use a fillnull value with something you can either always use as a default or something you can always recognised as having been introduced by fillnull so it can be removed/changed later.

[wvalente2]

@ITWhisperer 

This works like a charm. Thank you so much.

Just one more question.

When I ran the query you proposed, the 'ServerRule' field presents the values in JSON format. (This format is shown in the raw log).

I tried to parse the values with 'spath' but no success at all.

Is there a way to parse this field as well without affecting the results that we achieve?
ServerRule

{"ID": XX,"IDx":"XX","ExecutionSequence":XX,"Level":XX,"StateFlags":XX,"UserFlags":XX,"Condition":{"Restrictions":[{"Values":[{"PropTag":{},"PropType":XX,"Value":XX,"RawValue":XX},{"PropTag":{},"PropType":XX,"Value":"XX"},{"PropTag":{},"PropType":31,"Value":"XX","RawValue":"XX

Thank you for your help.

[ITWhisperer]

I am not sure I understand what you are asking

Would this work?

| spath input=ServerRule

If not, why not?