topic Re: DBX not outputing SQL DateTime Timestamp in Splunk Search

DBX not outputing SQL DateTime Timestamp

aelliott — Thu, 07 Nov 2013 17:00:44 GMT

I have a Splunk DB Connect input setup that simply runs a sql query to grab events from sql.
I have a template as my output.
The column name is EventDate. I set the timestamp column to EventDate and set $EventDate$ in the template for a different field. Both of these are coming back with blank as the timestamp.
I run the query within the DB Query feature of dbx and it comes back with dates.
This is the only column that is not coming back with data.
I have tried to convert it to a string and do a custom timestamp output format, but I am still getting back blank.

The version of Splunk DB Connect I have tried this with are 1.1.0-179259 and the latest (1.1.1-185178)

Re: DBX not outputing SQL DateTime Timestamp

lukejadamec — Thu, 07 Nov 2013 17:55:41 GMT

You do not need to use $EventDate$. Use the column name as is in the Timestamp field of the Database input configuration screen: EventDate

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Thu, 07 Nov 2013 17:57:16 GMT

Do not use $EventDate$ in the output template?
And yes EventDate is in the timestamp field

Re: DBX not outputing SQL DateTime Timestamp

lukejadamec — Thu, 07 Nov 2013 19:00:55 GMT

Correct, you don't use the $.

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Thu, 07 Nov 2013 19:18:33 GMT

Then how does it know that it is not just apart of the template words? All the other SQL fields have dollar signs around them within the template and are replaced with the SQL values.

EventDate is already in the "Timestamp column" field.

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Thu, 07 Nov 2013 22:34:35 GMT

Apparently this is a known issue per their documentation
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Releasenotes#Known_issues

When using a template as the database output format, timestamp data is not output correctly. (DBX-312)

Re: DBX not outputing SQL DateTime Timestamp

btsay_splunk — Mon, 28 Sep 2020 15:15:49 GMT

{{ where $rising_column$ > ? }} or where ... {{ and $rising_column$ > ? }}

This $rising_column$ is the only valid syntax, it cannot be other column names such as your case $EventData$. Instead, the rising_column will be taken from Text Field "Rising Column", that is the place you put "eventdata" there.

$rising_column$ is only meanful for Tail Input, not for Dump input.

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Tue, 12 Nov 2013 21:55:10 GMT

my rising column is an ID field and that part is working fine, it returns the appropriate number of records, however all the fields are returned except it does not like the datetime column from sql.

Re: DBX not outputing SQL DateTime Timestamp

btsay_splunk — Tue, 12 Nov 2013 22:16:33 GMT

what is your eventdate SQL type? timestamp column for timestamp field name that is eventdate in your case. Currently dbconnect recognizes TIMESTAMP and DATE two SQL types, others will be treated as String in your own format if you provided and be parsed by timestamp parse format that you can set it up in conf file.

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Tue, 12 Nov 2013 22:18:25 GMT

datetime is my SQL type (note: this was working fine before 1.1.1)

is date recognized and not datetime? (For a TIME stamp)?

Re: DBX not outputing SQL DateTime Timestamp

btsay_splunk — Wed, 13 Nov 2013 01:01:42 GMT

Can you use lower case for the column name for a test? Will it be possible that you give me a copy of your local/inputs.conf or just the stanza in this file that you failed in Timestamp.

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Wed, 13 Nov 2013 14:30:09 GMT

I have tried lower casing the column name in the queries to sql as well as the output.timestamp.column and template
host = myHost.company.com
index = myIndex
interval = 60
output.format = template
output.template = (leaving off as it won't fit)
output.timestamp = 1
output.timestamp.column = EventDate
query = SELECT ID,EventDate,Server,Application,CallingApplication,Layer,Category,Level,Duration,Key,Summary,Session,CallingSession,Message FROM dbo.MyLogDB WITH(NOLOCK) WHERE EventDate > '11/07/2013 13:32:00' {{ AND $rising_column$ > ? }}
sourcetype = myLogs
table = MyTable
tail.rising.column = ID

Re: DBX not outputing SQL DateTime Timestamp

btsay_splunk — Mon, 28 Sep 2020 15:16:22 GMT

I used mysql database for a test: here is my SQL:

select * from actor where last_update > TIMESTAMP('11-07-2013') {{ and $rising_column$ > ? }}

It seems to work fine.

I think the comparison of datetime or timestamp will be safer if you convert the string value into timestamp value from function.

if you have other jdbc based db tool, like dbvisualizer, you can test your SQL to see if that is working fine. DBConnect only takes whatever SQL you provided to run jdbc call, if the SQL is working in the tool, it should be running in dbconnect as well.

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Wed, 13 Nov 2013 17:36:00 GMT

Have you tried last_update being a datetime Sql data type?

I can tell the query is running just fine, as it is giving me the correct # of records and filling in every other field.

I will also note that i did test my Query using DBQuery in db connect and it worked fine there as well.

Re: DBX not outputing SQL DateTime Timestamp

btsay_splunk — Mon, 28 Sep 2020 15:16:24 GMT

still in mysql test: I did

select * from actor where last_datetime > TIMESTAMP('11-07-2013') {{ and $rising_column$ > ? }}

the last_datetime is datetime type now.

dbconnect does the following --
1. initial step, takes the sql without {{ ... }} part to run and get values, save the last value of rising_column
2. post init steps, combines the rising_column to a SQL in my case it is:

select * from actor where last_datetime > TIMESTAMP('11-07-2013') and actor_id > ?

and runs it with the saved actor_id as starting record.

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Wed, 13 Nov 2013 18:00:43 GMT

did you have an output type of "template" with the $last_datetime$ within the template as well as your timestamp?

Re: DBX not outputing SQL DateTime Timestamp

btsay_splunk — Mon, 28 Sep 2020 15:16:27 GMT

This is my final stanza in inputs.conf

[dbmon-tail://mydb/mytail13]
interval = 60
output.format = kv
output.timestamp = 1
output.timestamp.column = last_datetime
query = select last_datetime, first_name, actor_id from actor where last_datetime > TIMESTAMP('2013-06-07') {{ and $rising_column$ > ? }}
table = actor
tail.rising.column = actor_id

I didn't see any problem. I used manager UI to configure dbmon.

Re: DBX not outputing SQL DateTime Timestamp

aelliott — Wed, 13 Nov 2013 18:52:39 GMT

the difference between ours is that our output formats are different,
output.format = template
output.template = $EventDate$

Re: DBX not outputing SQL DateTime Timestamp

btsay_splunk — Wed, 13 Nov 2013 19:32:36 GMT

ok, puzzle resolved! The output.template should be:

$timestamp$

The $timestamp$ is the reserved word in DBConnect, you have to use it this way. the is your field name, that can be any text you like.

I am not saying this is a good design, but it is the way it is right now.

Re: DBX not outputing SQL DateTime Timestamp

btsay_splunk — Wed, 13 Nov 2013 19:50:01 GMT

In dbmon, DBConnect allows a "template" output format for outputting event data. If you want to output the timestamp field with timestamp format, the syntax is $timestamp$, DBConnect already knows your timestamp column name, so it uses $timestamp$ for such purpose. Do not use your field name such as $EventDate$ here. The correct template should be:

[text] $timestamp$ [other text]

It will replace/display the timestamp field value properly.