Splunk DB Connect: I have a database input with a query, but why is no data being captured by the tailing input?

[ebailey]

I am trying to pull a large amount of data from a data warehouse into Splunk. I have a database input setup with a query but no data is being captured by the tailing input.

I see the following in the dbx.log

2015-02-05 10:49:13.719 dbx663:INFO:TailDatabaseMonitor - Executing database monitor=[dbmon-tail://zenoss_reporting/raw_v2_in__pct]
2015-02-05 10:49:13.725 dbx663:INFO:TailDatabaseMonitor - Applying latest tail.rising.column value=2015-01-28 16:05:00.0
2015-02-05 10:49:18.767 dbx663:INFO:TailDatabaseMonitor - Database monitor=[dbmon-tail://reporting/raw_v2_in__pct] finished with status=true resultCount=0 in duration=5048 ms
2015-02-05 10:49:18.767 dbx663:INFO:ExecutionContext - Execution finished in duration=5048 ms
2015-02-05 10:49:18.767 monsch1:INFO:Scheduler - Execution of input=[dbmon-tail://reporting/raw_v2_in__pct] finished in duration=5048 ms with resultCount=0 success=true continueMonitoring=true
2015-02-05 10:49:35.720 dbx4712:INFO:TailDatabaseMonitor - Executing database monitor=[dbmon-tail://reporting/raw_v2_in__pct]
2015-02-05 10:49:35.726 dbx4712:INFO:TailDatabaseMonitor - Applying latest tail.rising.column value=2015-01-28 16:05:00.0
2015-02-05 10:49:40.295 dbx4712:INFO:TailDatabaseMonitor - Database monitor=[dbmon-tail://reporting/raw_v2_in__pct] finished with status=true resultCount=0 in duration=4574 ms
2015-02-05 10:49:40.295 dbx4712:INFO:ExecutionContext - Execution finished in duration=4575 ms
2015-02-05 10:49:40.296 monsch1:INFO:Scheduler - Execution of input=[dbmon-tail://reporting/raw_v2_in__pct] finished in duration=4574 ms with resultCount=0 success=true continueMonitoring=true
2015-02-05 10:49:40.557 dbx8545:INFO:Splunkd - Splunkd REST Keep-alive successful for user splunk-system-user
2015-02-05 10:49:40.557 dbx8545:INFO:ExecutionContext - Execution finished in duration=8 ms

My query:

SELECT
raw_v2_in__pct.component_key,
raw_v2_in__pct.fct_value,
raw_v2_in__pct.fct_ts,
raw_v2_in__pct.device_key
FROM
raw_v2_in__pct
{{ WHERE $rising_column$ > ?}}

The rising column is fct_ts

The times tamp column is fct_ts as well

I left time stamp format field blank. The data is coming across when I run a query in Db Connect as time since the epoch so I am not sure how to handle that as a format.

Any ideas?

Thanks!

[dmaislin_splunk]

Ed,

Have you tried this?

output.timestamp = 1
output.timestamp.format = %Y-%m-%d %h:%i:%s

And if the timestamp isn't being recognized properly, have you read this?

http://blogs.splunk.com/2014/04/23/its-that-time-again/

[ebailey]

giving this is a shot - I had the same time stamp format but "output.timestamp" was not in the input config

Thanks David

[ebailey]

The app was not pleased with the timestamp format and returned an xml parsing error. I had better luck with

output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss

and I adjusted the query to use '%Y-%m-%d %h:%m:%s as the desired data timestamp.

Now I see data flowing but also a few thousand of these error messages

Unrecognized timestamp format: '2014-12-30 06:12:00'
2015-02-11 13:36:30.265 dbx8902:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '2014-12-30 06:12:00'
2015-02-11 13:36:30.265 dbx8902:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '2014-12-30 06:12:00'

I am checking data quality now.

Thanks

Ed

[ChrisGermer]

hi ed!
your comment helped me a lot!! thank u very much 🙂 i was facing this problem for days...
instead of to_date in the sql-query, i work with FROM_UNIXTIME. this works better for me. i now get the right timestamps in splunk.
but... i also get an amount of unnecessary messages:

dbx2417:WARN:ResultSetOutputPayload - NULL Timestamp value in column=ColumnInfo{idx=1, sqlType=93, columnName='sn_creationdate'}

thx,

chris

[masonmorales]

Time since epoch (without a decimal) would be configured as: TIME_FORMAT = %s

[masonmorales]

Please post your inputs.conf from the dbx folder, along with a sample value for your rising column if you are still having trouble.

[ebailey]

input

[dbmon-tail://reporting/raw_v2_in_pct]
host = dw
index = dw
output.format = kv
output.timestamp = 0
output.timestamp.column = fct_ts
query = SELECT \r\n `raw_v2_inpct.component_key,\r\nraw_v2_inpct.fct_value,\r\nraw_v2_inpct.fct_ts,\r\nraw_v2_inpct.device_key\r\nFROM\r\
nraw_v2_inpct`\r\n {{WHERE $rising_column$ > ?}}
sourcetype = dw
tail.rising.column = fct_ts
interval = auto
output.timestamp.format = %s
table = raw_v2_in_pct

[ebailey]

data sample

| component_key | fct_value | fct_ts | device_key |
+---------------+-----------------+---------------------+------------+
| 658608 | 0.194550544228 | 2014-12-29 00:00:00 | 658569 |

The mysql client converts the time to a standard time stamp but the data shows up as epoch in the db connect query tool.

[ebailey]

update - I played around the query and made some progress but now have a new error

updated query for mysql

SELECT
raw_v2_in__pct.component_key,
raw_v2_in__pct.fct_value,
date_format(raw_v2_in__pct.fct_ts, '%Y-%m-%d %h:%i:%s') AS fct_ts,
raw_v2_in__pct.device_key
FROM
raw_v2_in__pct
WHERE
raw_v2_in__pct.component_key = 658608 OR
raw_v2_in__pct.component_key = 698395 OR
raw_v2_in__pct.component_key = 658632 OR
raw_v2_in__pct.component_key = 698453 OR
raw_v2_in__pct.component_key = 658572 OR
raw_v2_in__pct.component_key = 698478 OR
raw_v2_in__pct.component_key = 658591 OR
raw_v2_in__pct.component_key = 698381 OR
raw_v2_in__pct.component_key = 658692 OR
raw_v2_in__pct.component_key = 698392 {{AND $rising_column$ > ?}}

error message

2015-02-06 14:52:51.545 dbx937:INFO:TailDatabaseMonitor - Applying latest tail.rising.column value=2015-01-28 16:05:00.0
2015-02-06 14:52:54.798 dbx937:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '2014-12-29 12:00:00'

I have the timestamp formatted in two places. Which is which? Why would the year be off? I can run this query from the command line and I get the right results.

[ebailey]

timestamp format

yyyy-MM-dd HH:mm:ss

from reading the docs and splunk answers I cant get a clear understanding of the proper format so this is a guess