Splunk DB Connect: Time conversion error while working with SQL Audit log data

jasonsun — Tue, 18 Dec 2018 08:18:05 GMT

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.

Re: Splunk DB Connect: Time conversion error while working with SQL Audit log data

harsmarvania57 — Tue, 18 Dec 2018 08:52:42 GMT

Hi,

Can you please try to convert event_time using TO_CHAR(event_time, 'YYYY-MM-DD HH24:MI:SS.FF3') evt_time ?

topic Splunk DB Connect: Time conversion error while working with SQL Audit log data in Splunk Search

Splunk DB Connect: Time conversion error while working with SQL Audit log data

Re: Splunk DB Connect: Time conversion error while working with SQL Audit log data