topic oracle:audit:unified Parsing Issue in All Apps and Add-ons

oracle:audit:unified Parsing Issue

Rhidian — Wed, 12 Mar 2025 09:53:07 GMT

Hi,

I'm having an issues parsing the SQL_TEXT field from oracle:audit:unified. When the field comes through it contains spurious text that isn't returned by the query using DBConnect and the oracle:audit:unified template. For example:

DBConnect
grant create tablespace to test_splunk,

Splunk
grant create tablespace to test_splunk,4,,1,,,,,,

The RAW event seems to come through as a CSV by virtue of the Oracle TA but we have a regex for the event extraction that looks like the below which seems to work in regex101:

SQL_TEXT="(?<SQL_TEXT>(?:.|\n)*?)(?=(?:",\s\S+=|"$))

I know the data type is CLOD so I have tried to converting it using the substring command but I get the same result, any idea what is going on here?

Re: oracle:audit:unified Parsing Issue

kiran_panchavat — Wed, 12 Mar 2025 09:53:53 GMT

@Rhidian

Start with the rex solution in your search to quickly verify if you can isolate the valid SQL:

index=<your_index> sourcetype=oracle:audit:unified
| rex field=SQL_TEXT "^(?<cleaned_sql>[^,]+)"
| table cleaned_sql

If that works, apply it into props.conf and transforms.conf for a permanent fix at ingestion time. If the pattern of spurious text varies (e.g., not always commas).

Re: oracle:audit:unified Parsing Issue

kiran_panchavat — Wed, 12 Mar 2025 09:57:49 GMT

@Rhidian

Used makeresults to create sample events.

To apply this logic permanently to your oracle:audit:unified sourcetype in Splunk, you’ll need to configure props.conf and transforms.conf to clean the SQL_TEXT field during ingestion. This ensures the spurious text (e.g., 4,,1,,,,,,) is stripped out before the data is indexed, so all your searches will see the cleaned version.

In props.conf, you’ll associate the oracle:audit:unified sourcetype with a transform that cleans the SQL_TEXT field.

Location:

Typically $SPLUNK_HOME/etc/system/local/props.conf or an app-specific directory like $SPLUNK_HOME/etc/apps/<your_app>/local/props.conf.

props.conf

[oracle:audit:unified]
SHOULD_LINEMERGE = false
TRUNCATE = 10000
TRANSFORMS-clean_sql = clean_sql_text

transforms.conf

[clean_sql_text]
SOURCE_KEY = SQL_TEXT
REGEX = ^([^,]+)
FORMAT = SQL_TEXT::$1
DEST_KEY = SQL_TEXT

Re: oracle:audit:unified Parsing Issue

Rhidian — Wed, 12 Mar 2025 10:08:03 GMT

Thanks making this a bit odder if I look at the event in the fields the text seems OK but if I click on it in the Interesting fields that is when I see the spurious data see the screenshots.

Re: oracle:audit:unified Parsing Issue

kiran_panchavat — Wed, 12 Mar 2025 10:04:54 GMT

@Rhidian

Since you mentioned that SQL_TEXT is a CLOB (Character Large Object) in Oracle, the issue likely stems from how this data type is processed and ingested by Splunk versus how DBConnect handles it.

This happens because:

DB Connect is not handling CLOB properly.
It may append metadata or placeholders (such as null indicators, buffer sizes, or offsets).

Re: oracle:audit:unified Parsing Issue

Rhidian — Wed, 12 Mar 2025 10:08:22 GMT

Screenshot 2

Re: oracle:audit:unified Parsing Issue

Rhidian — Wed, 12 Mar 2025 10:10:19 GMT

Wouldn't casting this to a new data type in DBConnect fix this or is that merely converting the additional along with the other data?

Re: oracle:audit:unified Parsing Issue

kiran_panchavat — Wed, 12 Mar 2025 10:11:59 GMT

@Rhidian

In DBConnect, you can modify the SQL query to cast the SQL_TEXT field from a CLOB to a different data type, such as VARCHAR2 (Oracle’s variable-length string type).

Please have a look:

https://community.splunk.com/t5/All-Apps-and-Add-ons/Is-it-possible-to-get-Character-Large-Object-CLOB-values-from/m-p/183455

https://community.splunk.com/t5/All-Apps-and-Add-ons/Is-it-possible-to-get-Character-Large-Object-CLOB-values-from/m-p/183453

Re: oracle:audit:unified Parsing Issue

Rhidian — Wed, 12 Mar 2025 10:14:50 GMT

I tried this and the result is the same...

Re: oracle:audit:unified Parsing Issue

Rhidian — Fri, 14 Mar 2025 10:01:20 GMT

@kiran_panchavat did you get a chance to review the screenshots?

Re: oracle:audit:unified Parsing Issue

kiran_panchavat — Fri, 14 Mar 2025 10:07:52 GMT

@Rhidian Yes, I saw the screenshot. It looks like the SQL_TEXT field is displaying correctly within events but appears distorted or truncated when viewed in the "Interesting Fields" panel in Splunk.

Run the following search in Splunk to see if the issue persists in the search results:

index=<your_index> | table SQL_TEXT

If the data appears fine in the search results but not in the field summary panel, it's likely a UI rendering issue.

Re: oracle:audit:unified Parsing Issue

splunkreal — Fri, 25 Jul 2025 16:09:42 GMT

Hello @kiran_panchavat @Rhidian maybe we could filter special characters with REGEXP_REPLACE(sql_text… in the SQL query?

Thanks.