Splunk Search

Differentiate JSON event with multiple fields with the same name

mlovasco
Engager

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!

Labels (1)
0 Karma
1 Solution

manjunathmeti
Champion

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.

View solution in original post

manjunathmeti
Champion

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.

View solution in original post

mlovasco
Engager

Works perfectly, thanks!

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.