Using spath with Sequentially Numbered JSON Keys

[cw]

I'm trying to create a simple table from the following JSON data, and I only care about extracting three particular values: trap_recieved_ts, cctConfigChangeType, and cctDeviceLabel

{
  "trap_destination_ip": "1.2.3.4",
  "trap_recieved_epoch": "1234567890",
  "trap_recieved_ts": "2021-04-08 14:17:32",
  "trap_source_ip": "1.2.3.4",
  "traps": [
    {
      "DISMAN-EVENT-MIB::sysUpTimeInstance": "2:2:49:18.49",
      "ETV-Agent-MIB::cctConfigChangeTrapSequenceNumber.17": "Wrong Type (should be Counter32): 17",
      "ETV-Agent-MIB::cctConfigChangeType.17": "Switchover",
      "ETV-Agent-MIB::cctDeviceLabel.17": "HOSTNAME",
      "SNMP-COMMUNITY-MIB::snmpTrapAddress.0": "1.2.3.4",
      "SNMP-COMMUNITY-MIB::snmpTrapCommunity.0": "public",
      "SNMPv2-MIB::snmpTrapEnterprise.0": "ETV-Agent-MIB::cctConfigChangeTrapTable",
      "SNMPv2-MIB::snmpTrapOID.0": "ETV-Agent-MIB::cctSingleConfigChangeTrap"
    }
  ]
}

The first issue I'm running into is with the .17, which increments with every new data point. The dot forces Splunk to treat the 17 as a new object in the path, and the fact that it increments prevents be from statically defining the key in my search string.

index=index
| spath output=time path=trap_recieved_ts
| spath output=alert path=traps.ETV-Agent-MIB::cctConfigChangeType.17
| spath output=device path=traps.ETV-Agent-MIB::cctDeviceLabel.17
| table time alert device

I've read that I should be able to do the following in order to identify the two problematic keys I'm interested in, but Splunk seems to just disregard the {}

index=index
| spath output=time path=trap_recieved_ts
| spath output=alert path=traps{2}
| spath output=device path=traps{3}
| table time alert device

Any suggestions?

[ITWhisperer · Accepted solution]

Remove the numbering before the spath (and take into account the traps is a collection)

| rex mode=sed "s/(:cctConfigChangeTrapSequenceNumber)\.\d+/\1/g"
| rex mode=sed "s/(:cctConfigChangeType)\.\d+/\1/g"
| rex mode=sed "s/(:cctDeviceLabel)\.\d+/\1/g"
| spath output=time path=trap_recieved_ts
| spath output=alert path=traps{}.ETV-Agent-MIB::cctConfigChangeType
| spath output=device path=traps{}.ETV-Agent-MIB::cctDeviceLabel
| table time alert device

[cw]

This worked perfectly. Thanks @ITWhisperer

[manjunathmeti]

hi @cw,

Just try with spath and assign output field values to new fields.

| makeresults 
| eval _raw="{
  \"trap_destination_ip\": \"1.2.3.4\",
  \"trap_recieved_epoch\": \"1234567890\",
  \"trap_recieved_ts\": \"2021-04-08 14:17:32\",
  \"trap_source_ip\": \"1.2.3.4\",
  \"traps\": [
    {
      \"DISMAN-EVENT-MIB::sysUpTimeInstance\": \"2:2:49:18.49\",
      \"ETV-Agent-MIB::cctConfigChangeTrapSequenceNumber.17\": \"Wrong Type (should be Counter32): 17\",
      \"ETV-Agent-MIB::cctConfigChangeType.17\": \"Switchover\",
      \"ETV-Agent-MIB::cctDeviceLabel.17\": \"HOSTNAME\",
      \"SNMP-COMMUNITY-MIB::snmpTrapAddress.0\": \"1.2.3.4\",
      \"SNMP-COMMUNITY-MIB::snmpTrapCommunity.0\": \"public\",
      \"SNMPv2-MIB::snmpTrapEnterprise.0\": \"ETV-Agent-MIB::cctConfigChangeTrapTable\",
      \"SNMPv2-MIB::snmpTrapOID.0\": \"ETV-Agent-MIB::cctSingleConfigChangeTrap\"
            }
  ]
}" 
| spath 
| eval time=trap_recieved_ts, device='traps{}.ETV-Agent-MIB::cctDeviceLabel.17', alert='traps{}.ETV-Agent-MIB::cctConfigChangeType.17' 
| table time alert device

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