Audit All Select Statements

Legislation and regulation sometimes dictates that certain activities must be tracked within a database. On occasion, it will be required that queries be audited and tracked to reach compliance with the legislation or regulation. To achieve this compliance, this article will demonstrate how to use Extended Events to audit statements being executed within the database.

Over the course of a few recent articles, I have included little hints here and there alluding to this article. In fact, now would be a good time to review one of these articles in preparation for what I will be sharing today. Take a moment to refresh your memory on this article about finding the right event – here.

Backstory

I have to be a little honest here. Prior to somebody asking how they could possibly achieve a statement audit via extended events, I had not considered it as a tool for the job. I would have relied on Audit (which is Extended Event related), or some home grown set of triggers. In this particular request, Audit was not fulfilling the want and custom triggers was not an option. Another option might have included the purchase of third party software but there are times when budget does not allow for nice expensive shiny software.

So, with a little prodding, I hopped into the metadata and poked around a bit to see what I could come up with to achieve this low-budget audit solution.

Low-Budget Audit

Using the handy scripts I have shown previously (and that I just linked to), I was able to explore the Extended Events metadata and find just what may work properly. The requirements in this case were that it needed to be done in XEvents and that it must capture SELECT statements. To find the events that seemed plausible for this task, I plugged the term “SELECT” into my queries as follows:

DECLARE @TermDescription VARCHAR(64) = 'Select'
, @ColumnDesc VARCHAR(64) = 'statement'
, @ReadFlag VARCHAR(64) = NULL; --readonly' --ALL if all columntypes are desired --data --customizable
 
SELECT oc.object_name AS EventName
, oc.name AS column_name
, oc.type_name
, oc.column_type AS column_type
, oc.column_value AS column_value
, oc.description AS column_description
, ca.map_value AS SearchKeyword
, oc.column_type
FROM sys.dm_xe_object_columns oc
CROSS APPLY ( SELECT mv.map_value
FROM sys.dm_xe_object_columns occ
INNER JOIN sys.dm_xe_map_values mv
ON occ.type_name = mv.name
AND occ.column_value = mv.map_key
AND occ.object_package_guid = mv.object_package_guid
WHERE occ.name = 'KEYWORD'
AND occ.object_name = oc.object_name
) ca
WHERE (oc.object_name = @TermDescription
OR oc.description LIKE '%' + ISNULL(@ColumnDesc,@TermDescription) + '%')
AND oc.column_type = ISNULL(@ReadFlag, oc.column_type)
ORDER BY EventName, column_name;
GO

From this query, there was really only one event that made any sense for my task – “degree_of_parallelism”. There was another event returned in the result set, but it made no sense to me as a possible candidate event for auditing select statements (it was ucs_transmitter_reclassify). From the results, I then took the keyword associated to degree_of_parallelism to see what else might be pertinent. I plugged that keyword “execution” into the following query:

DECLARE @Keyword VARCHAR(64) = 'execution' --'synchronization'
 
SELECT oc.OBJECT_NAME AS EventName
,oc.name AS column_name, oc.type_name
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('',REVERSE(olm.name))-1)) AS DLLName
,olm.file_version
,xp.name AS PackageName
,mv.map_value AS SearchKeyword
,ch.Channel
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_map_values mv
ON oc.type_name = mv.name
AND oc.column_value = mv.map_key
AND oc.name = 'KEYWORD'
INNER JOIN sys.dm_xe_packages xp
ON oc.object_package_guid = xp.guid
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
INNER JOIN (SELECT c.object_name AS EventName,c.object_package_guid AS PkgGuid, v.map_value AS Channel
FROM sys.dm_xe_object_columns c
INNER JOIN sys.dm_xe_map_values v
ON c.type_name = v.name
AND c.column_value = CAST(v.map_key AS NVARCHAR)
WHERE c.name = 'channel') ch
ON ch.EventName = oc.object_name
AND ch.PkgGuid = oc.object_package_guid
WHERE mv.map_value = @Keyword
;
GO

The results of the previous query will be quite a bit more substantial. Some make sense to include in the audit and some do not. I opted to not included any of the events to keep things as clean and simple as possible.

More Requirements

I now have the base events covered that I want to use for my audit. That said, my base requirements are just not extensive enough. When I audit something, I really want to know who did it, when it was done, where it originated and what was it that they did. The degree_of_parallelism event will capture all of the select statements but it does not meet these additional requirements.

In order to meet the additional requirements, I will attach a handful of actions to the degree_of_parallelism event. The addition of the actions will provide all of the data I want and need. Combine the event and actions together, I came up with the following session definition.

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'AuditSelects' )
DROP EVENT SESSION AuditSelects
    ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO

