Splunk Search

DBX not outputing SQL DateTime Timestamp

aelliott
Motivator

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)

1 Solution

btsay_splunk
Splunk Employee
Splunk Employee

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.

View solution in original post

btsay_splunk
Splunk Employee
Splunk Employee

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.

aelliott
Motivator

This resolution worked, Thank You!!

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

aelliott
Motivator

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

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

aelliott
Motivator

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

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

aelliott
Motivator

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.

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

aelliott
Motivator

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

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

aelliott
Motivator

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

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

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

aelliott
Motivator

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.

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

{{ 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.

0 Karma

lukejadamec
Super Champion

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

aelliott
Motivator

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)

aelliott
Motivator

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.

0 Karma

lukejadamec
Super Champion

Correct, you don't use the $.

0 Karma

aelliott
Motivator

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

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 ...