Splunk Search

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

ebailey
Communicator

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!

0 Karma

dmaislin_splunk
Splunk Employee
Splunk Employee

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/

0 Karma

ebailey
Communicator

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

Thanks David

0 Karma

ebailey
Communicator

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

0 Karma

ChrisGermer
New Member

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

0 Karma

masonmorales
Influencer

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

0 Karma

masonmorales
Influencer

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

0 Karma

ebailey
Communicator

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_in
pct.component_key,\r\nraw_v2_inpct.fct_value,\r\nraw_v2_inpct.fct_ts,\r\nraw_v2_inpct.device_key\r\nFROM\r\
n
raw_v2_in
pct`\r\n {{WHERE $rising_column$ > ?}}
sourcetype = dw
tail.rising.column = fct_ts
interval = auto
output.timestamp.format = %s
table = raw_v2_in
_pct

0 Karma

ebailey
Communicator

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.

0 Karma

ebailey
Communicator

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.

0 Karma

ebailey
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...