CREATE EVENT SESSION [AuditSelects] ON SERVER
ADD EVENT sqlserver.degree_of_parallelism (
ACTION ( sqlserver.database_id, sqlserver.database_name,
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname,
sqlserver.context_info, sqlserver.client_connection_id )
WHERE  statement_type = ( 1 ) --type 1 is a select statement
AND [sqlserver].[database_name] = N'AdventureWorks2014' 
AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
AND [sqlserver].[client_app_name] NOT LIKE 'Red Gate Software Ltd SQL Prompt%'
)
ADD TARGET package0.event_file (  SET filename = N'C:DatabaseXEAuditSelects.xel'
, max_rollover_files = ( 25 ) )
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

This is a very simple session to pull together. I have added a few things in on my predicate to help limit the scope of the session to the AdventureWorks2014 database and to make sure I am not trapping events related to the code completion tools shown. Notice here also that there is a potential chance to optimize this session. Can you find it? Hint: It may be in the predicate. Second hint: re-read this article about predicates.

Now the trick to what makes this work to audit only the select statements is right there in the predicate. I have instructed the session to ignore any statement_type that is not a value of 1. As it turns out, statement_type of 1 is a select statement. To see how these values map, here is a quick query and the correlated results.

DECLARE @EventName VARCHAR(64) = 'degree_of_parallelism'
,@ReadFlag VARCHAR(64) = 'readonly' --readonly' --ALL if all columntypes are desired
 
SELECT oc.object_name as EventName,oc.name as ColName,mv.name as MapName, map_key, map_value 
FROM sys.dm_xe_map_values mv
Inner Join sys.dm_xe_object_columns oc
on mv.name = oc.type_name
AND mv.object_package_guid = oc.object_package_guid
WHERE oc.object_name = @EventName
AND oc.column_type <> @ReadFlag
ORDER BY MapName, mv.map_key;
GO

Based on these values, should you want to audit a different query type, just change the predicate to use the map_key value that corresponds to the desired statement type.

Testing

Testing this session is rather simple. Here is a quick and dirty script to help test it.

ALTER EVENT SESSION AuditSelects ON SERVER
STATE = START;
USE AdventureWorks2014;
GO
SELECT *
FROM Person.Person;

/* parse the data */IF EXISTS (SELECT OBJECT_ID('tempdb.dbo.#xmlprocess'))
BEGIN
DROP TABLE #xmlprocess
END
SELECT CAST ([t2].[event_data] AS XML) AS event_data, t2.file_offset,t2.file_name, cte1.event_session_id--, '' AS event_predicate
INTO #xmlprocess
FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),'.xel','*.xel') AS targetvalue, ses.event_session_id
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
--INNER JOIN sys.server_event_session_events sese
--ON ses.event_session_id = sese.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = 'AuditSelects'
) cte1
OUTER APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,NULL, NULL, NULL) t2
;

SELECT x.event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
, x.event_data.value('(event/@package)[1]', 'varchar(50)') AS package_name
, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
x.event_data.value('(event/@timestamp)[1]',
'datetime2')) AS [timestamp]
, event_data.value('(event/data[@name="dop"]/value)[1]',
'bigint') AS dop
, event_data.value('(event/data[@name="statement_type"]/value)[1]',
'varchar(max)') AS statement_type
, event_data.value('(event/data[@name="workspace_memory_grant_kb"]/value)[1]',
'bigint') AS workspace_memory_grant_kb
, event_data.value('(event/action[@name="client_app_name"]/value)[1]',
'varchar(max)') AS client_app_name
, event_data.value('(event/action[@name="client_connection_id"]/value)[1]',
'uniqueidentifier') AS client_connection_id
, event_data.value('(event/action[@name="client_hostname"]/value)[1]',
'varchar(max)') AS client_hostname
, event_data.value('(event/action[@name="context_info"]/value)[1]',
'varbinary(max)') AS context_info
, event_data.value('(event/action[@name="database_id"]/value)[1]',
'int') AS database_id
, event_data.value('(event/action[@name="database_name"]/value)[1]',
'varchar(max)') AS database_name
, event_data.value('(event/action[@name="nt_username"]/value)[1]',
'varchar(max)') AS nt_username
, event_data.value('(event/action[@name="session_id"]/value)[1]',
'int') AS session_id
, event_data.value('(event/action[@name="session_nt_username"]/value)[1]',
'varchar(max)') AS session_nt_username
, event_data.value('(event/action[@name="sql_text"]/value)[1]',
'varchar(max)') AS sql_text
, event_data.value('(event/action[@name="username"]/value)[1]',
'varchar(max)') AS username
FROM #xmlprocess x
LEFT OUTER JOIN sys.server_event_session_events sese
ON x.event_data.value('(event/@name)[1]', 'varchar(50)') = sese.name
AND x.event_session_id = sese.event_session_id
ORDER BY timestamp
, event_data.value('(event/action[@name="event_sequence"]/value)[1]',
'varchar(max)');

Conclusion

Building low cost solutions is a common requirement for the data professional. When given the opportunity, try things out to see what you can build to provide the solution. In this case, I have demonstrated how Extended Events, however unlikely a candidate, can provide a working solution to help audit any select statements occurring within your database.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.