- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
DBConnect 3.x Rising columns not working
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Got the same error for v3. Adding the where clause seems to CustomerID > ? ORDER by CustomerID ASC solves the error.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Deven,
Try disable inheritance for this folder from windows advanced security settings
Rgds
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Dev,
Note : You need to execute the query once to let the values for the Rising Column selector to be populated .
Rgds
Raja
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @rrsal,
If you were able to resolve this issue, please share your solution.
Thanks,
Dev
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...