Querying Azure SQL Extended Event not returning any data

Scott Klein 5

I created an extended event in Azure SQL database to capture query data. The data is being written to Azure blog storage, and there is definitely data in the files:

User's image

I verified that data exists by downloading one of the files and opening it in SSMS.

I created the appropriate SAS token and database credentials.

However, when querying any of the xel files, it returns no data, and I am not sure why. I have tried both sys.fn_xe_file_target_read_file and sys.fn_get_audit_file_v2. Both return nothing as shown below.

User's image

Any ideas? What did I miss? Thank you very much.

Sai Raghunadh M 1,605 Reputation points Microsoft Vendor

2024-12-23T20:45:20.4366667+00:00

Hi @Scott Klein,

Thanks for the question and using MS Q&A platform.

In Azure SQL Database, you can call the sys.fn_xe_file_target_read_file() function after you create a database-scoped credential containing a SAS token with the Read and List permissions on the container with the xel blobs:

If you specify a blob name prefix instead of the full blob name in the first argument of sys.fn_xe_file_target_read_file(), the function will return data from all blobs in the container that match the prefix. This lets you retrieve data from all rollover files of a given event session without using the * wildcard, which isn't supported by Azure Storage.

Azure SQL Database does not support wildcards in the path when querying Blob Storage directly.

If the wildcard (*) is used in the blob name (like '*.xel'), the function will not be able to match the blobs in Blob Storage, and therefore it will not retrieve any data, causing the function to return null.

To fix this, you need to specify a prefix that matches the start of the blob names. The sys.fn_xe_file_target_read_file() function requires a prefix to match the blobs, not a wildcard.

Please go through this documentation that might help you:

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/targets-for-extended-events-in-sql-server?view=sql-server-ver16#sysfn_xe_file_target_read_file-function

Please go through this similar thread:

https://learn.microsoft.com/en-us/answers/questions/1726320/cannot-create-and-start-extended-events-session-in

I hope this information helps, please do let us know if you have any Queries.
Sai Raghunadh M 1,605 Reputation points Microsoft Vendor

2024-12-24T14:31:10.88+00:00

Hi @Scott Klein,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Scott Klein 5 Reputation points

2024-12-25T06:05:16.8933333+00:00

HI Sai, thank you for your response. I am not sure if you caught it in my original post, but I did try using the sys.fn_xe_file_target_read_file() function. I did create a SAS token, and it has READ, List, and Write permissions.

I did get it to work by recreating the SAS token, and deleting and re-creating the database credential. So, to be honest, I am not sure what I did wrong the first time...I must have created the SAS token with the incorrect permissions.

Again, thank you for your response and for the information.

Share via

Querying Azure SQL Extended Event not returning any data

Your answer