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!
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?
giving this is a shot - I had the same time stamp format but "output.timestamp" was not in the input config
Thanks David
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
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
Time since epoch (without a decimal) would be configured as: TIME_FORMAT = %s
Please post your inputs.conf from the dbx folder, along with a sample value for your rising column if you are still having trouble.
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\n
raw_v2_inpct.
fct_value,\r\n
raw_v2_inpct.
fct_ts,\r\n
raw_v2_inpct.
device_key\r\nFROM\r\
raw_v2_inpct`\r\n {{WHERE $rising_column$ > ?}}
n
sourcetype = dw
tail.rising.column = fct_ts
interval = auto
output.timestamp.format = %s
table = raw_v2_in_pct
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.
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.
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