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

[someone4321]

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

[renjith_nair · Accepted solution]

@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'

[someone4321]

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

[renjith_nair]

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

[someone4321]

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?

[renjith_nair]

@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

[someone4321]

That worked, thanks!

[renjith_nair]

@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.