topic Re: JSON Data Query in Getting Data In

JSON Data Query

kenmcgarrahan — Tue, 24 Apr 2018 19:58:12 GMT

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?

Re: JSON Data Query

jconger — Tue, 24 Apr 2018 21:00:16 GMT

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.

Re: JSON Data Query

maciep — Tue, 24 Apr 2018 21:43:51 GMT

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.

Re: JSON Data Query

kenmcgarrahan — Wed, 25 Apr 2018 15:25:18 GMT

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.

Re: JSON Data Query

cblanton — Fri, 03 Apr 2020 19:22:49 GMT

@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.html @LCM any chance you know if the native capability mentioned, ever materialized?

Re: JSON Data Query

kenmcgarrahan — Tue, 07 Apr 2020 15:09:30 GMT

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