All Apps and Add-ons

Timestamp recognition with DBCONNECT app.

jlhamlet
Path Finder

Hi,

I am using DB CONNECT app to collect data coming from a MySQL database.

The table I am querying has 4 fields. Two of these fields are dates (in different formats). After being indexed in Splunk, the events look as follows:

**datetime1=1404374979 field2=value **datetime2="2014/07/04 - 23:45:00" field4=value

I wish to force Splunk to use datetime1 (Epoch format) as Timestamp.

What options do i have to apply to my props.conf to do so ?

I am thinking on TIME_FORMAT and TIME_PREFIX, but not sure of what option to use.

Also, do i have to clean my index so that changes can apply ?

Thank you for your help.

Regards

0 Karma

musskopf
Builder

I don't think you need to edit your props.conf. Have a look in your dbx app Input config (splunk/etc/apps/dbx/local/inputs.conf). Most of the configuration can be made via the GUI as well. You should have something like that:

[dbmon-tail://MyDB/dbTail01]
index = my_dbTail01<br> interval = 15 * * * *
output.format = kv
output.timestamp = 1
output.timestamp.column = datetime1
query = SELECT datetime1, field2, datetime2, field4 from TABLE1 {{WHERE $rising_column$ > ?}}
sourcetype = something
table = TABLE1
tail.rising.column = datetime1
disabled = 0

Also there is an additional parameter you might want use if DB Connect doesn't understand the "datetime1", that is:

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

where you specify the date format you DB will spit out. You might need to use it together with your Query. Look for the function "FROM_UNIXTIME" from MySQL doc.

Also DBX output.timestamp.format uses the Java SimpleDateFormat (sorry I the system didn't allow me to post the link but just google it if you need).

sroback_splunk
Splunk Employee
Splunk Employee

Hi. You might try to have your SQL query do the conversion using CAST or CONVERT to TIMESTAMP.

For more info on working with timestamps in DB Connect, see:
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#About_timestam...

For general info on configuring Splunk timestamp recognition, see: http://docs.splunk.com/Documentation/Splunk/latest/Data/Configuretimestamprecognition

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