topic Re: DBConnect 3.x Rising columns not working in All Apps and Add-ons

DBConnect 3.x Rising columns not working

ugruner — Tue, 29 Sep 2020 16:05:42 GMT

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

Re: DBConnect 3.x Rising columns not working

rrsal — Wed, 04 Oct 2017 14:25:38 GMT

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

Re: DBConnect 3.x Rising columns not working

harsmarvania57 — Thu, 05 Oct 2017 13:58:02 GMT

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

Re: DBConnect 3.x Rising columns not working

rajacybermak — Tue, 29 Sep 2020 16:34:26 GMT

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

Re: DBConnect 3.x Rising columns not working

damode — Wed, 08 Nov 2017 02:25:06 GMT

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 ?

Re: DBConnect 3.x Rising columns not working

damode — Tue, 29 Sep 2020 16:35:46 GMT

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

Re: DBConnect 3.x Rising columns not working

rajacybermak — Thu, 09 Nov 2017 06:08:42 GMT

Hi Deven,

Try disable inheritance for this folder from windows advanced security settings

Rgds

Re: DBConnect 3.x Rising columns not working

damode — Wed, 15 Nov 2017 00:42:54 GMT

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

Re: DBConnect 3.x Rising columns not working

damode — Wed, 15 Nov 2017 00:45:43 GMT

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

Re: DBConnect 3.x Rising columns not working

rajacybermak — Tue, 29 Sep 2020 16:47:57 GMT

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

Re: DBConnect 3.x Rising columns not working

rajacybermak — Wed, 15 Nov 2017 07:20:35 GMT

Hi Dev,

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

Rgds
Raja

Re: DBConnect 3.x Rising columns not working

damode — Tue, 29 Sep 2020 16:48:46 GMT

Hi Raja,

There is no checkpoint file in that folder. the folder is empty. Probably because the input wasnt created.
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

Re: DBConnect 3.x Rising columns not working

rajacybermak — Thu, 16 Nov 2017 09:08:36 GMT

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.

Re: DBConnect 3.x Rising columns not working

rrsal — Thu, 16 Nov 2017 22:13:17 GMT

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

Re: DBConnect 3.x Rising columns not working

reswob4 — Wed, 18 Jul 2018 18:23:52 GMT

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.

Re: DBConnect 3.x Rising columns not working

choronz — Sun, 13 Jan 2019 10:08:55 GMT

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