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!
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.
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.
Works perfectly, thanks!