How to I sum the count with Category on specific field name and create a bar graph?

[chiraggl]

We get JSON data in which we have to calculate the sum of the count of all Categories and create a bar graph with specific field names (i.e. Warn, good..). Please check the data format as below:

JSON Data:

{
"links": {
"previous": null,
"next": null
},
"count": 7,
"results": [
{
"date": "2019-12-24",
"grade": null,
"counts": [
{
"count": 2123,
"category": "warn"
},
{
"count": 4353,
"category": "neutral"
},
{
"count": 9170,
"category": "good"
},
{
"count": 169,
"category": "bad"
},
{
"count": 507,
"category": "fair"
}
]
},
{
"date": "2019-11-30",
"grade": null,
"counts": [
{
"count": 1905,
"category": "warn"
},
{
"count": 4365,
"category": "neutral"
},
{
"count": 8463,
"category": "good"
},
{
"count": 143,
"category": "bad"
},
{
"count": 496,
"category": "fair"
}
]
},
{
"date": "2019-10-31",
"grade": null,
"counts": [
{
"count": 2367,
"category": "warn"
},
{
"count": 4373,
"category": "neutral"
},
{
"count": 9566,
"category": "good"
},
{
"count": 150,
"category": "bad"
},
{
"count": 647,
"category": "fair"
}
]
},
{
"date": "2019-09-30",
"grade": null,
"counts": [
{
"count": 2472,
"category": "warn"
},
{
"count": 6276,
"category": "neutral"
},
{
"count": 10281,
"category": "good"
},
{
"count": 185,
"category": "bad"
},
{
"count": 718,
"category": "fair"
}
]
},
{
"date": "2019-08-31",
"grade": null,
"counts": [
{
"count": 2439,
"category": "warn"
},
{
"count": 6283,
"category": "neutral"
},
{
"count": 10257,
"category": "good"
},
{
"count": 188,
"category": "bad"
},
{
"count": 749,
"category": "fair"
}
]
},
{
"date": "2019-07-31",
"grade": null,
"counts": [
{
"count": 141,
"category": "warn"
},
{
"count": 4420,
"category": "neutral"
},
{
"count": 10770,
"category": "good"
},
{
"count": 191,
"category": "bad"
},
{
"count": 2438,
"category": "fair"
}
]
},
{
"date": "2019-06-30",
"grade": null,
"counts": [
{
"count": 129,
"category": "warn"
},
{
"count": 4383,
"category": "neutral"
},
{
"count": 10639,
"category": "good"
},
{
"count": 199,
"category": "bad"
},
{
"count": 2567,
"category": "fair"
}
]
}
]
}

[to4kawa]

| makeresults 
| eval _raw="{\"links\":{\"previous\":null,\"next\":null},\"count\":7,\"results\":[{\"date\":\"2019-12-24\",\"grade\":null,\"counts\":[{\"count\":2123,\"category\":\"warn\"},{\"count\":4353,\"category\":\"neutral\"},{\"count\":9170,\"category\":\"good\"},{\"count\":169,\"category\":\"bad\"},{\"count\":507,\"category\":\"fair\"}]},{\"date\":\"2019-11-30\",\"grade\":null,\"counts\":[{\"count\":1905,\"category\":\"warn\"},{\"count\":4365,\"category\":\"neutral\"},{\"count\":8463,\"category\":\"good\"},{\"count\":143,\"category\":\"bad\"},{\"count\":496,\"category\":\"fair\"}]},{\"date\":\"2019-10-31\",\"grade\":null,\"counts\":[{\"count\":2367,\"category\":\"warn\"},{\"count\":4373,\"category\":\"neutral\"},{\"count\":9566,\"category\":\"good\"},{\"count\":150,\"category\":\"bad\"},{\"count\":647,\"category\":\"fair\"}]},{\"date\":\"2019-09-30\",\"grade\":null,\"counts\":[{\"count\":2472,\"category\":\"warn\"},{\"count\":6276,\"category\":\"neutral\"},{\"count\":10281,\"category\":\"good\"},{\"count\":185,\"category\":\"bad\"},{\"count\":718,\"category\":\"fair\"}]},{\"date\":\"2019-08-31\",\"grade\":null,\"counts\":[{\"count\":2439,\"category\":\"warn\"},{\"count\":6283,\"category\":\"neutral\"},{\"count\":10257,\"category\":\"good\"},{\"count\":188,\"category\":\"bad\"},{\"count\":749,\"category\":\"fair\"}]},{\"date\":\"2019-07-31\",\"grade\":null,\"counts\":[{\"count\":141,\"category\":\"warn\"},{\"count\":4420,\"category\":\"neutral\"},{\"count\":10770,\"category\":\"good\"},{\"count\":191,\"category\":\"bad\"},{\"count\":2438,\"category\":\"fair\"}]},{\"date\":\"2019-06-30\",\"grade\":null,\"counts\":[{\"count\":129,\"category\":\"warn\"},{\"count\":4383,\"category\":\"neutral\"},{\"count\":10639,\"category\":\"good\"},{\"count\":199,\"category\":\"bad\"},{\"count\":2567,\"category\":\"fair\"}]}]}" 
| rename COMMENT as "This is sample of your search, index=x"
| eval category=spath(_raw,"results{}.counts{}.category")
| eval count=spath(_raw,"results{}.counts{}.count")
| eval _counter=mvrange(0,mvcount(category))
| stats list(*) as * by _counter
| foreach * 
    [eval <<FIELD>> = mvindex(<<FIELD>>,_counter)]
