Differentiate JSON event with multiple fields with the same name

[mlovasco]

Hello - I have JSON events that have multiple items nested inside them.  Each item has fields with the same name.  I'm trying to report with stats and timechart on specifically "lastvalue_raw" for each "sensor" however when trying a few different things my query still chooses the first "lastvalue_raw" for any of the sensors.  The JSON event could have any number of nested items within it depending on the type of sensor.  Below is an example event:

{
    "prtg-version":  "21.1.65.1767",
    "treesize":  2,
    "sensor":  [
                   {
                       "device":  "Colo Palo Alto FW1",
                       "device_raw":  "Colo Palo Alto FW1",
                       "objid":  8219,
                       "objid_raw":  8219,
                       "sensor":  "Comcast (1Gbit/s - Circuit ID)",
                       "sensor_raw":  "Comcast (1Gbit/s - Circuit ID)",
                       "status":  "Unusual",
                       "status_raw":  10,
                       "lastvalue":  "37 Mbit/s",
                       "lastvalue_raw":  4637266.8945
                   },
                   {
                       "device":  "Colo Palo Alto FW1",
                       "device_raw":  "Colo Palo Alto FW1",
                       "objid":  33904,
                       "objid_raw":  33904,
                       "sensor":  "Verizon Business (1Gbit/s - Circuit ID)",
                       "sensor_raw":  "Verizon Business (1Gbit/s - Circuit ID)",
                       "status":  "Up",
                       "status_raw":  3,
                       "lastvalue":  "163 Mbit/s",
                       "lastvalue_raw":  20343218.0333
                   }
               ]
}

And here is an example of a query I have tried to separate them:

index=prtg_test sourcetype=_json 
| spath 
| rename "sensor{}.lastvalue_raw" AS lastvalue, "sensor{}.sensor" AS sensor 
| timechart span=1m latest(lastvalue) by sensor

Any help is greatly appreciated!

[manjunathmeti · Accepted solution]

hi @mlovasco,

Fields lastvalue and sensor are multivalue fields. You need to expand them before using timechart/stats command.

index=prtg_test sourcetype=_json 
| spath 
| rename "sensor{}.lastvalue_raw" AS lastvalue, "sensor{}.sensor" AS sensor 
| eval mzip=mvzip(sensor, lastvalue, "&") 
| mvexpand mzip 
| eval mzip=split(mzip, "&"), sensor=mvindex(mzip, 0), lastvalue=mvindex(mzip, 1) 
| timechart span=1m latest(lastvalue) by sensor

If this reply helps you, a like would be appreciated.

[mlovasco]

Works perfectly, thanks!