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

[sohaibomar]

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

[niketn · Accepted solution]

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