Creating alerts for custom errors with auditing and Log Analytics
Errors are an inherent part of any application. As a database professional managing an Azure SQL Managed Instance, you may be interested in understanding when specific errors occur and how to leverage user-generated errors to respond swiftly when particular scenarios arise. On this post we will see how we can setup alerts for scenarios like query blocking and long open transactions. This will be an extend of what is described on How to setup alerts for deadlocks using Log Analytics | Microsoft Community Hub Step 1 - Setup Log Analytics and auditing Follow steps 1, 2, 3 and 4 described on How to setup alerts for deadlocks using Log Analytics | Microsoft Community Hub Step 2 - Create a table to save the details of the blocking chain / long open transaction Getting an alert itself is not useful if you don`t have a way of getting details for later analysis. Create the table on a database of your choice. If you are interested on blocking: CREATE TABLE [dbo].[blocking_report] ( [DateTime] [datetime] NULL ,[HeadBlocker] [varchar](1) NOT NULL ,[SessionID] [smallint] NOT NULL ,[Login] [nvarchar](128) NOT NULL ,[Database] [nvarchar](128) NULL ,[BlockedBy] [smallint] NULL ,[OpenTransactions] [int] NULL ,[Status] [nvarchar](30) NOT NULL ,[WaitType] [nvarchar](60) NULL ,[WaitTime_ms] [bigint] NULL ,[WaitResource] [nvarchar](256) NULL ,[WaitResourceDesc] [nvarchar](3072) NULL ,[Command] [nvarchar](32) NULL ,[Application] [nvarchar](128) NULL ,[TotalCPU_ms] [int] NOT NULL ,[TotalPhysicalIO_MB] [bigint] NULL ,[MemoryUse_KB] [int] NULL ,[LoginTime] [datetime] NOT NULL ,[LastRequestStartTime] [datetime] NOT NULL ,[HostName] [nvarchar](128) NULL ,[QueryHash] [binary](8) NULL ,[BlockerQuery_or_MostRecentQuery] [nvarchar](max) NULL ) If you are interested on open transactions: CREATE TABLE [dbo].[opentransactions]( [CapturedTime] [datetime] NOT NULL, [tran_elapsed_time_seconds] [int] NULL, [transaction_begin_time] [datetime] NOT NULL, [session_id] [int] NOT NULL, [database_name] [nvarchar](128) NULL, [open_transaction_count] [int] NOT NULL, [host_name] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [login_name] [nvarchar](128) NULL, [status] [nvarchar](30) NULL, [text] [nvarchar](max) NULL ) Step 3 - Create a SQL Agent job Schedule the two SQL Agent jobs using the queries below. You might be interested on schedule that runs every X amount of seconds/minutes. Describing very briefly what each query does: if a query is being blocked for more than 60 seconds, save the blocking chain on a table and raise an error if a transaction is open for more than 120 seconds, save the query details on a table and raise an error Make the necessary adjustments according to your goals. Query for blocking: IF ( SELECT count(*) FROM sys.dm_exec_requests WHERE wait_type LIKE 'LCK%' AND wait_time > 60000 ) > 0 -- checks for queries waiting to obtain a lock for more than 30 seconds BEGIN INSERT INTO database_name.dbo.Blocking_report -- make sure that you change the database_name value SELECT ( SELECT getdate() ) ,[HeadBlocker] = CASE WHEN r2.session_id IS NOT NULL AND ( r.blocking_session_id = 0 OR r.session_id IS NULL ) THEN '1' ELSE '' END ,[SessionID] = s.session_id ,[Login] = s.login_name ,[Database] = db_name(p.dbid) ,[BlockedBy] = w.blocking_session_id ,[OpenTransactions] = r.open_transaction_count ,[Status] = s.STATUS ,[WaitType] = w.wait_type ,[WaitTime_ms] = w.wait_duration_ms ,[WaitResource] = r.wait_resource ,[WaitResourceDesc] = w.resource_description ,[Command] = r.command ,[Application] = s.program_name ,[TotalCPU_ms] = s.cpu_time ,[TotalPhysicalIO_MB] = (s.reads + s.writes) * 8 / 1024 ,[MemoryUse_KB] = s.memory_usage * 8192 / 1024 ,[LoginTime] = s.login_time ,[LastRequestStartTime] = s.last_request_start_time ,[HostName] = s.host_name ,[QueryHash] = r.query_hash ,[BlockerQuery_or_MostRecentQuery] = txt.TEXT FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id) LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id) LEFT OUTER JOIN sys.dm_os_tasks t ON ( r.session_id = t.session_id AND r.request_id = t.request_id ) LEFT OUTER JOIN ( SELECT * ,ROW_NUMBER() OVER ( PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC ) AS row_num FROM sys.dm_os_waiting_tasks ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1 LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id) LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid) OUTER APPLY sys.dm_exec_sql_text(ISNULL(r.[sql_handle], c.most_recent_sql_handle)) AS txt WHERE s.is_user_process = 1 AND ( r2.session_id IS NOT NULL AND ( r.blocking_session_id = 0 OR r.session_id IS NULL ) ) OR blocked > 0 ORDER BY [HeadBlocker] DESC ,s.session_id; THROW 50000 ,'There are queries being blocked for more than 60 seconds' ,1; END Query for open transactions: DECLARE @count INT = ( SELECT count(*) FROM sys.dm_tran_active_transactions at INNER JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id LEFT OUTER JOIN sys.dm_exec_sessions sess ON st.session_id = sess.session_id LEFT OUTER JOIN sys.dm_exec_connections conn ON conn.session_id = sess.session_id OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS txt WHERE DATEDIFF(SECOND, transaction_begin_time, GETDATE()) > 120 -- 120 seconds ) IF @count > 0 BEGIN INSERT INTO database_name.dbo.opentransactions -- change database_name to where the table was created SELECT GETDATE() AS CapturedTime ,DATEDIFF(SECOND, transaction_begin_time, GETDATE()) AS tran_elapsed_time_seconds ,at.transaction_begin_time ,st.session_id ,DB_NAME(sess.database_id) AS database_name ,st.open_transaction_count ,sess.host_name ,sess.program_name ,sess.login_name ,sess.STATUS ,txt.TEXT FROM sys.dm_tran_active_transactions at INNER JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id LEFT OUTER JOIN sys.dm_exec_sessions sess ON st.session_id = sess.session_id LEFT OUTER JOIN sys.dm_exec_connections conn ON conn.session_id = sess.session_id OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS txt WHERE DATEDIFF(SECOND, transaction_begin_time, GETDATE()) > 120 --120 seconds ORDER BY tran_elapsed_time_seconds DESC; THROW 50000 ,'There are open transactions for more than 120 seconds' ,1; END Step 4 - Create the alert based on a Log Analytics query Just as described on steps 6 and 7 of How to setup alerts for deadlocks using Log Analytics | Microsoft Community Hub, you can create the two separate alerts using the queries below. For blocking: AzureDiagnostics | where TimeGenerated > ago(15m) //last 15 minutes | where LogicalServerName_s == "server_name" //server name | where Category == "SQLSecurityAuditEvents" | where additional_information_s contains "There are queries being blocked for more" | project TimeGenerated, LogicalServerName_s, database_name_s, additional_information_s For open transactions: AzureDiagnostics | where TimeGenerated > ago(15m) //last 15 minutes | where LogicalServerName_s == "server_name" //server name | where Category == "SQLSecurityAuditEvents" | where additional_information_s contains "There are open transactions for more than" | project TimeGenerated, LogicalServerName_s, database_name_s, additional_information_sConfigure Auditing for Azure SQL Database on a Specific table
The Azure SQL Auditing feature is used to track database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs. For those who are interested on how to configure Auditing on Azure SQL Database either on server-level or database-level please visit this Configure Auditing for Azure SQL Database series - Part 1 and Configure Auditing for Azure SQL Database series - Part 2 For those who are looking into enable Auditing for Azure SQL Database on a specific table as this option is not available on Azure Portal as of now. we don`t have any estimate date when this option is available on Azure Portal, but they can use the following PowerShell script that will help you enable Auditing on a specific table in Azure SQL Database. In script I had given example table as (MyTable) targeting a table named MyTable in the DBO schema and want to Audit SELECT and INSERT actions. # Define variables $resourceGroup = "YourResourceGroup" $serverName = "your-sql-server-name" $databaseName = "your-database-name" $storageAccount = "yourstorageaccount" $tableName = "MyTable" $schemaName = "dbo" # Login to Azure Connect-AzAccount # Enable auditing at the database level Set-AzSqlDatabaseAuditing ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -DatabaseName $databaseName ` -StorageAccountName $storageAccount ` -AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP" ` -AuditAction "SELECT ON $schemaName.$tableName BY public", "INSERT ON $schemaName.$tableName BY public" ` -State Enabled REST API - Manage Auditing Using APIs - Azure SQL Database & Azure Synapse Analytics | Microsoft Learn To enable auditing on a specific table in Azure SQL Database using the REST API, you can use the Create or Update Database Extended Auditing Policy endpoint. This allows you to define fine-grained auditing rules, including actions on specific tables. URL PUT https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/extendedAuditingSettings/default?api-version=2021-11-01-preview Reference Request Body: { "properties": { "state": "Enabled", "storageEndpoint": "https://.blob.core.windows.net/", "storageAccountAccessKey": "", "retentionDays": 90, "auditActionsAndGroups": [ "SELECT ON dbo.MyTable BY public", "INSERT ON dbo.MyTable BY public" ], "isStorageSecondaryKeyInUse": false } } Parameters: auditActionsAndGroups: This is where you specify the exact actions and the table. You can include SELECT, INSERT, UPDATE, DELETE, etc. storageEndpoint: The Azure Blob Storage endpoint where audit logs will be stored. retentionDays: Number of days to retain logs. state: Must be "Enabled" to activate auditing. AZ Cli – https://learn.microsoft.com/en-us/cli/azure/sql/db/audit-policy?view=azure-cli-latest#az-sql-db-audit-policy-update az sql db audit-policy update -g ResourceGroupName -s Servername -n DatabaseName --state Enabled --bsts Enabled --storage-key "" --storage-endpoint https://StorageAccount.blob.core.windows.net/ --actions FAILED_DATABASE_AUTHENTICATION_GROUP 'UPDATE ON dbo.MyTable BY public' sample output { "auditActionsAndGroups": [ "FAILED_DATABASE_AUTHENTICATION_GROUP", "UPDATE ON dbo.MyTable BY public" ], "id": "/subscriptions/xxxxx-xxxxx-xxxxxx-xxxxx-xxxxxx/resourceGroups/ResourceGroupName/providers/Microsoft.Sql/servers/ServerName/databases/DatabaseName/auditingSettings/Default", "isAzureMonitorTargetEnabled": true, "isManagedIdentityInUse": false, "isStorageSecondaryKeyInUse": false, "kind": null, "name": "Default", "queueDelayMs": null, "resourceGroup": "ResourceGroupName", "retentionDays": 10, "state": "Enabled", "storageAccountAccessKey": null, "storageAccountSubscriptionId": "xxxx-xxxxx-xxxx-xxxxx-xxxxxxx", "storageEndpoint": https://StorageAccount.blob.core.windows.net/, "type": "Microsoft.Sql/servers/databases/auditingSettings" } For more information Configure Auditing for Azure SQL Database series - Part 1 Configure Auditing for Azure SQL Database series - Part 2 Set-AzSqlDatabaseAudit Manage Auditing Using APIs - Azure SQL Database & Azure Synapse Analytics | Microsoft Learn az sql db audit-policy updateMicrosoft Fixes Copilot Audit Records
After a report to the MSRC about some missing file data from Copilot audit records, Microsoft fixed the problem and audit records now contain details about the SharePoint Online files reviewed by Copilot to construct answers to user prompts. Having solid audit and compliance data is a good thing, unless you’re a lawyer charged with defending an eDiscovery action who might be asked to produce the files. https://office365itpros.com/2025/08/22/copilot-audit-records-fixed/
Purview and auditing file modifications
I have full M365 E5 license and use Purview auditing a lot for investigations. I noticed is reports file modified which is create but some of my files would get modified constantly. I'm curious if it can log and provide a report on what exactly was modified. For example: If text was added or deleted, can it tell me what was added or deleted i.e. the actual text and the action (Add\Delete) If an image was pasted into a word document, can it tell me that? If possible, down to a copy of the image that was inserted? If it can't do this level of detail anyone have suggestions of a product that can?
Auditing Resource Bookings
I am once again dealing with overlapped bookings on rooms set to not allow overlapped bookings. I've verified this in PowerShell. I've limited who can make these to 2 users. I've trained these users. They are using Outlook on the web - just in case the app doesn't update. STILL, I am getting bookings on top of each other. One person books something - then another person books something on top of it. I need to audit this. I have enabled resource auditing. I have tried 1) Purview auditing the booking users - everything they do related to Exchange. This returns hundreds of entries for the time periods specified, and I genuinely do not have time to parse all of this. 2) using purview to search only for entries on the room resource. These apparently have no data, ever. 3) Using purview to search for activities related to booking.. Too bad the activity dropdown does not work, and 80% of the entries are repeats. Maybe I can find operation names from the documentation? (no) 4) Maybe Copilot can help me find operation names? (no, he just hallucinates) 6) Abandoning Purview, I open Exchange and head to the Collect Logs entries. I run both Resource Bookings and Calendar logs. Because that is, essentially, exactly what I want. Unfortunately neither of these are even remotely useful to a human - except to confirm this shouldn't be possible. I spend an hour parsing through the endless nigh-identically named fields, to find data relevant to me - What I can find isn't accurate, and isn't useful. 7) I open Outlook, and try to look at the actual mail interactions on that shared mailbox. Unfortunately, there is nothing for these days. No sent, inbox, deleted items referencing the bookings at all. 8) I open powershell, i try commands microsoft gives. These are deprecated. This should really not be this hard. In fact, it should be REALLY EASY to see when bookings came in and out. I am really close to looking for a 3rd party solution just so i don't have to waste any more time on this
Interface Views in Microsoft 365 Admin Center
1. Simplified View Purpose: Designed for small businesses or organizations with limited IT resources. Features: Streamlined dashboard with essential tasks like user management, license assignment, and password resets. Minimal configuration options to reduce complexity. Quick access to support and basic service health info. Use Case: Ideal for admins who need to perform routine tasks without diving into advanced settings. 2. Dashboard View (Advanced View) Purpose: Tailored for medium to large enterprises with complex IT environments. Features: Full access to all admin centers (Exchange, SharePoint, Teams, etc.). Advanced analytics, reporting, and configuration tools. Role-based access control and security management. Customizable widgets and navigation for personalized workflows. Use Case: Suitable for IT teams managing multiple services, users, and compliance requirements. Customization Needs Are there specific tasks we perform frequently that can be automated in the dashboard?
Obtain Deleted Stats (SharePoint) by Retention Policy
I've scoured: https://learn.microsoft.com/en-us/purview/retention-cmdlets and the Unified Audit Log (https://www.meetingroom365.com/blog/search-unifiedauditlog-powershell/, https://theitbros.com/query-microsoft-365-audit-logs-using-powershell/#penci-Search_the_SharePoint_Online_Audit_Log) to see if I can come up with a method to obtain some statistics regarding how many files and space (storage) has been freed up with the use of retention policies being enabled. I'm drawing a blank. In an ideal world, I'd like know how many files have been deleted by the system (the system enforcing a 5 Year from last modified Date and Delete Policy) for the last year or 6 month intervals. If possible the corresponding volume of storage space recovered from these deletions. Any ideas?Use Audit Data to Improve Finding Inactive Copilot Users
A previous article explained how Microsoft 365 usage report data can highlight inactive Copilot users. If we add audit data to the mix, the analysis becomes much richer because we can see exactly what use people make of different Copilot apps, like Word, Chat, Outlook, and so on. Better data means better decisions! https://practical365.com/inactive-copilot-users/
Report all active users in tenant and their installed integrated apps
Our security team has requested that we block the install of any Copilot apps until our AI policy is in place. Before we do this, I'd like to know what apps from Microsoft 365 admin center > Settings > Integrated apps > Available apps are currently installed by our users. I don't see any way that the UI offers this capability, so I believe it will be PowerShell. I did already run the following script, but it returns only 2 apps, which are apps we have deployed to our users. It's possible our 2600 users haven't installed anything else, but not probable. Install-Module O365CentralizedAddInDeployment Import-Module -Name O365CentralizedAddInDeployment Connect-OrganizationAddInService Get-OrganizationAddIn If the above isn't possible, it would also be useful to find a script that would give me a list of users who have a given app (from 365 Integrated apps > Available apps) installed, such as CopilotForce or Microsoft Copilot Studio.