| stats sum(count) as count by category
| sort - count
| transpose header_field=category column_name=category

I tried to be kind to memory.
try Visualization > Bar Chart

[vnravikumar]

Hi

Check this

| makeresults 
| eval temp="{
    \"links\": {
        \"previous\": null,
        \"next\": null
    },
    \"count\": 7,
    \"results\": [{
            \"date\": \"2019-12-24\",
            \"grade\": null,
            \"counts\": [{
                    \"count\": 2123,
                    \"category\": \"warn\"
                                },
                {
                    \"count\": 4353,
                    \"category\": \"neutral\"
                                },
                {
                    \"count\": 9170,
                    \"category\": \"good\"
                                },
                {
                    \"count\": 169,
                    \"category\": \"bad\"
                                },
                {
                    \"count\": 507,
                    \"category\": \"fair\"
                                }
            ]
        },
        {
            \"date\": \"2019-11-30\",
            \"grade\": null,
            \"counts\": [{
                    \"count\": 1905,
                    \"category\": \"warn\"
                                },
                {
                    \"count\": 4365,
                    \"category\": \"neutral\"
                                },
                {
                    \"count\": 8463,
                    \"category\": \"good\"
                                },
                {
                    \"count\": 143,
                    \"category\": \"bad\"
                                },
                {
                    \"count\": 496,
                    \"category\": \"fair\"
                                }
            ]
        },
        {
            \"date\": \"2019-10-31\",
            \"grade\": null,
            \"counts\": [{
                    \"count\": 2367,
                    \"category\": \"warn\"
                                },
                {
                    \"count\": 4373,
                    \"category\": \"neutral\"
                                },
                {
                    \"count\": 9566,
                    \"category\": \"good\"
                                },
                {
                    \"count\": 150,
                    \"category\": \"bad\"
                                },
                {
                    \"count\": 647,
                    \"category\": \"fair\"
                                }
            ]
        },
        {
            \"date\": \"2019-09-30\",
            \"grade\": null,
            \"counts\": [{
                    \"count\": 2472,
                    \"category\": \"warn\"
                                },
                {
                    \"count\": 6276,
                    \"category\": \"neutral\"
                                },
                {
                    \"count\": 10281,
                    \"category\": \"good\"
                                },
                {
                    \"count\": 185,
                    \"category\": \"bad\"
                                },
                {
                    \"count\": 718,
                    \"category\": \"fair\"
                                }
            ]
        },
        {
            \"date\": \"2019-08-31\",
            \"grade\": null,
            \"counts\": [{
                    \"count\": 2439,
                    \"category\": \"warn\"
                                },
                {
                    \"count\": 6283,
                    \"category\": \"neutral\"
                                },
                {
                    \"count\": 10257,
                    \"category\": \"good\"
                                },
                {
                    \"count\": 188,
                    \"category\": \"bad\"
                                },
                {
                    \"count\": 749,
                    \"category\": \"fair\"
                                }
            ]
        },
        {
            \"date\": \"2019-07-31\",
            \"grade\": null,
            \"counts\": [{
                    \"count\": 141,
                    \"category\": \"warn\"
                                },
                {
                    \"count\": 4420,
                    \"category\": \"neutral\"
                                },
                {
                    \"count\": 10770,
                    \"category\": \"good\"
                                },
                {
                    \"count\": 191,
                    \"category\": \"bad\"
                                },
                {
                    \"count\": 2438,
                    \"category\": \"fair\"
                                }
            ]
        },
        {
            \"date\": \"2019-06-30\",
            \"grade\": null,
            \"counts\": [{
                    \"count\": 129,
                    \"category\": \"warn\"
                                },
                {
                    \"count\": 4383,
                    \"category\": \"neutral\"
                                },
                {
                    \"count\": 10639,
                    \"category\": \"good\"
                                },
                {
                    \"count\": 199,
                    \"category\": \"bad\"
                                },
                {
                    \"count\": 2567,
                    \"category\": \"fair\"
                                }
            ]
        }
    ]
}" 
| spath input=temp 
| rename results{}.counts{}.count as counts, results{}.counts{}.category as category 
| fields counts,category 
| eval temp=mvzip(counts, category) 
| mvexpand temp 
| makemv temp delim="," 
| eval counts=mvindex(temp, 0) 
| eval category=mvindex(temp, 1) 
| stats sum(counts) as counts by category