topic Re: How to count distinct items in nested fields? in Splunk Search

How to count distinct items in nested fields?

aj_54321 — Wed, 30 Mar 2022 20:21:26 GMT

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!

Re: How to count distinct items in nested fields?

ITWhisperer — Wed, 30 Mar 2022 21:25:31 GMT

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

Re: How to count distinct items in nested fields?

aj_54321 — Thu, 31 Mar 2022 15:55:36 GMT

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.

Re: How to count distinct items in nested fields?

ITWhisperer — Thu, 31 Mar 2022 18:07:13 GMT

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?

Re: How to count distinct items in nested fields?

aj_54321 — Thu, 31 Mar 2022 20:53:14 GMT

The event is formatted as JSON

Re: How to count distinct items in nested fields?

somesoni2 — Thu, 31 Mar 2022 21:00:24 GMT

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

Re: How to count distinct items in nested fields?

yuanliu — Fri, 01 Apr 2022 09:03:53 GMT

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

Re: How to count distinct items in nested fields?

aj_54321 — Mon, 04 Apr 2022 15:04:24 GMT

Thanks for this. Is there a way to avoid empty values? Empty values are accounting for most of the results.

Re: How to count distinct items in nested fields?

ITWhisperer — Mon, 04 Apr 2022 15:13:00 GMT

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