DBConnect 3.x Rising columns not working

[ugruner]

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]

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

[damode]

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

[choronz]

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

[rajacybermak]

Hi Deven,

Try disable inheritance for this folder from windows advanced security settings

Rgds

[damode]

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

[rajacybermak]

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

[damode]

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

[rajacybermak]

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]

Hi Dev,

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

Rgds
Raja

[harsmarvania57]

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

[damode]

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 ?

[reswob4]

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.

[rrsal]

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

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

[damode]

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

[rrsal]

i answer my own question about what i did -> https://answers.splunk.com/answers/578394/splunk-db-connect-field-displays-no-matches-instea.html#an...