Getting Data In

JSON Data Query

Explorer

We are ingesting JSON data similar to the following:

{
   "Id":"987654321",
   "data":[
      {
         "answer":"12345",
         "label":"Work Order"
      },
      {
         "answer":"04/01/2018",
         "label":"Work Order Date"
      },
      {
         "answer":"15:17:00",
         "label":"Order Time"
      },
      .
      .    (more answer/label elements)
      .
   ],
   "username":"some.user"
}

Entirely possible we're missing it in the spath and related JSON processing documentation, but we're unable to determine how to format a search query which allows use of the "label" value as the left-hand side and the "answer" value as the right-hand side of a query (e.g., "Work Order Date"="04/01/2018").

The JSON array contains additional answer/label pairs with other date values, so specifying something like 'data{}.answer="04/01/2018"' is insufficient, as it matches any event where that date appears as any 'answer' value.

Any suggestions?

Tags (2)

Communicator

@kenmcgarrahan did you ever come up with a better solution than the configurations below? I have the same issue.

I see this post from 2016 describes the same. https://www.splunk.com/en_us/blog/tips-and-tricks/eureka-extracting-key-value-pairs-from-json-fields... @LCM any chance you know if the native capability mentioned, ever materialized?

0 Karma

Explorer

@cblanton - We did not pursue the props.conf/transforms.conf referenced below. To be brutally honest, I had completely forgotten about this query. Either the need/requirement for it was re-stated or it was no longer relevant.

0 Karma

Champion

Splunk isn't going to inherently know that you want the label field to equal the answer field....but you can tell it. On your search head, configure props/transforms to extract those fields.

i didn't test it, but something like this.

props.conf

[your_sourcetype]
REPORT-labels_answers = extract_labels_answers

transforms.conf

[extract_labels_answers]
REGEX = "answer":"([^"]+)",[^"]+"label":"([^"]+)
FORMAT = $2::$1

This should extract each answer/label pair and then a create a field for the value of label and set it to the value of answer. At that point, you should be able to filter the way you'd like.

0 Karma

Splunk Employee
Splunk Employee

What is the desired outcome? Are you trying to split the data array into individual events? Technically, for the example you gave, you could do something like:

base_search data{}.label="Work Order Date" data{}.answer="04/01/2018"

But, that still just returns the entire event.

0 Karma

Explorer

Ideally, we'd end up with a table where each event was a row, with each 'label' value a named column whose value is the 'answer' value. With apologies for the formatting, something like:

Id                      username     Work Order     Work Order Date     Order Time     Completion Date
987654321     some.user     12345               04/01/2018               15:17:00           04/01/2018
987654322     other.user     12346               04/01/2018               16:00:00           04/03/2018

The JSON 'data' array is a list of attributes which varies in content across events. Being able to report and filter results off those attributes is the target.

0 Karma