Splunk Search

How to extract field from json array?

Explorer

If I have a log which is in JSON format and contains array in JSON, can Splunk extract values in this array? For example,

{"name": ["john", "mark", "abby"]}

how can I write a regular expression to parse this string and get the field like

name=john name=mark name=abby

Thank you.

Tags (1)

Path Finder

After having played around with the 'jsonkv' tool in the 'jsonutils' app on SplunkBase, I got stuck on parsing array values.

Fortunately Splunk 5 has a search command 'spath' that does the job.

http://docs.splunk.com/Documentation/Splunk/5.0.3/SearchReference/Spath

I had to extract the JSON component of my log entry into its own field and pass it to |spath input=raw_json, then the rest worked as documented.

Splunk Employee
Splunk Employee

Here's the poor-man's method of extracting the multi-value array for the name field at search time. This example will strip any surrounding punctuation from the names. It assumes that your sourcetype is "jsonarray", and it will create a multi-valued field called 'name_mv' that contains the array elements.

PROPS.CONF:
[jsonarray]
REPORT-json = xf-jsonarray, xf-jsonmv

TRANSFORMS.CONF:
[xf-jsonarray]
REGEX  = \{"(?<_KEY_1>[^"]+)":\s\[(?<_VAL_1>[^\]]+)

[xf-jsonmv]
SOURCE_KEY = name
REGEX = ("?)(?<name_mv>[^,"]*)("?)(,\s*)?
MV_ADD = true

With the mv field created, you can then do searches that look for array elements, like:

sourcetype=jsonarray name_mv=abby

HTH
Ron

Splunk Employee
Splunk Employee

Splunk does not parse json at index time, and at search-time any sort of regex would do a half-hearted job, especially on your example where a value is a list.

There are two options:

1) The fastest option is to add a scripted input. For example, you can write a little python program to convert json to attr=val format and output the logs in a friendly format, before splunk is called: http://www.splunk.com/base/Documentation/latest/Admin/Setupcustom%28scripted%29inputs

2) Another option is to write a custom search command to do the conversion for you at search-time. It's slower, but allows you to tweak it as needed, and apply it only when you want -- http://www.splunk.com/base/Documentation/latest/SearchReference/Customsearchshape

0 Karma

Splunk Employee
Splunk Employee

Conveniently, someone has written some of these utilities and put them up on Splunkbase: http://splunkbase.splunk.com/apps/All/4.x/app:jsonutils

0 Karma