topic Re: Differentiate JSON event with multiple fields with the same name in Splunk Search

Differentiate JSON event with multiple fields with the same name

mlovasco — Fri, 26 Mar 2021 12:59:31 GMT

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!

Re: Differentiate JSON event with multiple fields with the same name

manjunathmeti — Fri, 26 Mar 2021 06:05:17 GMT

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.

Re: Differentiate JSON event with multiple fields with the same name

mlovasco — Fri, 26 Mar 2021 12:59:59 GMT

Works perfectly, thanks!