oracle:audit:unified Parsing Issue

[Rhidian]

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?

[Rhidian]

Screenshot 2

[Rhidian]

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.

[kiran_panchavat]

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

[Rhidian]

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?

[kiran_panchavat]

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

https://community.splunk.com/t5/All-Apps-and-Add-ons/Is-it-possible-to-get-Character-Large-Object-CL... 

[Rhidian]

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

[Rhidian]

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

[kiran_panchavat]

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

[kiran_panchavat]

@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

[splunkreal]

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

Thanks.

[kiran_panchavat]

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