topic How to prevent fields automatically extracted by DB Connect from being truncated? in Getting Data In

How to prevent fields automatically extracted by DB Connect from being truncated?

wsnyder2 — Wed, 20 Aug 2014 12:44:27 GMT

Hello, we just started using dbconnect and both my users complain that field values extracted by Splunk are truncated. These fields are length error messages from a database table that may contain practically any character. It seems results get truncated sometimes at spaces, colons :, and CR/LF. Any suggestions on how to debug this?

Re: How to prevent fields automatically extracted by DB Connect from being truncated?

richgalloway — Wed, 20 Aug 2014 15:18:49 GMT

Any details you can share about your database and your query would be helpful.

Re: How to prevent fields automatically extracted by DB Connect from being truncated?

jcoates_splunk — Wed, 20 Aug 2014 16:46:25 GMT

How long are the values that are getting truncated? Greater than 1024 characters? It's not uncommon for databases to truncate these, you can probably alter the SQL statement to fix it.

Re: How to prevent fields automatically extracted by DB Connect from being truncated?

wsnyder2 — Mon, 28 Sep 2020 17:23:11 GMT

Thanks for the question.

We have datasources reading from both Oracle and MSSQL.
type = mssql, type = oracle, from database.conf

Sample queries, from inputs.conf;
mssql, query = select ID, Date, Hostname, Level, Message, Thread, Logger, ActionId, Action, ActionContext, UserId, CompanyId, Exception,OtherInfo, CookieInfo \r\nfrom dbo.ErrorLog where ID > 31422354 {{AND $rising_column$ > ?}}

oracle, query = SELECT * FROM SPR.V_IA_MESG_SPLUNK WHERE IAM_ID > 1020016850045340 {{AND $rising_column$ > ?}}

These are working. Events are being read into our Splunk indexers.

Re: How to prevent fields automatically extracted by DB Connect from being truncated?

wsnyder2 — Mon, 28 Sep 2020 17:23:14 GMT

Thank you for the suggestion. I crafted the following search ...
index=hro_prod sourcetype="dbmon:mkv" earliest="8/22/2014:07:15:00" latest="8/22/2014:07:25:00" Exception=* | eval a_len=len(Exception) | dedup a_len | table a_len

when I run this (admin) I get, a_len: 57,109,214,81,184,34 ... when my user runs this same search they get a_len: 24,59,33,25,34. Very strange. They don't get the same number of events .. they also don't get the same numbers.

Re: How to prevent fields automatically extracted by DB Connect from being truncated?

wsnyder2 — Tue, 26 Aug 2014 19:48:37 GMT

some additional information about this issue .. seems that those with admin role can see the entire fields, those that are either user or power role, can't see the entire field. The problem happens both with Oracle and MSSQL database reads. We have confirmed/set permissions for sourtype= dbmon:mkv, transforms = dbx-mkv

Re: How to prevent fields automatically extracted by DB Connect from being truncated?

nadid — Fri, 04 Sep 2015 17:40:33 GMT

Dear wsnyder2,

A trick to avoid truncation is add in the dbquery double quotes if possible. So what I usually do is the following:

|dbquery db "select '\"'||tablefield||'\"' as tablefieldname from table "

in this way you have complete field without escaping. I don't like it but somehow it looks like it gets confuse splunk with it.

I hope this helps you,
Nadid