Azure SQL Database identify blocking source

Question

Attention: @Dimitri Furman

For an Azure SQL Database, I have enabled 'Enable Azure SQL Auditing' with destination 'Log Analytics Workspace'. I also have diagnostic turned on at a database level with destination to the same 'Log Analytics Workspace'.

Now when I am parsing the blocked_process_filtered_s column using parse_xml function some of the values are marked as 'filtered'.
For example:
loginname="filtered"
hostname="filtered"

Why is that? How can I get the host and login name?

<?xml version="1.0" encoding="UTF-8"?>  
<blocked-process-report monitorLoop="523631">  
   <blocked-process>  
      <process id="process1e5f17ef468" taskpriority="0" logused="0" waitresource="OBJECT: 5:978102525:0 " waittime="26518" ownerId="22464123" transactionname="SELECT" lasttranstarted="2021-07-06T07:59:07.707" XDES="0x1e5dc37dbe8" lockMode="X" schedulerid="2" kpid="61140" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2021-07-06T07:59:07.703" lastbatchcompleted="2021-07-06T07:59:07.607" lastattention="1900-01-01T00:00:00.607" clientapp="Microsoft SQL Server Management Studio - Query" hostname="filtered" hostpid="5036" loginname="filtered" isolationlevel="read committed (2)" xactid="22464123" currentdb="5" currentdbname="taiobtestads" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">  
         <executionStack>  
            <frame queryhash="0x5cbb5f2c0ae5b440" queryplanhash="0x16fbbac78e112e8f" line="1" stmtend="68" sqlhandle="0x020000005dc25d32ef099b94b452d2f6eab02f0daa2e14810000000000000000000000000000000000000000" />  
         </executionStack>  
         <inputbuf>filtered</inputbuf>  
      </process>  
   </blocked-process>  
   <blocking-process>  
      <process status="sleeping" spid="85" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-07-06T07:58:55.373" lastbatchcompleted="2021-07-06T07:58:55.373" lastattention="1900-01-01T00:00:00.373" clientapp="Microsoft SQL Server Management Studio - Query" hostname="filtered" hostpid="5036" loginname="filtered" isolationlevel="read committed (2)" xactid="22463875" currentdb="5" currentdbname="taiobtestads" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">  
         <executionStack />  
         <inputbuf>filtered</inputbuf>  
      </process>  
   </blocking-process>  
</blocked-process-report>  

Answer 1

Hi @Taiob Ali - Saurabh is correct. The data being collected in this case is stored on Microsoft-owned systems before it gets to you Log Analytics workspace, and as such it needs to comply with strict data privacy and security requirements. The two fields you mentioned are considered PII data, therefore the data is intentionally removed.

To troubleshoot blocking chains, you can start your own XE session and collect the blocked_process_report event, rather than the blocked_process_report_filtered event that is collected when you enable built-in auditing or monitoring features.