Getting Data In
Highlighted

Searching based on a particular element of a JSON array

New Member

I have Splunk ingesting JSON output from a tool we have which processes SNMP traps, which for the most part works great. The problem I have is with accessing elements of the JSON arrays. An example of one of our log entries is:

{   [-] 
  agent:     192.168.0.1    
  logs:  0> Queued for 3 targets
  0> v2c TRAP -> 192.168.0.2/162, sec-name=public
  0> v2c TRAP -> 192.168.0.2/162, sec-name=public

  pathname:  /Root/EMS
  pdu:  {   [-] 
    oids:   [   [-] 
      1.3.6.1.2.1.1.3.0 
      1.3.6.1.6.3.1.1.4.1.0 
      1.3.6.1.4.1.4998.1.1.10.1.1.2.0   
      1.3.6.1.4.1.4998.1.1.10.1.1.3.0   
      1.3.6.1.4.1.4115.1.9.1.2.9.1.1.2  
      1.3.6.1.4.1.4115.1.9.1.2.5.1.1.61335  
      1.3.6.1.4.1.4115.1.9.1.2.5.1.4.61335  
      1.3.6.1.4.1.4115.1.9.1.2.5.1.3.61335  
      1.3.6.1.4.1.4115.1.9.1.2.5.1.5.61335  
      1.3.6.1.6.3.18.1.3.0  
    ]   
    pduType:     TRAP   
    types:  [   [-] 
      TimeTicks 
      OID   
      Counter32 
      Integer32 
      Integer32 
      Integer32 
      OctetString   
      OctetString   
      Integer32 
      IpAddress 
    ]   
    variables:  [   [-] 
      1481974575    
      1.3.6.1.4.1.4115.1.9.1.2.15.0.3   
      134998    
      7 
      2 
      61335 
      ac:12:7a:25   
      00:00:00:00   
      51605 
      192.168.0.1   
    ]   
  } 
  peer:  192.168.0.1    
  securityName:  public
  securityNameIsPrintable:   true   
  timestamp:     1555347384005  
  version:   v2c    
}

The raw event is:

{"timestamp":1555347384005,"peer":"192.168.0.1","agent":"192.168.0.1","securityName":"public","version":"v2c","pdu":{"oids":["1.3.6.1.2.1.1.3.0","1.3.6.1.6.3.1.1.4.1.0","1.3.6.1.4.1.4998.1.1.10.1.1.2.0","1.3.6.1.4.1.4998.1.1.10.1.1.3.0","1.3.6.1.4.1.4115.1.9.1.2.9.1.1.2","1.3.6.1.4.1.4115.1.9.1.2.5.1.1.61335","1.3.6.1.4.1.4115.1.9.1.2.5.1.4.61335","1.3.6.1.4.1.4115.1.9.1.2.5.1.3.61335","1.3.6.1.4.1.4115.1.9.1.2.5.1.5.61335","1.3.6.1.6.3.18.1.3.0"],"variables":["1481974575","1.3.6.1.4.1.4115.1.9.1.2.15.0.3","134998","7","2","61335","ac:12:7a:25","00:00:00:00","51605","192.168.0.1"],"types":["TimeTicks","OID","Counter32","Integer32","Integer32","Integer32","OctetString","OctetString","Integer32","IpAddress"],"pduType":"TRAP"},"securityNameIsPrintable":true,"pathname":"/Root/EMS","logs":"0> Queued for 3 targets\n0> v2c TRAP -> 192.168.0.2/162, sec-name=public\n0> v2c TRAP -> 192.168.0.2/162, sec-name=public\n"}

I can easily search the top-level fields (such as agent or peer). I can also search the deeper fields that have a single value (such as pdu.pduType). The issue is with the nested array fields (e.g. pdu.variables). I can search for a value in that field if I use pdu.variables{}=value, but that just tells me if any field is that value. I want to be able to treat it like an array and use an index, such as pdu.variables{0}=value, but that doesn't work.

I have done some poking around on Splunk Answers and found this answer which suggested that I should use mvIndex to get the value out, but eval foo = mvIndex(pdu.variables, 0) | search foo=1481974575 returns zero results, even though it should return the example I have included here. So I'm at a loss as to how to actually get Splunk to actually search in these fields correctly. I could do it pretty easily if I downloaded the raw data and wrote a script, but I imagine there has to be a way to have Splunk do the search I want.

For what it's worth, my ultimate goal is to get a stats overview of SNMP trap types, so I can count which trap types are most prevalent in our environment. So what I need to do here is tag the second element of pdu.variables as a field called trapType, then do | stats count by trapType to get the overview I want.

0 Karma
Highlighted

Re: Searching based on a particular element of a JSON array

Esteemed Legend

Post your raw event (what you gave us has collapsed areas represented by the [-] string sprinkled throughout.

0 Karma
Highlighted

Re: Searching based on a particular element of a JSON array

New Member

I updated my post to show the raw event as well.

0 Karma
Highlighted

Re: Searching based on a particular element of a JSON array

Splunk Employee
Splunk Employee

For the specific search above, the problem is in the field name used.  For field names with special characters in them in eval and where commands use single tick/quote. 

... | eval foo = mvIndex('pdu.variables{}', 0) |search foo=1481974575

0 Karma
Highlighted

Re: Searching based on a particular element of a JSON array

Ultra Champion
| makeresults 
| eval _raw="{\"timestamp\":1555347384005,\"peer\":\"192.168.0.1\",\"agent\":\"192.168.0.1\",\"securityName\":\"public\",\"version\":\"v2c\",\"pdu\":{\"oids\":[\"1.3.6.1.2.1.1.3.0\",\"1.3.6.1.6.3.1.1.4.1.0\",\"1.3.6.1.4.1.4998.1.1.10.1.1.2.0\",\"1.3.6.1.4.1.4998.1.1.10.1.1.3.0\",\"1.3.6.1.4.1.4115.1.9.1.2.9.1.1.2\",\"1.3.6.1.4.1.4115.1.9.1.2.5.1.1.61335\",\"1.3.6.1.4.1.4115.1.9.1.2.5.1.4.61335\",\"1.3.6.1.4.1.4115.1.9.1.2.5.1.3.61335\",\"1.3.6.1.4.1.4115.1.9.1.2.5.1.5.61335\",\"1.3.6.1.6.3.18.1.3.0\"],\"variables\":[\"1481974575\",\"1.3.6.1.4.1.4115.1.9.1.2.15.0.3\",\"134998\",\"7\",\"2\",\"61335\",\"ac:12:7a:25\",\"00:00:00:00\",\"51605\",\"192.168.0.1\"],\"types\":[\"TimeTicks\",\"OID\",\"Counter32\",\"Integer32\",\"Integer32\",\"Integer32\",\"OctetString\",\"OctetString\",\"Integer32\",\"IpAddress\"],\"pduType\":\"TRAP\"},\"securityNameIsPrintable\":true,\"pathname\":\"/Root/EMS\",\"logs\":\"0> Queued for 3 targets\n0> v2c TRAP -> 192.168.0.2/162, sec-name=public\n0> v2c TRAP -> 192.168.0.2/162, sec-name=public\n\"}" 
| spath 
| eval tmp=mvzip('pdu.oids{}',mvzip('pdu.types{}','pdu.variables{}')) 
| stats values(*) as * by tmp 
| rex field=tmp "(?<oids>[^,]+),(?<types>[^,]+),(?<variables>\S+)" 
| fields - pdu.* _raw tmp

I made the table, so you can search it.

0 Karma