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