All Apps and Add-ons

Splunk DB Connect 2: How to set timestamp format to Java date? Is this a bug or did I do something wrong?

empbetty
Explorer

alt textHi all,

I am connecting a MySQL database to Splunk using DB Connect 2. Everything works fine until I set the Timestamp format. I've got a column called sTime where I've got strings like 2015-07-21 20:00:00 and I choose this column to be Timestamp. I choose Timestamp Format to be Java data and entered yyyy-MM-dd HH:mm:ss. However, I just CAN'T set the format. Whenever I click on set, the Timestamp Format field will change to red and I just CAN'T save the configuration! I've got screenshots, but unfortunately I can't upload them here...

Is this a bug? Or I did something wrong?

Hopefully developer of the app can see this post... Stuck on this for a day...

Thanks!!!

araitz
Splunk Employee
Splunk Employee

This could be DBX-1980. As far as we can tell, it only occurs when users attempt to select a timestamp column that occurs to the left of another column in the table that contains non-ASCII characters. Can you verify if you have any non-ASCII column names?

The workaround is as follows:

 SELECT sGuid, IP, Port, ..., sDate as newDate from ...

Then, use newDate as the timestamp column.

We hope to have this fixed in the next release of DBX, but as always this is just an intention and is not a guarantee.

robertlabrie
Path Finder

Same. Latest Splunk Enterprise and DBX2 as of today's date. Both running on Win7 x64. MySQL on Debian 8. Latest jar for driver. Same drama, no worky until I did custom SQL and put it at the end of the row.

Not that it mattered, dbx still merrily ignored my timestamp format.

feature request!:::::: Please validate the timestamp I gave against the data you've already retrieved to see if the extracted date is what I want.

0 Karma

juddnick
Explorer

Ok so I have now got it to work and on the way learnt a few things:

1) The GUI doesn't work well in some cases - you are better off editing the inputs.conf file directly
2) The timestamp column doesn't work as the first column returned by the SQL query (at least for me) but does work as the last
3) When debugging timestamp issues with lots of data, try reducing the batch size or do what I had to and add a new field to your SQL query each time so you can recognise the data in Splunk (Select 'first' AS Test). I initially had 10,000 batch size and data spread over several months so it was hard to see where my changes ended up.
4) Restart splunk after each change - for me this flushed the events in the processing queues. It turns out earlier changes HAD worked but the data I was seeing was still the previous batches being processed.

My config which works:

[mi_input://PROD]
connection = PROD
index = test_test
input_timestamp_column_name = st
input_timestamp_column_number = 33
interval = 100
max_rows = 1000
mode = tail
output_timestamp_format = yyyy-MM-dd HH:mm:ss.SSS
query = select 'first' AS Test,EventSequence,TextData,DatabaseID,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,Duration,StartTime,EndTime,Reads,Writes,CPU,RowCounts,Permissions,Severity,Success,EventClass,EventSubClass,ObjectID,ObjectType,ObjectName,DatabaseName,FileName,OwnerName,RoleName,TargetUserName,DBUserName,ParentName,SessionLoginName, CONVERT(varchar,StartTime,121) AS st from fn_trace_gettable ('D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\tracefile.trc',1) where StartTime IS NOT NULL AND EventSequence IS NOT NULL AND EventClass!=14 AND EventClass!=15
source = audit
sourcetype = audit
tail_follow_only = 1
tail_rising_column_name = EventSequence
tail_rising_column_number = 1
ui_query_mode = advanced
tail_rising_column_checkpoint_value = 3429579512
input_timestamp_format = yyyy-MM-dd HH:mm:ss.SSS

BorisGrochalski
Explorer

Glad to hear that it works now!
To add to your points:
1) and 2) : Same for me

3): What helped me immensely to differentiate between the multiple timestamp configs I tested was: Everytime I would change something in the inputs.conf, I would set a new value for source, in which I would briefly describe what I did (for example: source = input_timestamp_format-lowercaseyyyy).
Also I used a very small batch size of 10 or so events, with timestamps I thought had the most potential for timestamp-errors and an ID column with values from 1-10 on which I would tail.
After I was done with the changes I would reset "tail_rising_column_checkpoint_value" to 0 and save the file.

4) For me and my small batch size it was enough to trigger the "/debug/refresh" command in the browser to reload my .conf files after I did the changes and the events with the new configuration would come in right away.
But with larger batches, restarting Splunk is probably better.

BorisGrochalski
Explorer

This worked for me too. However all other column names were also in ASCII Format.

@juddnick:
Did you try using lowercase yyyy for the year? I have a similar looking timestamp (without the milliseconds).
When I was using uppercase YYYY, DBX would only recognise the time, but not the date. For that it would use the date of index time.
After switching to lowercase yyyy, it worked.
MySQL DB with timestamp column in varchar Format.

juddnick
Explorer

Thanks Boris - but unfortunately that didn't work. It is odd - I have:

1) inputs.conf: input_timestamp_format = yyyy-MM-dd HH:mm:ss.SSS
2) timestamp as output in the SQL data: 2015-07-23 13:33:39.243
3) inputs.conf: output_timestamp_format = yyyy-MM-dd HH:mm:ss.SSS
4) timestamp Splunk has added to the data: "2015-12-29 13:33:39" (so December??? with no milliseconds)
5) Index time = 30/07/2015 13:33:39.000 (ie today with the correct time but no milliseconds)

So I think Splunk decides December this year is too far in advance so uses today's date, but how did it come up with december in the first place??? And where are my milliseconds? Any help appreciated! Thanks

0 Karma

juddnick
Explorer

This worked for me - I had exactly the same issue. No combination of timestamp format worked in the GUI text box when the timestamp was the first column in the returned data however when I moved the timestamp column to the end of the select statement it allowed the format to be entered.

The adjacent column however was a normal text field with the column name "TextData" - all ASCII in the name but there may have been non-ASCII in the actual data somewhere.

It then populated the inputs.conf file with "input_timestamp_format = YYYY-MM-dd HH:mm:ss.SSS" however this still does not seem to work recognising the timestamp value of 2015-07-23 07:35:30.557 - which as far as I can tell is what is required from http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html as specified in the documentation for DBX...

BorisGrochalski
Explorer

I'm having a similar issue connecting to an Oracle DB on Splunk 6.2.1 and DB Connect 2.0.4. None of the timestamp formats i put into the input field is accepted.
Could not resolve it.

0 Karma

empbetty
Explorer

bump!! still unsolved!!

0 Karma

dolivasoh
Contributor

I also have/had this same problem. I had three people look to make sure I wasn't crazy. I finally got it working by installing the most up to date version, changing my timestamp to char type in my query, and then inputting my format string, backing out, then back in and clicking save about 3 times. Sounds crazy but its definitely a bug that needs looking at.

0 Karma

empbetty
Explorer

I've tried everything but it still doesn't work... Hopefully someone can see this and fix this bug soon!

0 Karma

ppablo
Retired

Hi @empbetty

If you have images you'd like users to see to better understand your problem, you can always host the images on another site and just edit your post to include the links to those screenshots.

0 Karma

empbetty
Explorer

uploaded!!!!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...