How do I set rising column when executing a script in DBConnect (SQL Server)?

[mmarinov]

I have to read SQL Server logs through the sp_readerrorlog procedure, for which I use the following script, because I cannot use a "WHERE" clause while executing a stored procedure:

IF OBJECT_ID('tempdb..#SPLUNKTmpErrorLog') IS NOT NULL
BEGIN
    DROP TABLE #SPLUNKTmpErrorLog
END

CREATE TABLE [tempdb].[dbo].[#SPLUNKTmpErrorLog]
([LogDate] DATETIME NULL,
[ProcessInfo] VARCHAR(20) NULL,
[Text] VARCHAR(MAX) NULL);

INSERT INTO #SPLUNKTmpErrorLog ([LogDate], [ProcessInfo], [Text])
EXEC [master].[dbo].[sp_readerrorlog] 0, 1;

SELECT 
[LogDate],
[ProcessInfo],
[Text]
FROM [tempdb].[dbo].[#SPLUNKTmpErrorLog]

The script works and returns results, but I cannot pick rising column from the drop down menu.
I understand that using DDL in DBConnect is not a wise thing, but I have no other option.

[amitm05 · Accepted solution]

DBX will only be able to give you the rising column if you are making a query on DB table.
I doubt if for an SP you could define a rising column as the Splunk DBX is not aware of the columns that are there.

I'd suggest to try to include your condition within the SP only.
Thanks

[amitm05]

Please mark it as answer if thats acceptable to you

[mmarinov]

It is a system stored procedure, cannot be altered.

[amitm05]

Went on to do some digging for this problem. Can you confirm which DBX version are you using. If its 1 OR 2 then you dont have the support But with 3.0 SProcs are supported.

However there seems to be still some problem when using it as a DB input and need to set the rising column values for it.
Unwrapping the stored proc and directly using the query in DBX is how some people are doing it plus an adjustment for tail_rising_column in db_inputs.conf directly.

See this post for a more comprehensive explanation:
https://answers.splunk.com/answers/594665/splunk-db-connect-311-stored-procedure-as-input.html

Hope this helps.
Please mark as answer if this would resolve your problem. Thanks

[mmarinov]

DBX is version 3.
Thanks for the link, gave us some nice insights.
We are going a different route though, the DBA will create a table for us, and populate it via SQL Server job.