Hi! I can't seem to figure out how to get a count of each operation in a document like below:
{ [-]
request_id: 12345
revision: 123
other_field: stuff
my_precious: { [-]
1648665400.774453: { [-]
keys: [ [-]
key:key1
]
op: operation_1
}
1648665400.7817056: { [-]
keys: [ [-]
key:key2
]
op: operation_2
}
1648665400.7847242: { [-]
keys: [ [-]
key:key4
]
op: operation_1
}
1648665400.7886434: { [-]
keys: [ [-]
key:key5
]
op: operation_3
}
1648665400.7932374: { [-]
keys: [ [-]
key:key3
]
op: operation_2
}
I want to be able to see the count of each operation. For example, the above would yield:
operation_1: 2
operation_2: 2
operation_3: 1
I've tried with the following rex, which is unreliable tbh, as there could be other documents with " op: ". But not even the following works...
| rex (?<opr>"(?<= op: )\w+") |stats count by opr |
Any help is appreciated!
What @ITWhisperer suggests is that if event is conformant JSON, you would have already fields like my_precious.1648665400.774453.op, my_precious.1648665400.7817056.op, etc; if not, apply "| spath". (C.f., spath. It is more reliable to use builtin functions to extract structured data.)
To count by op values, use mvexpand. Because your op fields have deep paths, you also need to flatten them with rename, then use foreach to consolidate them into a single field.
| rename my_precious.*.*.op as *_*_op ``` flatten paths ```
| foreach *_op ``` consolidate op ```
[eval my_op=mvappend(my_op, '<<FIELD>>')]
| mvexpand my_op
| stats count by my_op
With sample data, output is
my_op | count |
operation_1 | 2 |
operation_2 | 2 |
operation_3 | 1 |
Give this a try
| rex max_match=0 "op:\s*(?<opr>\w+)" |stats count by opr
OR
| rex max_match=0 "\"op\":\s*\"(?<opr>[^\"]+)" |stats count by opr
This looks like it might be JSON except that you have shown the formatted version. Is it actually JSON? If so, can you share the raw JSON from the event in a code block (as you did with the formatted JSON)?
Is there any way to do this with rex? For example, I've tried
| rex (?<opr>"(?<=op:) \w+")
which should match any word after "op: ", no? But it's not returning any results for some reason.
rex works (by default) on the _raw field. What you have shared appears to be formatted JSON. Please can you confirm that your events have unformatted JSON or formatted JSON?
The event is formatted as JSON
What @ITWhisperer suggests is that if event is conformant JSON, you would have already fields like my_precious.1648665400.774453.op, my_precious.1648665400.7817056.op, etc; if not, apply "| spath". (C.f., spath. It is more reliable to use builtin functions to extract structured data.)
To count by op values, use mvexpand. Because your op fields have deep paths, you also need to flatten them with rename, then use foreach to consolidate them into a single field.
| rename my_precious.*.*.op as *_*_op ``` flatten paths ```
| foreach *_op ``` consolidate op ```
[eval my_op=mvappend(my_op, '<<FIELD>>')]
| mvexpand my_op
| stats count by my_op
With sample data, output is
my_op | count |
operation_1 | 2 |
operation_2 | 2 |
operation_3 | 1 |
Thanks for this. Is there a way to avoid empty values? Empty values are accounting for most of the results.
What do you mean by "empty" values? If it is an empty string (as opposed to the field not being there), you could add an if condition to the eval
| foreach *_op ``` consolidate op ```
[eval my_op=if('<<FIELD>>'=="",my_op,mvappend(my_op, '<<FIELD>>'))]