All Apps and Add-ons
Highlighted

How do I get Splunk DB Connect 2 to recognize the Timestamp column in my MS SQL database?

Path Finder

I have a MS-SQL database with a column "lastupdatedate" which is epoch time (10 digit). However, when I tell DB Connect v2 to use that column (with the format selected as epoch time), it fails to import the database new rows at the next schedule (I make test changes in between intervals).

If I change nothing else except making the timestamp "Current index time" instead of choosing a column, it works.

I'm also currently using the Type "Rising Column" (same "lastupdatedate" column) with "Follow Tail" enabled.

Any suggestions on how to get DB Connect v2 to recognize the timestamp column?

Highlighted

Re: How do I get Splunk DB Connect 2 to recognize the Timestamp column in my MS SQL database?

Contributor

Using the timestamp as the rising column may have some issues being serializable Imagine two rows being written on the same second. DB Connect would see the same values in a field it expects to increment.

For your timestamp issue, perhaps try using %s as the input format and or leaving it off and converting it at search time using | eval time = latestupdate_date

0 Karma
Highlighted

Re: How do I get Splunk DB Connect 2 to recognize the Timestamp column in my MS SQL database?

Path Finder

I switched the output timestamp format to %s in the inputs.conf file, but this gave an error:

[ERROR] [websocket.py] ERROR: The datatype of output timestamp column number [41] is invalid as [int]..

Did not import the altered records from the DB either.

Not sure I understand the rising column statement. I was under the impression that the rising column was something DB Connect v2 reads to find if it has incremented. And this is how it determines if it has been updated. It should not be trying to write to the specified column.

0 Karma
Highlighted

Re: How do I get Splunk DB Connect 2 to recognize the Timestamp column in my MS SQL database?

Path Finder

Just realized that you told me to do %s as the input format, not the output timestamp format. There isn't really a place to specify this. When selecting the timestamp column, it provides two options; epoch time or java time. I have always had the epoch time selected. Even in the inputs.conf for this app there is not input format.

Here's my inputs.conf

[mi_input://ca_owned_resource]
connection = CMDB
index = main
input_timestamp_column_name =
input_timestamp_column_number =
interval = 16 * * * *
max_rows = 100000
mode = tail
output_timestamp_format = YYYY-MM-dd HH:mm:ss
query = SELECT * FROM "mdb"."dbo"."ca_owned_resource"
source = ca_owned_resource
sourcetype = dbx2
ui_query_catalog = mdb
ui_query_mode = simple
ui_query_schema = dbo
ui_query_table = ca_owned_resource
tail_follow_only = 1
tail_rising_column_name = last_update_date
tail_rising_column_number = 41
tail_rising_column_checkpoint_value = 1430149072
0 Karma