In Splunk DB Connect, why is field data truncating after double quotes?

[sureshkrovi]

Hi,

I've indexed Oracle table data that has a field to store special character "(double quote). I see Splunk trim the data after double code while ingesting the field into index.

e.g DB column ERROR_STACK has value

-ORA-06512: at "ApplicationName.PKG_SNP", line 4804
ORA-06512: at "ApplicationName.PKG_SNP", line 4553
ORA-06512: at "ApplicationName.PKG_SNP", line 4302
ORA-06512: at "ApplicationName.PKG_SNP", line 4051

Index field in Splunk has only -ORA-06512: at

Is there a way to get the full field value added in Splunk that has special characters?

[Richfez · Accepted solution]

Splunk actually has this exact issue documented "Incomplete field values are extracted when the value contains double quotes"

The offer that you can make your own extractions for that data, which does make sense. The link above has that information in it and pointers to the information you'll need to review.

You could also possibly replace that in your SQL.

So, a quick note - doing a select * is only ever recommended for testing purposes. You should specify all your fields for three important reasons:
1) So that you know you need all those fields or have at least reviewed them.
2) Because rising columns are now specified by field number, and unless you SELECT Field1, Field2, Field3 ... you can't guarantee that the right field will always stay in the right location.
3) Performance. While not usually a huge problem, the SQL engine having to enumerate all the fields before it can even start can affect the performance of the query overall.

Anyway.

So it's in Oracle and I'm not familiar with it too much, but a pseudo-syntax is below...

SELECT Field1, REPLACE(Field2, "\"", "'") AS Field2, Field 3...

That ... well, it's pseudo-code so unless I accidentally got it right, it won't work anywhere. But the idea is that for Field2, to replace all regular-quotes with single-quotes while you are reading it from the DB, so Splunk only ever sees the data with the single quote marks in it.

Anyway, give those a try, see if they get you where you need to be!

Happy Splunking,
Rich

[sureshkrovi]

Thanks Rich,It worked.Here is the SQL version I used
SELECT ID,
ERROR_ID,
REGEXP_REPLACE(ERROR_STACK,
'[^0-9A-Za-z :._,]', '') AS ERROR_STACK,MESSAGE,SYSTEM_ERROR_CODE,SYSTEM_ERROR_MESSAGE, ENVIRONMENT_INFO,CREATED_ON,CREATED_BY,CHANGED_ON,CHANGED_BY
FROM Q$ERROR_INSTANCE
WHERE ID > ?
ORDER BY ID ASC

[Richfez]

Thank you for providing the exact solution you used! That will help people who find this answer in a search, later! It is much appreciated.

[Richfez]

Could you provide your db_inputs.conf stanza for this input? Or at least the query involved?

(And be sure to check it for sensitive information)

[sureshkrovi]

Here is db_inputs.conf

[default]
query_timeout = 30
max_rows = 0
fetch_size = 300
batch_upload_size = 1000
# 10MB
max_single_checkpoint_file_size = 10485760
index = default

(Edit - I changed the conf file entry to "code" so it doesn't do goofy things... 🙂 )

[sureshkrovi]

Oracle SQL query is straight forward
SELECT * FROM tablename WHERE ID > ?
ORDER BY ID ASC