topic Re: How can I count mv fields by type? in Splunk Search

How can I count mv fields by type?

akshaysaraf — Tue, 03 Mar 2020 01:11:19 GMT

My data looks like:

{
 parent_id: 1
 child_info: [
             {
               id: 123,
               status: "PASS"
             },
             {
               id: 456,
               status: "FAIL"
             }
          ]
}

I am trying to print the result in format:

parent ID  |  Child_Passed_count  |  Child_Failed_count
 1                 1                           1

Re: How can I count mv fields by type?

anmolpatel — Wed, 30 Sep 2020 04:23:28 GMT

| makeresults 
| eval jsonTest = "{
  \"parent_id\": 1,
  \"child_info\": [
              {
                \"id\": \"123\",
                \"status\": \"PASS\"
                      },
              {
                \"id\": \"456\",
                \"status\": \"FAIL\"
                      },
                  {
                \"id\": \"457\",
                \"status\": \"FAIL\"
                      }
      ]
 }" 
| spath input=jsonTest path=parent_id output=parent_id 
| spath input=jsonTest output=id_initial path=child_info{}.id 
| spath input=jsonTest output=status_initial path=child_info{}.status 
| eval val = mvzip(id_initial, status_initial) 
| mvexpand val
| eval mvVal = split(val, ",") 
| eval id = mvindex(mvVal, 0) 
| eval status = mvindex(mvVal, 1) 
| stats count(eval(status=="PASS")) as Child_Passed_count count(eval(status=="FAIL")) as Child_Failed_count by parent_id

parent_id Child_Passed_count Child_Failed_count
1 1 2

Re: How can I count mv fields by type?

akshaysaraf — Tue, 03 Mar 2020 05:07:13 GMT

Thank you, but in my production server I have about 500,000 events which means 500,000 parent ID's, and each parent has about 200 child records. When I mvexpand for such fields I run out of memory and the search truncates my result. Is there a way of doing it without mvexpand?

Re: How can I count mv fields by type?

sumanssah — Tue, 03 Mar 2020 05:51:30 GMT

Try using this

<your base search>
  | rex "(?<json>\{.+)" | spath input=json | fields - json
  | rename child_info{}.id AS child_id child_info{}.status AS child_status
  | mvexpand child_status
  | stats count(eval(child_status=="PASS")) as Child_Passed_count count(eval(child_status=="FAIL")) as Child_Failed_count by parent_id

Re: How can I count mv fields by type?

woodcock — Tue, 03 Mar 2020 06:09:13 GMT

This is another case of FIX YOUR BROKEN JSON. Here is how you can manually fix it but really, get it fixed at the source:

| makeresults 
| eval _raw = "{
   parent_id: 1
   child_info: [
               {
                 id: 123,
                 status: \"PASS\"
                           },
               {
                 id: 456,
                 status: \"FAIL\"
                           },
              {
                 id: 457,
                 status: \"FAIL\"
                           }
       ]
  }" 

| rename COMMENT AS "Everything above generates BROKEN JSON; everything below is your solution"

| rex mode=sed "s/([\r\n\s]+)([^\r\n\s\"]+):/\1\"\2\":/g s/([^{\[\r\n\s:,])([\r\n\s]+\")/\1,\2/g"
| kv
| chart count BY parent_id child_info{}.status

Re: How can I count mv fields by type?

akshaysaraf — Tue, 03 Mar 2020 23:30:37 GMT

Actually I didn't add double quotes while asking this question, the data in production server does have it.
I was trying it with stats, but wasn't giving expected result. Thank You for you help.

Re: How can I count mv fields by type?

woodcock — Wed, 04 Mar 2020 04:23:57 GMT

That would have saved me 90% of my effort.