topic Re: How do I get Splunk to extract nested JSON arrays properly? in Splunk Search

How do I get Splunk to extract nested JSON arrays properly?

lyndac — Mon, 08 Feb 2016 20:58:12 GMT

I am indexing JSON data. I need to be able to do stats based "by patches" and "by admin". I can't get spath or mvexpand to extract the nested arrays properly. Can anyone help me to figure this out?

My props.conf looks like this:

[ my_json ]
INDEXED_EXTRACTIONS = json
KV_MODE=none
MAX_TIMESTAMP_LOOKAHEAD=30
NO_BINARY_CHECK=true
TIMESTAMP_FIELDS=upTime
TIME_FORMAT=%Y-%m-%d %H:%M:%S

The data is similar to this:

{
  "upTime": "2015-02-08 16:43:23",
  "type": "thetype",
   "id": "123454829",
   "systems": [
      {
          "hostname": "host1",
          "admins": [ "jdoe","lcod", "pamtie"],
          "os": "linux",
          "patches": ["1.2", "2.3", "4.3", "5.4"]
      },
      {
          "hostname": "host2",
          "admins": [ "barry", "patty"],
          "os": "linux",
          "patches": ["2.3", "5.4"]
      }
  ]
}

Re: How do I get Splunk to extract nested JSON arrays properly?

bmacias84 — Mon, 08 Feb 2016 22:33:10 GMT

This kind of data is a pain to work with because it requires the uses of mv commands. to extract what you want you need first zip the data you want to pull out.

…| rename systems{}.hostname as hostname, rename systems{}.patches as patches | eval a mvzip(hostname,patches, "|") | fields _time a | mvexpand a | rex field=a "(?<hostname>[^\|]+)\|(?<patches>[^\|]+)"

If you need to expand patches just append mvexpand patches to the end.

I use this method to to extract multilevel deep fields with multiple values. It does a the limitation of only able to extract two multi valued fields from the data and get very slow with large data sets.

Re: How do I get Splunk to extract nested JSON arrays properly?

somesoni2 — Mon, 08 Feb 2016 22:38:13 GMT

Your sample data doesn't look like in proper json format (some keys are not in double quotes) and that should be the reason spath (in search) and INDEXED_EXTRACTIONS = json in props.conf is not recognizing array. Check the syntax here http://pro.jsonlint.com/

Re: How do I get Splunk to extract nested JSON arrays properly?

lyndac — Tue, 09 Feb 2016 17:48:57 GMT

Thanks for your help. I was finally able to accomplish this using spath & mvexpand. In case someone else needs this in the future, my search is now:

index=foo | spath path=systems{} output=x | fields - _raw | fields upTime, type, id, x | mvexpand x | spath input=x | rename admins{} as admins | mvexpand admins | stats count as Count by type, admins