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...
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.
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.
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:
connection = PROD
index = testtest
inputtimestampcolumnname = st
inputtimestampcolumnnumber = 33
interval = 100
maxrows = 1000
mode = tail
outputtimestampformat = 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 fntracegettable ('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
tailfollowonly = 1
tailrisingcolumnname = EventSequence
tailrisingcolumnnumber = 1
uiquerymode = advanced
tailrisingcolumncheckpointvalue = 3429579512
inputtimestamp_format = yyyy-MM-dd HH:mm:ss.SSS
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 = inputtimestampformat-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 "tailrisingcolumncheckpointvalue" 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.
This worked for me too. However all other column names were also in ASCII Format.
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.
Thanks Boris - but unfortunately that didn't work. It is odd - I have:
1) inputs.conf: inputtimestampformat = 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: outputtimestampformat = 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
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 "inputtimestampformat = 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...
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.
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.
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.