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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...