Getting Data In

How do you parse and chart fields in a JSON array?

someone4321
Explorer

Hi,

I have a log event where part of the log entry contains some JSON data similar to the following format:

    [
        {
            "fieldName": "value1",
            "counter": 4
        },
        {
            "fieldName": "value2",
            "counter": 2
        },
        {
            "fieldName": "value3",
            "counter": 13
        }
    ]

This gets outputted periodically and each log may have the same fieldName values. I have a field extraction to parse the JSON part into a field myMetrics. I now need to sum the counter over a given period of time BY fieldName and then chart it. So far I have the following search:

index="myIndex" host="myHost" sourcetype=mySourceType "My log: " | spath input=myMetrics | timechart span=1h sum({}.counter) by {}.fieldName

This partially works and is given me the following result:

_time                          value1    value2    value3
2019-01-01T01:00:00.000-0800   19        19        19

It seems to be summing all the values and not by fieldName.

How can I fix this?

Thanks

0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@someone4321 ,

Updated as per comments:

|makeresults|eval json=     "[
         {
             \"fieldName\": \"value1\",
             \"counter\": 4
         },
         {
             \"fieldName\": \"value2\",
             \"counter\": 2
         },
         {
             \"fieldName\": \"value3\",
             \"counter\": 13
         }
     ]"
     |spath input=json|eval x=mvzip('{}.counter','{}.fieldName',"#")|mvexpand x
     |rex field=x "(?<counter>\d+)#(?<fieldName>\w+)"
     |timechart span=1h sum(counter) by fieldName

Try enclosing the field names in single quote (')

     index="myIndex" host="myHost" sourcetype=mySourceType "My log: " | spath input=myMetrics 
   | timechart span=1h sum('{}.counter') by '{}.fieldName'
Happy Splunking!

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

@someone4321 ,

Updated as per comments:

|makeresults|eval json=     "[
         {
             \"fieldName\": \"value1\",
             \"counter\": 4
         },
         {
             \"fieldName\": \"value2\",
             \"counter\": 2
         },
         {
             \"fieldName\": \"value3\",
             \"counter\": 13
         }
     ]"
     |spath input=json|eval x=mvzip('{}.counter','{}.fieldName',"#")|mvexpand x
     |rex field=x "(?<counter>\d+)#(?<fieldName>\w+)"
     |timechart span=1h sum(counter) by fieldName

Try enclosing the field names in single quote (')

     index="myIndex" host="myHost" sourcetype=mySourceType "My log: " | spath input=myMetrics 
   | timechart span=1h sum('{}.counter') by '{}.fieldName'
Happy Splunking!

someone4321
Explorer

@renjith.nair No luck, adding quotes resulted in zero results found.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Ok, are you able to see the results for this?

index="myIndex" host="myHost" sourcetype=mySourceType "My log: " | spath input=myMetrics|table {}.counter,{}.fieldName

If yes , can you try

index="myIndex" host="myHost" sourcetype=mySourceType "My log: " 
| spath input=myMetrics|rename {}.counter as counter,{}.fieldName as fieldName
|timechart span=1h sum(counter) by fieldName
Happy Splunking!
0 Karma

someone4321
Explorer

Unfortunately I tried that already and it produces the same results as I got originally. That is, for the original example, I still get all the distinct fieldNames showing the total sum count:

 _time                          value1    value2    value3
 2019-01-01T01:00:00.000-0800   19        19        19

The table you suggested works but what is perhaps interesting, is if I try to put the values in a table as you suggested, each unique fieldName is not given it own distinct row as I would have thought. It just appears as a single row in the table with unique values given new lines within the row. They do line up with the correct counter values however. But maybe that's a hint?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@someone4321 ,
Ofcourse it helps. You have a multivalue field and we need to extract them. So try

index="myIndex" host="myHost" sourcetype=mySourceType "My log: " 
 | spath input=myMetrics
|eval x=mvzip('{}.counter','{}.fieldName',"#")|mvexpand x
|rex field=x "(?<counter>\d+)#(?<fieldName>\w+)"|timechart span=1h sum(counter) by fieldName
Happy Splunking!

someone4321
Explorer

That worked, thanks!

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@someone4321 ,glad to know, Had updated the answer with the suggestion. If you dont have further question on that, please accept as answer to close the thread.

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...