question

sqlworldwide avatar image
0 Votes"
sqlworldwide asked DimitriFurman-MSFT answered

Azure SQL Database identify blocking source

Attention: @DimitriFurman-MSFT

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>

azure-sql-databaseazure-monitor
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @sqlworldwide,

Thanks for using Microsoft Q&A !!

I believe login and host name is being redacted intentionally for security purpose but I am checking internally on the same and get back to you.

Thanks
Saurabh

0 Votes 0 ·

1 Answer

DimitriFurman-MSFT avatar image
1 Vote"
DimitriFurman-MSFT answered

Hi @sqlworldwide - 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.