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