Splunk Search

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

jasonsun
Explorer

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.

Tags (1)
0 Karma

harsmarvania57
Ultra Champion

Hi,

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

0 Karma
Get Updates on the Splunk Community!

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Enhance Security Operations with Automated Threat Analysis in the Splunk EcosystemAre you leveraging ...

Splunk Developers: Go Beyond the Dashboard with These .Conf25 Sessions

  Whether you’re building custom apps, diving into SPL2, or integrating AI and machine learning into your ...

Index This | How do you write 23 only using the number 2?

July 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...