Splunk Search

Differentiate JSON event with multiple fields with the same name

mlovasco
Explorer

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.

mlovasco
Explorer

Works perfectly, thanks!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...