SQL Server Query History Configuration Query Log Ingestion is done using Extended Events
STEPS
1 Grant Alation Service Account Why is this needed?
VIEW SERVER STATE permission Alation must have access to run sys.fn_xe_file_target_read_file command to read the logged XEL files that extended events creates. VIEW SERVER STATE is required to use that command.
2 Grant Alation Service Account permission to run Why is this needed?
the stored procedure xp_dirtree Alation needs to read the list of logged XEL files that it is going to ingest. This stored procedure lists files in a directory.
3 Create and turn on an extended events session
that logs queries. See Section A for the SQL code for SQL Server versions greater than 2008 and Section B for SQL Server 2008. You will have to change the file path to log the files and may optionally change some of the other param- eters like the buffer size.
4 When you first turn on the session monitor it for a
few hours to see that it is working and the volume of log files. If the volume is growing such that log files will be rolled over within 3 days then turn off logging and contact Alation support who will help with the process of creating a custom extended events session to filter out some of those queries that are not useful.
5 Have the Alation Admin for your instance config-
ure the settings from the Alation UI to point to the files that are being logged. SECTION A
SQL Server 2009+ Events Creation
-- Query To Create Extended Events Session
CREATE EVENT SESSION [alation_query_log] ON SERVER ADD EVENT sqlserver. sp_statement_completed( ACTION(package0.collect_system_time,package0.event_sequence,sqlos.task_ time,sqlserver.cl ient_app_name,sqlserver.database_id,sqlserver.database_ name,sqlserver.nt_username,sqlser ver.server_principal_name,sqlserver. server_principal_sid,sqlserver.session_id,sqlserver.sessio n_nt_user- name,sqlserver.transaction_id,sqlserver.username) WHERE ([statement] like ‘%ALTER %’ OR [statement] like ‘%CREATE %’ OR [statement] like ‘%DROP %’ OR [statement] like ‘%TRUNCATE %’ OR [statement] like ‘%MERGE %’ OR [statement] like ‘%FROM %’ OR [statement] like ‘%USE %’ )), ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
ACTION(package0.collect_system_time,package0.event_sequence,sqlos.task_ time,sqlserver.cl ient_app_name,sqlserver.database_id,sqlserver.data- base_name,sqlserver.nt_username,sqlser ver.server_instance_name,sqlserver. server_principal_name,sqlserver.session_id,sqlserver.sess ion_nt_user- name,sqlserver.transaction_id,sqlserver.username) -- Generic Alation filters, we only make use of certain types of -- statements -- Note SELECT is omitted because SELECT without FROM is not useful -- for our analysis WHERE ([statement] like ‘%ALTER %’ OR [statement] like ‘%CREATE %’ OR [statement] like ‘%DROP %’ OR [statement] like ‘%TRUNCATE %’ OR [statement] like ‘%MERGE %’ OR [statement] like ‘%FROM %’ OR [statement] like ‘%USE %’ )) ADD TARGET package0.event_file( SET filename=N’C:\Users\All Users\Documents\alation_query_log.xel’, -- CONFIGURE THIS OPTIONALLY CONFIGURE THIS, max file size in -- MB before rolling over -- Note Alation will read one file at a time so this is the size of -- file that may be read into memory while it is being fetched. max_file_size=(100), -- in MB max_rollover_files=(100)) -- OPTIONALLY CONFIGURE THIS WITH (MAX_MEMORY=50 MB, -- OPTIONALLY CONFIGURE THIS, the total event buffer size EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, -- If buffer is full events will be dropped instead of blocking -- the server MAX_DISPATCH_LATENCY=30 SECONDS, -- OPTIONALLY CONFIGURE THIS, max time before writing events to -- storage MAX_EVENT_SIZE=0 KB, -- Any events that are too large to fit in the buffer will be -- dropped MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON ) GO -- Query to start the session (change START > STOP to stop it) ALTER EVENT SESSION alation_query_log ON SERVER STATE = START go -- If you need to delete and recreate the session this is the syntax -- to drop it. DROP EVENT SESSION [alation_query_log] ON SERVER GO -- Check if the session is dropping events and see other data about -- the session SELECT * FROM sys.dm_xe_sessions; SECTION B
SQL Server 2008 Events Creation
-- Query To Create Extended Events Session
CREATE EVENT SESSION alation_query_log ON SERVER ADD EVENT sqlserver.sql_statement_completed( ACTION(package0.collect_sys- tem_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.d atabase_ id,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_user- name,sqlserv er.transaction_id,sqlserver.username, sqlserver.sql_text)) ADD TARGET package0.asynchronous_file_target( SET filename=N’C:\Users\Public\Documents\alation_query_log.xel’, -- CONFIGURE THIS -- OPTIONALLY CONFIGURE THIS, max file size in MB before rolling over -- Note Alation will read one file at a time so this is the size -- file that may be read into memory while it is being fetched. max_file_size=(100), -- in MB max_rollover_files=(100)) -- OPTIONALLY CONFIGURE THIS WITH (MAX_MEMORY=50 MB, -- OPTIONALLY CONFIGURE THIS, the total event buffer size EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, -- If buffer is full events will be dropped instead of blocking -- the server MAX_DISPATCH_LATENCY=30 SECONDS, -- OPTIONALLY CONFIGURE THIS, max time before writing events to -- storage MAX_EVENT_SIZE=0 KB, -- Any events that are too large to fit in the buffer will be -- dropped MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON ) GO -- Query to start the session (change START > STOP to stop it) ALTER EVENT SESSION alation_query_log ON SERVER STATE = START go -- If you need to delete and recreate the session this is the syntax to -- drop it. DROP EVENT SESSION [alation_query_log] ON SERVER GO -- Check if the session is dropping events and see other data about the session SELECT * FROM sys.dm_xe_sessions;
