Who created this Extended Events session?

Question

I have been asked to find out who created a certain Extended Events session on an Azure SQL Database. However, looking through the DMVs, there are plenty of attributes, but nothing to indicate when it was created, or by whom.

Is there a way to determine this?

Thank you.

Answer 1

In Azure this can be done but you have to have SQL Auditing enabled either on the database or server level. Then you have to execute the following using the sys.fn_get_audit_file function:

SET NOCOUNT ON;
SELECT
   server_principal_id
 , database_principal_id
 , target_server_principal_id
 , target_database_principal_id
 , session_server_principal_name
 , server_principal_name
 , server_principal_sid
 , database_principal_name
 , target_server_principal_name
 , target_server_principal_sid
 , target_database_principal_name
 , server_instance_name
 , database_name
 , schema_name
 , object_name
 , statement
 , additional_information
FROM    sys.fn_get_audit_file(
                             'https://blob_storage_name.blob.core.windows.net/sqldbauditlogs/SERVER_NAME/DATABASE_NAME/SqlDbAuditing_ServerAudit/2018-11-27' -- INSERT date here
                            , DEFAULT
                            , DEFAULT
                         )
WHERE statement LIKE '%CREATE EVENT SESSION%';

This should give you back the information you need. Keep in mind that SQL Auditing can generate A LOT of data, so you may need to query the audit files per day or even per hour (you can read how date patterns are used with sys.fn_get_audit_file here).

If you find the amount of data too big to query you can always download the audit files (.xel files, SQL Auditing is implemented via Extended Events) and write a custom tool to do that (Microsoft is offering a library to parse Extended Event files via LINQ. See details here).

Answer 2

Adding a comment here in case it helps anyone who finds this older article. I solved this by adding a SQL Audit with the Audit Action Type set to SERVER_OBJECT_CHANGE_GROUP, like this:

CREATE SERVER AUDIT SPECIFICATION [SessionTracking]
FOR SERVER AUDIT [EE_monitor]
ADD (SERVER_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO