Getting Data In

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

wsnyder2
Path Finder

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?

0 Karma

nadid
Path Finder

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

0 Karma

wsnyder2
Path Finder

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

0 Karma

wsnyder2
Path Finder

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.

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

wsnyder2
Path Finder

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...