In Splunk, I am trying to index data from sql server table, but not able to add data to index

[nilesh_warwatka]

Hi

Issue: I am trying to index data from sql server, but not able to add data to index.

In splunk I am able to fetch sql server data using “SQL Explorer” and “dbquery”.

To index sqlserver data with splunk, I created “Input” by passing appropriate parameters, but it did not work for me.

Application: Splunk DB Connect 3.0.3 (on Linux server)

Please provide your inputs

Entries with db_inputs.conf file, I also tried to add data to default index as well as custom index. But did not get any success.

[Input_db_log]
connection = Logging
fetch_size = 100
index = idxmssql
input_timestamp_column_fullname = (002) Log.Date.datetime
input_timestamp_column_name = Date
interval = */3 * * * *
max_rows = 1000
mode = advanced
query = SELECT * FROM "Sitecore.Logging"."dbo"."Log"  where id > ? order by id
sourcetype = dbx
tail_rising_column_name = ID
ui_query_catalog = Sitecore.Logging
ui_query_mode = advanced
ui_query_schema = dbo
ui_query_table = Log

[newbie2tech]

Can you check below things

1) Run the query from the db connect ui to see it goes through fine

2) Make sure ID is not alphanumeric and is unique and is incremental

3) can you hardcode the id to start off with for example id>1234 such that intial load knows where to start from and then it takes off from there

4) Once the schedule run is complete, look for data in the index for "All Time" , as it is possible that the events are back dated depending on which column is being treated as event date

5) Make sure you have date column available in the table, if you already have an date column which you would want to use as event date, make that column as the first column( select a.modify_time as event_date , a.* from table1 a where id>1234) , if you do not have date column you may use currentimestamp as event_date, I would call it out explicitly though I know splunk would use upload time as event date just to make sure no other column is playing spoilsport.

6) none of these work, then check you error logs and debug options as suggested by Dave.

[davebrooking]

Have you checked the DB Connect logs for instances of your query being scheduled? You may need to change the logging level to DEBUG to get additional diagnostics.

Dave

[davebrooking]

Should the query attribute be

query = SELECT * FROM "Sitecore.Logging"."dbo"."Log" where id > ? order by id

in your statement you haven't specified which columns you want to select

Dave

[nilesh_warwatka]

" * " represents all the Column from the table , i need all the columns from table.
Note : i have specify each column name manually in query ?
Please correct me if I am missing any thing...

[woodcock]

That was formatting error; fixed now.