Getting Data In

JSON Data Query

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

cblanton
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

kenmcgarrahan
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

maciep
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

jconger
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

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

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...