How to parse my sample JSON data to get a stats count grouped by a certain value?

[splunk_skr]

For the json below:

{"key5":"Thu Nov 03 08:34:19 CDT 2016","key1":"123456","key2":"{\"key21\":\"(123)-456-7890\",\"key22\":\"valueForkey22 \",\"key23\":\"valueForkey23\"}","key3":"valueForkey3","key4":"11/04/2016 00:00:00 CDT"}

The basic stats count search gets the total number of times an entry is present within index

index="indexName" source="*fileName*" "*key3*" | stats count

I want to get the data grouped by "valueForkey3". e.g. If my log file has 10 rows, and each of those rows have 5 distinct "key3" , the values would be 2 each. i.e. 5 distinct "key3" present "2" times each. How do I do this?

[gokadroid]

Extract that value from your json using regex and then count on that extracted field like:

yourBaseQuery to return you json
| rex "\,\"key3\"\:\"(?<k3Value>[^\"]+)\"\,\".*"
| stats count by k3Value

Since the term k3Value in extracted field might be confusing so renaming it as per comments and considering updated json

yourBaseQuery to return you json
| rex "\,\"key3\"\s*:\s*\"(?<k3Field>[^\"]+)\"\,\".*"
| stats count by k3Field

*Updating based on the comments: *

yourBaseQuery to return json
| rex max_match=0 "\"(?<key>\w+)\"\s*\:\s*(?<value>[^,]+)"
| eval z=mvzip(key, value, "~") | mvexpand z | rex field=z "(?<key>[^~]+)~(?<value>.*)" | table key value

If above gets you the table of key and value, in the end of the above query just append below which should give you result
stats count by value | where key="key3"

[splunk_skr]

Thanks. The values are dynamic though. Not sure how this would work.

I managed to use the chart function and get the results index="indexName" source="*fileName*" "*key3*" | chart count by key3

[gokadroid]

what do u mean by values are dynamic...? you mean this string "key3" is dynamic? Can you please paste some actual samples and highlight the terms you want to do a grouping on? if key3 is already an extracted field where key3=value then your command should have worked.

[splunk_skr]

My apologies. JSON was not formatted properly.

 {
      "key1": "123456",
      "key2": "{\"key21\":\"(123)-456-7890\",\"key22\":\"valueForkey22 \",\"key23\":\"valueForkey23\"}",
      "key3": "valueForkey3",
      "key4": "11/04/2016 00:00:00 CDT",
      "key5": "Thu Nov 03 08:34:19 CDT 2016"
    }

I wanted to group by key3. so all distinct values that appear under key3 and their count.

[gokadroid]

Can you try this query and see if you get results or not as I have put in the regex based on the new json given:

| makeresults |eval x="{
       \"key1\": \"123456\",
       \"key2\": \"{\"key21\":\"(123)-456-7890\",\"key22\":\"valueForkey22 \",\"key23\":\"valueForkey23\"}\",
       \"key3\": \"valueForkey3\",
       \"key4\": \"11/04/2016 00:00:00 CDT\",
       \"key5\": \"Thu Nov 03 08:34:19 CDT 2016\"
     }
"
| rex field=x "\"key3\"\s*\:\s*\"(?<k3Value>[^\"]+)\"\,"
| stats count by k3Value

and if you do then there shouldn't be any reason why you don't get the results out of

yourBase query
| rex "\"key3\"\s*\:\s*\"(?<k3Value>[^\"]+)\"\,"
| stats count by k3Value

[splunk_skr]

I guess you meant

| rex "\,\"key3\"\:\"(?<key3>[^\"]+)\"\,\".*"
 | stats count by key3

This works.

[gokadroid]

actually in my rex the value where I stroed was ?<k3Value> hence you should have used that name | stats count by k3Value and not actually the value of key3. Now since you have changed it to ?<key3> so yes this query will work. I think what confused you was I called my extraction as "k3Value" and you assumed it to be the value of key3 rather than using the fieldname which was k3Value. So if it works so please accept the answer and upvote.