topic Re: SQL audit log not working when using event_time as indexing in Getting Data In

SQL audit log not working when using event_time as indexing

fl66 — Mon, 11 Nov 2024 16:13:06 GMT

Hi,

I am using the Db connect 3.18.1 to collect sql audit logs FROM sys.fn_get_audit_file function. When I use event_time as the indexing column, no events are collected with no error messages. But when I changed the indexing to be Current, I got the audit events logged to the indexer.

But no logs were collected when I used event_time as indexing column. I did not see any useful or error messages from debug logs. Appreciate any help or tips.

thanks,

Re: SQL audit log not working when using event_time as indexing

PaulPanther — Thu, 14 Nov 2024 08:25:33 GMT

Please share your sql query, table structure and some sample events.

Re: SQL audit log not working when using event_time as indexing

fl66 — Thu, 14 Nov 2024 14:50:54 GMT

I used this. Thank you!

SELECT *
FROM sys.fn_get_audit_file('/tmp/SQLAudit/*',default,default)
WHERE event_time > ?
ORDER BY event_time ASC

Sample data in Splunk with index with current. The site won't allow me to post sql query result in the readable format.

2024-11-11 20:58:14.339, event_time="2024-11-11 15:58:14.3397210", sequence_number="1", action_id="DR ", succeeded="1", is_column_permission="0", session_id="53", server_principal_id="1", database_principal_id="1", target_server_principal_id="0", target_database_principal_id="0", object_id="6", class_type="DB", session_server_principal_name="sa", server_principal_name="sa", database_principal_name="dbo", server_instance_name="u22", database_name="testdb114", object_name="testdb114", statement="drop database testdb114", file_name="/tmp/SQLAudit/MSSQL_Server_Audit_5C4ED78A-BFBD-4C6C-8793-F98B88C55293_0_133757544438840000.sqlaudit", audit_file_offset="20992", user_defined_event_id="0", audit_schema_version="1", transaction_id="852605", client_ip="127.0.0.1", application_name="SQLCMD", duration_milliseconds="0", response_rows="0", affected_rows="0", connection_id="EB46CB4B-CF55-48EA-B497-99D4A04D41FF", host_name="u22", client_tls_version="771", client_tls_version_name="1.2", database_transaction_id="0", ledger_start_sequence_number="0", is_local_secondary_replica="0

Re: SQL audit log not working when using event_time as indexing

PaulPanther — Fri, 15 Nov 2024 12:47:18 GMT

Okay, and you've set following parameter for your input in DB Connect,right?

Rising Column ---> event_time

Checkpoint Value ---> any valid date

Timestamp - Choose Column ---> event_time

Could you share a screenshot of this configuration details?

Try to set a Checkpoint value that is quite close to the current date that you only collect few events.