topic Re: How to create a chart that shows the JSON count by fields within an object? in Splunk Search

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

splunk_novice — Sat, 23 Jun 2018 00:39:13 GMT

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

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

Sukisen1981 — Sat, 23 Jun 2018 07:53:27 GMT

Hi, have you tried spath - http://docs.splunk.com/Documentation/Splunk/7.1.1/SearchReference/Spath

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

renjith_nair — Sat, 23 Jun 2018 08:10:47 GMT

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

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

Sukisen1981 — Sat, 23 Jun 2018 08:27:36 GMT

<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

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

splunk_novice — Sat, 23 Jun 2018 18:55:51 GMT

Thanks renjith, worked like a charm.