Home
%3CLINGO-SUB%20id%3D%22lingo-sub-481759%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2383%3A%20How%20to%20create%20my%20own%20SQL%20Auditing%20file%20in%20Azure%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481759%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Team%2C%3C%2FP%3E%0A%3CP%3EToday%2C%20I%20worked%20in%20a%20new%20service%20request%20that%20our%20customer%20besides%20that%20they%20are%20using%20my%20previous%20article%20about%20%3CU%3E%3CSPAN%20style%3D%22color%3A%20rgb(0%2C%20102%2C%20204)%3B%22%3EMonitoring%20Login-Logout%20events%20in%20Azure%20SQL%20Database%20using%20Extended%20Events%3C%2FSPAN%3E%3C%2FU%3E%20they%20requested%20to%20add%20all%20the%20TSQLs%20executed%2C%20so%2C%20I%20added%20more%20events%20for%20taking%20these%20TSQL%20executions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ECREATE%20EVENT%20SESSION%20%5BLoginAudit%5D%0AON%20DATABASE%0AADD%20EVENT%20sqlserver.login(%20ACTION(sqlserver.client_app_name%2Csqlserver.client_connection_id%2Csqlserver.client_hostname%2Csqlserver.database_id%2Csqlserver.database_name%2Csqlserver.session_id%2Csqlserver.username))%2C%0AADD%20EVENT%20sqlserver.logout(%20ACTION(sqlserver.client_app_name%2Csqlserver.client_connection_id%2Csqlserver.client_hostname%2Csqlserver.database_id%2Csqlserver.database_name%2Csqlserver.session_id%2Csqlserver.username))%2C%0AADD%20EVENT%20sqlserver.attention(%0AACTION(package0.event_sequence%2Csqlserver.client_app_name%2Csqlserver.client_pid%2Csqlserver.database_id%2Csqlserver.database_name%2Csqlserver.query_hash%2Csqlserver.session_id%2Csqlserver.sql_text%2Csqlserver.transaction_id%2Csqlserver.transaction_sequence%2Csqlserver.username)%0AWHERE%20(%5Bpackage0%5D.%5Bequal_boolean%5D(%5Bsqlserver%5D.%5Bis_system%5D%2C(0))))%2C%0AADD%20EVENT%20sqlserver.existing_connection(SET%20collect_options_text%3D(1)%0AACTION(package0.event_sequence%2Csqlserver.client_app_name%2Csqlserver.client_pid%2Csqlserver.database_id%2Csqlserver.database_name%2Csqlserver.query_hash%2Csqlserver.session_id%2Csqlserver.sql_text%2Csqlserver.transaction_id%2Csqlserver.transaction_sequence%2Csqlserver.username))%2C%0AADD%20EVENT%20sqlserver.rpc_completed(%0AACTION(package0.event_sequence%2Csqlserver.client_app_name%2Csqlserver.client_pid%2Csqlserver.database_id%2Csqlserver.database_name%2Csqlserver.query_hash%2Csqlserver.session_id%2Csqlserver.sql_text%2Csqlserver.transaction_id%2Csqlserver.transaction_sequence%2Csqlserver.username)%0AWHERE%20(%5Bpackage0%5D.%5Bequal_boolean%5D(%5Bsqlserver%5D.%5Bis_system%5D%2C(0))))%2C%0AADD%20EVENT%20sqlserver.sql_batch_completed(%0AACTION(package0.event_sequence%2Csqlserver.client_app_name%2Csqlserver.client_pid%2Csqlserver.database_id%2Csqlserver.database_name%2Csqlserver.query_hash%2Csqlserver.session_id%2Csqlserver.sql_text%2Csqlserver.transaction_id%2Csqlserver.transaction_sequence%2Csqlserver.username)%0AWHERE%20(%5Bpackage0%5D.%5Bequal_boolean%5D(%5Bsqlserver%5D.%5Bis_system%5D%2C(0))))%2C%0AADD%20EVENT%20sqlserver.sql_batch_starting(%0AACTION(package0.event_sequence%2Csqlserver.client_app_name%2Csqlserver.client_pid%2Csqlserver.database_id%2Csqlserver.database_name%2Csqlserver.query_hash%2Csqlserver.session_id%2Csqlserver.sql_text%2Csqlserver.transaction_id%2Csqlserver.transaction_sequence%2Csqlserver.username)%0AWHERE%20(%5Bpackage0%5D.%5Bequal_boolean%5D(%5Bsqlserver%5D.%5Bis_system%5D%2C(0))))%0AADD%20TARGET%20package0.asynchronous_file_target(%0ASET%20filename%3D'https%3A%2F%2Fxxxx.blob.core.windows.net%2Fextended%2FDemoPersistedError.xel')%0AWITH%20(MAX_MEMORY%3D4096%20KB%2CEVENT_RETENTION_MODE%3DALLOW_SINGLE_EVENT_LOSS%2CMAX_DISPATCH_LATENCY%3D30%20SECONDS%2CMAX_EVENT_SIZE%3D0%20KB%2CMEMORY_PARTITION_MODE%3DNONE%2CTRACK_CAUSALITY%3DON%2CSTARTUP_STATE%3DOFF)%0AGO%0A%3C%2FPRE%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-481759%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Team%2C%3C%2FP%3E%0A%3CP%3EToday%2C%20I%20worked%20in%20a%20new%20service%20request%20that%20our%20customer%20besides%20that%20they%20are%20using%20my%20previous%20article%20about%20%3CU%3E%3CSPAN%20style%3D%22color%3A%20rgb(0%2C%20102%2C%20204)%3B%22%3EMonitoring%20Login-Logout%20events%20in%20Azure%20SQL%20Database%20using%20Extended%20Events%3C%2FSPAN%3E%3C%2FU%3E%20they%20requested%20to%20add%20all%20the%20TSQLs%20executed%2C%20so%2C%20I%20added%20more%20events%20for%20taking%20these%20TSQL%20executions.%3C%2FP%3E%3C%2FLINGO-TEASER%3E

Hello Team,

Today, I worked in a new service request that our customer besides that they are using my previous article about Monitoring Login-Logout events in Azure SQL Database using Extended Events they requested to add all the TSQLs executed, so, I added more events for taking these TSQL executions.

 

CREATE EVENT SESSION [LoginAudit]
ON DATABASE
ADD EVENT sqlserver.login( ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.username)),
ADD EVENT sqlserver.logout( ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.username)),
ADD EVENT sqlserver.attention(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)),
ADD EVENT sqlserver.rpc_completed(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_starting(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))
ADD TARGET package0.asynchronous_file_target(
SET filename='https://xxxx.blob.core.windows.net/extended/DemoPersistedError.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Enjoy!