How can I extract all fields from my DB Connect results in Splunk?

[jroeser1404]

I have configured a Database Input in DB Connect to pull in data from an Oracle view. A sample string from one of the events follows:

2023-02-28 15:40:50.760, AUDIT_TYPE="Standard", OS_USERNAME="Administrator", TERMINAL="unknown", DBUSERNAME="RACOON", CLIENT_PROGRAM_NAME="SQL Developer", STATEMENT_ID="978", EVENT_TIMESTAMP="2023-02-28 18:40:50.76", ACTION_NAME="ALTER USER",  OBJECT_NAME="SPLUNK", SQL_TEXT="ALTER USER "SPLUNK" DEFAULT ROLE "CONNECT","AUDIT_VIEWER"", SYSTEM_PRIVILEGE_USED="SYSDBA", CURRENT_USER="SYS", UNIFIED_AUDIT_POLICIES="ORA_SECURECONFIG"

However, when I run this search the fields are not correctly identified:

index=oracle_audit sourcetype=ID source=OracleAuditConnection

Specifically, what should be fields like TERMINAL, CLIENT_PROGRAM_NAME, and OS_USERNAME (among many others) are not identified as fields. Additionally, the search is picking up values as fields, that should not be fields at all (often from the SQL_TEXT field). For example, "ACTIONS ALTER ON SPLUNK.BAT" is picked up as a field, rather than a value.

I can improve the results a little by using the following:

index=oracle_audit sourcetype=ID source=OracleAuditConnection  | extract pairdelim="\"{,}"

However, it still does not correctly identify all the fields. Nor does it work on the more complicated SQL_TEXT field, which may contain quotations and the equals signs at time.

What can I do to successfully have all of my fields extracted? Is there any trick I can do, given that I am using DB Connect?

[richgalloway]

Splunk usually parses well events with fields in the format key=value, but stumbles when the value field contains embedded (and unescaped) quotation marks.  The ID sourcetype needs settings added or modified in a props.conf file to properly parse that data.  You may need a transform to parse the event using a regular expression.

Please share the current props.conf settings for the ID sourcetype.

[jroeser1404]

Thank you for your reply! I'm still learning Splunk and just let the settings default to whichever for the sourcetype. However, I did find this:

https://docs.splunk.com/Documentation/AddOns/released/Oracle/Datatypes

I am pulling data in from the UNIFIED_AUDIT_TRAIL view in Oracle, which I believe would correspond to the oracle:audit:unified sourcetype in the above link. I installed the Splunk Add-On for Oracle Databases, then went to settings > source types. I verified that oracle:audit:unified existed.

I then cloned my Database Input, and changed Source Type from "ID" to "oracle:audit:unified". Yet despite doing this, it seems that the sourcetype is not being applied for some reason.

When I view the Advanced column of the oracle:audit:unified, it has all the rules I would expect for cleaning out my data (specifically the SQL_TEXT column). Yet it doesn't seem to be applied to my search. What am I doing wrong here?

I have not made any changes to props.conf for oracle:audit:unified.

Please see below: 

[Thnai]

hi @jroeser1404 

i have the exact same issue, did you resolve this maybe?