topic Searching based on a particular element of a JSON array in Getting Data In

Searching based on a particular element of a JSON array

cdrzewiecki — Mon, 15 Apr 2019 17:21:48 GMT

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.

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

woodcock — Mon, 15 Apr 2019 19:23:21 GMT

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

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

cdrzewiecki — Tue, 16 Apr 2019 19:49:12 GMT

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

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

jnapier — Fri, 19 Jun 2020 17:23:19 GMT

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

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

to4kawa — Sat, 20 Jun 2020 01:02:35 GMT

| 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.