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.
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.
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.
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.
I am not sure I understand what you are asking
Would this work?
| spath input=ServerRule
If not, why not?