topic Re: JSON vector of vector in Splunk Search

JSON vector of vector

splunkcol — Tue, 23 Mar 2021 18:09:35 GMT

I have a data source which I collect using DB CONNECT from an oracle database which brings the information in JSON format, but I see that the use of regular expressions is not successful, neither is the field extraction assistant.

The problem is because it is a vector of vectors, it is a vector that within each log in JSON format this in turn has more JSON logs

Someone something similar happened to him?

Re: JSON vector of vector

tscroggins — Sun, 28 Mar 2021 17:35:42 GMT

@splunkcol

Can you provide an example of how the JSON is nested? I interpret "vector" as an array, but I think you may mean objects escaped as string values in a parent object:

{"foo": "{\"bar\": \"baz\"}"}

{"bar": "baz"}

In this case, you can use e.g.:

| spath input=foo | table bar

Re: JSON vector of vector

splunkcol — Sun, 28 Mar 2021 21:13:00 GMT

Hello, this is one of the logs

Re: JSON vector of vector

splunkcol — Tue, 30 Mar 2021 18:53:38 GMT

@gcusello could you help me with this case?

Re: JSON vector of vector

gcusello — Wed, 31 Mar 2021 06:36:41 GMT

Hi @splunkcol,

no I didn't encountered this problem: it's the first time that I hear of a DB that exports data in JSON.

Anyway, I'd try to use in a different way the DB-Connect: do you export a full table or a query result?

Maybe using a query you have a table to manage in the usual way.

Ciao.

Giuseppe

Re: JSON vector of vector

splunkcol — Wed, 31 Mar 2021 14:32:01 GMT

The development area did was send all the logs of a solution implemented as microservices to a database, that is why a JSON contains other JSON since they are different types of logs within a JSON

Re: JSON vector of vector

tscroggins — Thu, 01 Apr 2021 23:42:42 GMT

@splunkcol

You're up against a few different problems.

First, recent versions of Splunk DB Connect do not escape quotes and backslashes in field values. In prior version of DB Connect--those that wrote data to temporary files instead of HEC--the quote and backslash characters were escaped:

"COLUMN_NAME"="This is a column \"value\" with escaped quotes."

In this case, the source type could be configured to properly interpret the strings at search time:

# props.conf
[my_source_type]
KV_MODE = auto_escaped

COLUMN_NAME
This is a column "value" with escaped quotes.

DB Connect now outputs:

COLUMN_NAME="This is not a column "value" with escaped quotes."

COLUMN_NAME
This is not a column

(With a trailing space in the value.)

The typical way to work around this is in your SQL query (using REPLACE in this example for SQL dialects that support it):

SELECT REPLACE(REPLACE(COLUMN_NAME, '\', '\\'), '"', '\"') COLUMN_NAME
FROM FOO
WHERE BAR > ?
ORDER BY BAR ASC

COLUMN_NAME
This is a column \"value\" with escaped quotes.

"COLUMN_NAME"="This is a column \"value\" with escaped quotes."

Second, your column contains an outer JSON object. Here's an example with proper escape sequences:

"COLUMN_NAME"="[{\"foo\":\"bar\"},{\"foo\":\"baz\"}]"

COLUMN_NAME
[{"foo":"bar"},{"foo":"baz"}]

If the outer JSON is properly formatted, it's simple enough to parse the field:

| spath input=COLUMN_NAME

From there, you can find and parse any fields containing nested JSON:

"COLUMN_NAME"="[{\"foo\":\"{\\\"bar\\\":\\\"baz\\\"}\"},{\"foo\":\"{\\\"bar\\\":\\\"qux\\\"}\"}]"

COLUMN_NAME
[{"foo":"{\"bar\":\"baz\"}"},{"foo":"{\"bar\":\"qux\"}"}]

| spath input=COLUMN_NAME
| mvexpand {}.foo

{}.foo
{"bar":"baz"}
{"bar":"qux"}

| spath input={}.foo

bar
baz
qux

Finally, the JSON value in your database column appears to be malformed. Paraphrasing:

MESSAGE_JSON="[{"key":"Rxxxs","name":"Cxxxs:{\"txxxd":\"4xxx2\",...\"}}"}]"

Even properly escaped, the value after Cxxxs: is an invalid JSON object:

{\"txxxd":\"4xxx2\",...\"}

Notice the inconsistency in escape sequences where a plain quote follows an escaped quote.

Before you go any further, take a closer look at the value of MESSAGE_JSON in the database. If some or all of the value can be correctly parsed as JSON, then it can be properly escaped in the SQL query and in turn properly parsed in Splunk.

If you can post an example of the complete MESSAGE_JSON value in text with obfuscated keys and strings rather than an image, we can provide more insight.