Splunk Search

How to create a chart that shows the JSON count by fields within an object?

splunk_novice
New Member

Each line of my log has the following json construct

{        resourceUsage: [   
        {
         cloud:  AWS    
         count:  34 
         resource:   EC2_INSTANCE   
        }   


        {   
         cloud:  AWS    
         count:  3  
         resource:   NAT_GATEWAY    
        }
      ]

}

I want create a time chart that shows sum (resourceUsage.count) by resourceUsage.resource eg. EC2_INSTANCE = 51, NAT_GATEWAY=25

My query which doesn't work looks like this timechart span=1d sum(resourceUsage{}.count) by resourceUsage{}.resource

Tags (4)
0 Karma
1 Solution

renjith_nair
Legend

Hi @splunk_novice,

Hope this helps.

        | makeresults |eval json="{
                \"resourceUsage\":    [    
                     {
                        \"cloud\":     \"AWS\",
                        \"count\":     34,    
                        \"resource\":  \"EC2_INSTANCE\"
                     }, 
                     {    
                        \"cloud\":     \"AWS\",
                        \"count\":     3,    
                        \"resource\":  \"NAT_GATEWAY\"
                     },
                     {    
                        \"cloud\":     \"AWS\",
                        \"count\":     10,    
                        \"resource\":  \"EC2_INSTANCE\"
                     },
                     {    
                        \"cloud\":     \"AWS\",
                        \"count\":     22,    
                        \"resource\":  \"NAT_GATEWAY\"
                     },
                     {    
                        \"cloud\":     \"AWS\",
                        \"count\":     7,    
                        \"resource\":  \"EC2_INSTANCE\"
                     }               
                   ]
            }"
| spath input=json|fields - json|rename resourceUsage{}.resource  as resource,resourceUsage{}.count as count
|eval zip=mvzip(resource,count)
|fields _time,zip| mvexpand zip|eval splitted=split(zip,",")|eval resource=mvindex(splitted,0)|eval count=mvindex(splitted,1)
|table _time resource,count|timechart sum(count) by resource
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

Sukisen1981
Champion
<your index> |  rex field=_raw "count:(?<count>.*)" max_match=0 |  rex field=_raw "resource:(?<resource>.*)" max_match=0| eval count=trim(count)|eval resource=trim(resource) |eval fields = mvzip(count,resource) 
| mvexpand fields 
| rex field=fields "(?<count>\w+),(?<resource>\w+)" 
|timechart values(count) by resource

Try this if you are not sure about your json field, ideally @renjith.nair 's solution and spath is the correct way to go about this

0 Karma

renjith_nair
Legend

Hi @splunk_novice,

Hope this helps.

        | makeresults |eval json="{
                \"resourceUsage\":    [    
                     {
                        \"cloud\":     \"AWS\",
                        \"count\":     34,    
                        \"resource\":  \"EC2_INSTANCE\"
                     }, 
                     {    
                        \"cloud\":     \"AWS\",
                        \"count\":     3,    
                        \"resource\":  \"NAT_GATEWAY\"
                     },
                     {    
                        \"cloud\":     \"AWS\",
                        \"count\":     10,    
                        \"resource\":  \"EC2_INSTANCE\"
                     },
                     {    
                        \"cloud\":     \"AWS\",
                        \"count\":     22,    
                        \"resource\":  \"NAT_GATEWAY\"
                     },
                     {    
                        \"cloud\":     \"AWS\",
                        \"count\":     7,    
                        \"resource\":  \"EC2_INSTANCE\"
                     }               
                   ]
            }"
| spath input=json|fields - json|rename resourceUsage{}.resource  as resource,resourceUsage{}.count as count
|eval zip=mvzip(resource,count)
|fields _time,zip| mvexpand zip|eval splitted=split(zip,",")|eval resource=mvindex(splitted,0)|eval count=mvindex(splitted,1)
|table _time resource,count|timechart sum(count) by resource
---
What goes around comes around. If it helps, hit it with Karma 🙂

splunk_novice
New Member

Thanks renjith, worked like a charm.

0 Karma

Sukisen1981
Champion
0 Karma
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...