we use DB connect to monitor database jobs. Each job generates an entry. the field lognumber is an increasing counter, the fiels logentrydate is the actual timestamp.
log_entrydate format: 2013-06-19 14:03:12.183506
I tried to extract the timestamp and to get an event for each log_number but was not successful. Configuration:
output.timestamp = true
output.timestamp.column = LOG_ENTRYDATE
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss.SSS --> I don't know how to specify
Any help is appreciated.
DB Connect uses a SimpleDateFormat to parse the timestamp. SimpleDateFormat only support milli-seconds precision, the timestamp in your case seems to too precise (nano-seconds). There are 2 ways to work around this:
1) Parse the timestamp up to the seconds (ie. reduce the precision):
yyyy-MM-dd HH:mm:ss, which should work
2) Use a custom SQL statement and convert the
LOG_ENTRYDATE column to TIMESTAMP (eg. using the
TO_TIMESTAMP function: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions193.htm)
thanks for the hint. Finally I got a it working.
1) SELECT TOTIMESTAMP(TOCHAR(LOGENTRYDATE),'YYYY-MM-DD HH24:MI:SS.FF') LOGENTRYDATE,
2) SELECT TOCHAR (TOTIMESTAMP (LOGENTRYDATE, 'YYYY-MM-DD HH24:MI:SS,FF6'),
Both should work, for me solution 1) was just fine.
Sigi & kochera, your science is pure and appreciated. I tweaked solution slightly for MS-SQL. I'll paste those configs here for anyone else querying MS-SQL.
SELECT CAST([EPOEvents].[ReceivedUTC] as varchar) as [timestamp], ...
Respective parameters in inputs.conf:
output.timestamp = 1 output.timestamp.column = timestamp # format for writing event: 2012-08-03 08:17:00 output.timestamp.format = yyyy-MM-dd HH:mm:ss # format for translating date from query results: Aug 3 2012 8:17AM output.timestamp.parse.format = MMM dd yyyy HH:mmaa
Relevant bit from props.conf: