Splunk Search

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

splunk_skr
Explorer

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?

0 Karma

gokadroid
Motivator

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"

0 Karma

splunk_skr
Explorer

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

0 Karma

gokadroid
Motivator

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.

0 Karma

splunk_skr
Explorer

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.

0 Karma

gokadroid
Motivator

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
0 Karma

splunk_skr
Explorer

I guess you meant

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

This works.

gokadroid
Motivator

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...