Hi,
we use DB connect to monitor database jobs. Each job generates an entry. the field log_number is an increasing counter, the fiels log_entrydate 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.
Cheers,
Andy
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)
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.
Query Snippet:
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:
TIME_FORMAT=%Y-%m-%d %H:%M:%S
TZ=UTC
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)
Hi,
thanks for the hint. Finally I got a it working.
1) SELECT TO_TIMESTAMP(TO_CHAR(LOG_ENTRYDATE),'YYYY-MM-DD HH24:MI:SS.FF') LOG_ENTRYDATE,
2) SELECT TO_CHAR (TO_TIMESTAMP (LOG_ENTRYDATE, 'YYYY-MM-DD HH24:MI:SS,FF6'),
'YYYY-MM-DD HH24:MI:SS.FF3')
LOG_ENTRYDATE,
Both should work, for me solution 1) was just fine.
Cheers,
Andy
Hi Sigi,
find below the definition of the log_entrydate field.
LOG_ENTRYDATE VARCHAR2 YES 29 0 10
cheers,
Andy
What's the datatype of the log_entrydate
column? Is it VARCHAR (or similar) or is it an actual TIMESTAMP)?