CODEWITHSUNDEEP
sql-serverazuret-sqlazure-sql-databasedmv
BIDeveloper
BIDeveloper

Reputation: 847

How to find all the queries which are executed on Azure SQL database with username using T-SQL

I have requirement to find all the queries which are executed in last 24 hours on an Azure SQL database with user name. I have got the below query which is giving me all the queries run in last 24 hours but I am unable to get the user information on that, like who has run that query.

I don't have access to the Azure Portal so have to get it from T-SQL.

SELECT 
    Qry.last_execution_time,
    Txt.query_text_id, Txt.query_sql_text, Pl.plan_id,   
    Qry.*, txt.*, pl.*
FROM 
    sys.query_store_plan AS Pl
INNER JOIN 
    sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id
INNER JOIN 
    sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id
WHERE
    Qry.last_execution_time >= DATEADD(Day,-1,Getdate())    
ORDER BY
    1 DESC;

I tried below query as well but it is giving me the user name but this showing current running queries not the history

SELECT 
    s.login_name,
    r.command,
    r.status,
    r.start_time,
    r.total_elapsed_time,
    r.cpu_time,
    r.logical_reads,
    r.writes,
    r.reads,
    r.text_size,
    r.statement_start_offset,
    r.statement_end_offset,
    q.text AS [query_text],
    
    r.database_id,
    r.user_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type
FROM 
    sys.dm_exec_requests r
INNER JOIN 
    sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) AS q
WHERE 
    r.start_time >= DATEADD(DAY, -1, GETDATE()) -- Queries executed in the last 24 hours
ORDER BY 
    r.start_time DESC;

Upvotes: 0

Views: 256

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15698

The best way to have that information is to enable SQL Auditing, then you can query Audit logs as shown below:

Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup"
 -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT"  
 -StorageAccountName "storageAccount" 
-AuditActionGroup "BATCH_COMPLETED_GROUP" 
 -AuditAction "UPDATE ON database::[AdventureWorksLT] BY [public]"  
 -RetentionInDays 60

A sample audit record for the BATCH_COMPLETED_GROUP will show the following information:

enter image description here

Upvotes: 1

Related Questions