Splunk Search

How to extract field from json array?

haobin
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)

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

Ron_Naken
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

carasso
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

gkanapathy
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...