Splunk Search

How to extract fields from json and apply stats to plot a bar graph?

sohaibomar
Explorer

I have JSON formatted data in event as below:

{
"stats":
[
{"name":"Facebook", "count":50},
{"name":"yahoo", "count":80},
{"name":"gmail", "count":0},
{"name":"hotmail", "count":8}]}

For each stats.name, I want to get Top 2 max count and show in Bar or Column graph.

I am using following search:

...| rename stats{}.name AS Category, stats{}.value AS Value | stats sum(Value) BY Category

Above search gives same count for each stats.name.
for ex:

Name                 Count
Facebook           138
yahoo                  138
gmail                   138
hotmail                138

but i want result like:

Name                 Count
Facebook           50
yahoo                  80

Thanks in Advance

0 Karma
1 Solution

niketn
Legend

Your JSON data is fetching multivalued results which you will have to convert to single value first.

You can try the following run anywhere search. The pipes till spath generate the sample data as per your question (you do not require them when testing against your Splunk data).

| makeresults
| eval jsonData = "{
 \"stats\":
 [
 {\"name\":\"Facebook\", \"count\":50},
 {\"name\":\"yahoo\", \"count\":80},
 {\"name\":\"gmail\", \"count\":0},
 {\"name\":\"hotmail\", \"count\":8}]}"
| spath input=jsonData path=stats{}.name output=Name
| spath input=jsonData path=stats{}.count output=Count
| eval NameCount=mvzip(Name,Count)
| mvexpand NameCount
| eval mvNameCount=split(NameCount,",")
| eval Name=mvindex(mvNameCount,0)
| eval Count=mvindex(mvNameCount,1)
| table Name Count
| stats sum(Count) as Count by Name

For details on multivalue evaluation functions/commands like mvzip(), mvexpand, mvindex() and split() refer to documentation and related topics: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

Your JSON data is fetching multivalued results which you will have to convert to single value first.

You can try the following run anywhere search. The pipes till spath generate the sample data as per your question (you do not require them when testing against your Splunk data).

| makeresults
| eval jsonData = "{
 \"stats\":
 [
 {\"name\":\"Facebook\", \"count\":50},
 {\"name\":\"yahoo\", \"count\":80},
 {\"name\":\"gmail\", \"count\":0},
 {\"name\":\"hotmail\", \"count\":8}]}"
| spath input=jsonData path=stats{}.name output=Name
| spath input=jsonData path=stats{}.count output=Count
| eval NameCount=mvzip(Name,Count)
| mvexpand NameCount
| eval mvNameCount=split(NameCount,",")
| eval Name=mvindex(mvNameCount,0)
| eval Count=mvindex(mvNameCount,1)
| table Name Count
| stats sum(Count) as Count by Name

For details on multivalue evaluation functions/commands like mvzip(), mvexpand, mvindex() and split() refer to documentation and related topics: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Get Updates on the Splunk Community!

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 ...

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, ...