Splunk Search

JSON vector of vector

splunkcol
Builder

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?

Labels (2)
0 Karma

splunkcol
Builder

@gcusello could you help me with this case?

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

splunkcol
Builder

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

0 Karma

tscroggins
Influencer

@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

 

0 Karma

splunkcol
Builder

 

Hello, this is one of the logs

splunkcol_0-1616965927856.png

 

0 Karma

tscroggins
Influencer

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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...