Splunk Search

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

chiraggl
Engager

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"
}
]
}
]
}alt text

0 Karma

to4kawa
Ultra Champion
| 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

0 Karma

vnravikumar
Champion

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
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...