All Apps and Add-ons

DBConnect 3.x Rising columns not working

ugruner
Explorer

After migration to DBConnect 3.11 my SQL Statement won't work any more. It fails with an error in the UI.

com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1.

I created a new Input in the Ui, first I run the stement with Batchmode by Execute Sql

SELECT [Entry No_] as [Entry_No]
,[Date and Time] as [Date_and_Time]
,[Time] as [Time]
,[User ID] as [User_ID]
FROM [table]

Next I select Rising Column and select Enty_No (bigint) also added the following line to my SQL Statement

WHERE Entry_No > ? ORDER BY Entry_No ASC

When I run Execute SQL again, the above error is displayed.

I created several Inputs with DB Connect 2.x and all of them worked. But now I cannot create a single one with 3.11

rajacybermak
Explorer

In windows environment,
1. Splunk DB connect log files are stored in C:\Program Files\Splunk\var\log\splunk folder
the files are splunk_app_db_connect_*.log
3. Splunk DB connect rising values are stored in C:\Program Files\Splunk\var\lib\splunk\modinputs\server\splunk_app_db_connect
A file with the connection name stores the values of the rising column

If rising columns are are not working, it is possible that " C:\Program Files\Splunk\var\lib\splunk\modinputs\server\splunk_app_db_connect" folder is ready only. So DB Connect is not able to update the check point files with recent values. Change the permissions on the folder/file to and Read+Write and rising columns should start working.

It is not a must to specify each field in the Select statement. We can simply use * , for example
"Select * from "SYSTEM"."CUSTOMERS" where CustomerID > ? ORDER by CustomerID ASC

Regards
Raja Rajesh

0 Karma

damode
Motivator

Hi Raja,

I am trying to follow your method, however, I am unable to remove the read-only attribute from properties of the dbconnect folder. No matter how many times, I uncheck the option or even use this command, [attrib -r "$Splunk_home$\etc\apps\splunk_app_db_connect*.*" ], the folder still shows the read-only option enabled.

If you also have faced this issue and have been able to get around it, please suggest what solution did you implement.

Thanks,
Deven

0 Karma

choronz
New Member

Got the same error for v3. Adding the where clause seems to CustomerID > ? ORDER by CustomerID ASC solves the error.

0 Karma

rajacybermak
Explorer

Hi Deven,

Try disable inheritance for this folder from windows advanced security settings

Rgds

0 Karma

damode
Motivator

Hi Raja,

I have disabled inheritance as well, however, it still shows "read-only (only applies to files in folder)" enabled on the folder. To add another point, files within the folder actually have read-only disabled. So, I am guessing read-only issue is resolved, however, it still shows, "no matches" in the rising column drop down of the dbconnect app. please help.

Rgds,
Dev

0 Karma

rajacybermak
Explorer

Hi Dev
1. Try deleting the check point file in "C:\Program Files\Splunk\var\lib\splunk\modinputs\server\splunk_app_db_connect".
The check point file will automatically be created at the next cycle.

2.Try your query directly in DBConnect - SQL editor and see if the results are as expected

Rgds
Raja

0 Karma

damode
Motivator

Hi Raja,

  1. There is no checkpoint file in that folder. the folder is empty. Probably because the input wasnt created.
  2. that query gave me an error, java.sql.SQLException: The user does not have permission to perform this action, however, I have already been provided db_datareader (i.e. read only) permissions. Below is my query, SELECT * FROM sys.fn_get_audit_file ('C:\\SQLAudit\\*',default,default) WHERE event_time > '2017-11-08 00:12:00.000' ORDER BY event_time ASC
0 Karma

rajacybermak
Explorer

Hi Dev
Does not look like a DB connect issue here. Looks more like a permission issue on the audit file.
For further diagnosis
- Try to read from a regular table (instead of Audit file) in DB Connect as Batch and as rising and see if its working.

rajacybermak
Explorer

Hi Dev,

Note : You need to execute the query once to let the values for the Rising Column selector to be populated .

Rgds
Raja

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Hi @ugruner,

I got the same error first time when I was configuring new input but after that I figuered out that I need to select Connection, Catalog, Schema, Table (All 4 items) and then I ran query it was working fine OR you can write your query as below after selecting Connection.

SELECT "Entry No" as Entry_No,"Date and Time" as Date_and_Time,Time
,"User ID" as User_ID FROM "<CATALOG>"."<SCHEMA>"."<TABLE>" WHERE Entry_No > ? ORDER BY Entry_No ASC

I hope this helps.

Thanks,
Harshil

0 Karma

damode
Motivator

Hi @Harshil,

I am facing the same issue and I tried following your method, however, in my case, there is no selection available in "table" column.

Can you please advise how I can make this work ?

0 Karma

reswob4
Builder

Pick a different schema. This took me a while to figure out, but if you have picked a valid connection, then valid catalog, then a schema, if that schema has tables, they will be listed under 'Table'. If that schema does not have tables, it will be blank. Once you have tables listed, click on the one you want to use.

0 Karma

rrsal
Engager

i think we got the same error. I put a image to explain:
alt text

This happens in every input, including the already made (this are working because are made in dbconnect version 2.x.x). but they can be changed , why? when i try to select a Rising Column give me a "no matches"statement

0 Karma

damode
Motivator

Hi @rrsal,
If you were able to resolve this issue, please share your solution.
Thanks,
Dev

0 Karma

rrsal
Engager
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...