I have a SQL query using at Splunk DB Connect to pull the SQL audit log into Splunk as below:
SELECT event_time, action_id, succeeded, session_id, server_principal_id, database_principal_id, object_id, class_type, session_server_principal_name, server_principal_name, database_principal_name, target_server_principal_name, server_instance_name, database_name, schema_name, object_name, statement, file_name, audit_schema_version, transaction_id FROM sys.fn_get_audit_file ('C:\\\\SQLAudit\\\\*',default,default) WHERE event_time > ? ORDER BY event_time ASC
However, the system return error:
"java.sql.SQLException: Conversion failed when converting date and/or time from character string."
The raw event_time field return value in format:
2018-08-26 10:29:57.3456782
I havw tried to do conversion as below but no luck:
SELECT substr('2018-08-26 10:29:57.3456782', 1, 19) as evt_time,
TO_DATE(substr('2018-08-26 10:29:57.3456782', 1, 19), 'YYYY-MM-DD HH24:MI:SS') as evt_datetime,
to_char(TO_DATE(substr('2018-08-26 10:29:57.3456782', 1, 19), 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDD HH24MISS') as evt_datetime
Anyone can give me a hand on this? Thanks.
