Azure database for MySQL azuremetrics connections_failed tracking

Question

Hi,
In Azure database for MySQL audit logs, i can find failed connections count using this KQL Query1 and Query2 mentioned below.

But my question here is ..

how to know what are these failed connections, meaning which query is failing or which query is giving me error which is linked to this failed connection ? Can you please provide some KQL queries to track down these connection failures.

Query1:
AzureMetrics
| project TimeGenerated, MetricName, Total, UnitName
| where MetricName =='connections_failed'

Query2:
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMYSQL"
and Category == 'MySqlAuditLogs' and event_class_s == "connection_log" and (event_subclass_s == 'CONNECT' or event_subclass_s == 'DISCONNECT')

Answer 1

hi @AnuragSharma-MSFT , The above query is not correct. Please find the correct query and correct me if any. If there is any failed connection like wrong password , it tries to connect to the db with CONNECT and there wont be any DISCONNECT for that connection so i made this query and worked for me.

let startdt=datetime(6/21/2021, 0:00:00.000 PM);
let enddt=datetime(6/22/2021, 0:00:00.000 PM);
let x=
(AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMYSQL"
and Category == 'MySqlAuditLogs' and event_class_s == "connection_log" and ( event_subclass_s == 'CONNECT')
and TimeGenerated > startdt and TimeGenerated < enddt
| project TimeGenerated, event_class_s, event_subclass_s, user_s , ip_s , connection_id_d, db_s
);
let y=
(AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMYSQL"
and Category == 'MySqlAuditLogs' and event_class_s == "connection_log" and ( event_subclass_s == 'DISCONNECT')
and TimeGenerated > startdt and TimeGenerated < enddt
| project TimeGenerated, event_class_s, event_subclass_s, user_s , ip_s , connection_id_d, db_s
);
x | join kind=leftanti y on connection_id_d | order by connection_id_d

Answer 2

Hi @sandeep pigilam , thanks for your patience.

We can run below query to understand which queries failed and what is the SQL text of them. This also provides the user id and ip address from where it is fired.

AzureDiagnostics  
| where LogicalServerName_s == 'anuragmysqlserver'  
| where Category == 'MySqlAuditLogs'   
| where event_subclass_s  == 'ERROR'  
| order by TimeGenerated desc   

Please let us know if this helps.

----------

If answer helps, you can mark it 'Accept Answer'

Answer 3

hi @AnuragSharma-MSFT , Please find the correct query. If there is any failed connection like the wrong password and Username, it tries to connect to the db with CONNECT and there won't be any DISCONNECT for that connection and No general logs so I made this query, and worked for me.

let startdt=datetime(11/29/2023, 09:59:00.000 AM);
let enddt=datetime(11/29/2023, 10:55:00.000 AM);
let connect= (AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMYSQL"
and Category == 'MySqlAuditLogs' and event_class_s == "connection_log" and ( event_subclass_s == 'CONNECT')
and TimeGenerated > startdt and TimeGenerated < enddt
| project TimeGenerated, event_class_s, event_subclass_s, user_s , ip_s , connection_id_d, db_s);
let disconnect= (AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMYSQL"
and Category == 'MySqlAuditLogs' and event_class_s == "connection_log" and ( event_subclass_s == 'DISCONNECT')
and TimeGenerated > startdt and TimeGenerated < enddt
| project TimeGenerated, event_class_s, event_subclass_s, user_s , ip_s , connection_id_d, db_s);
let connection_log = (connect | join kind=leftanti disconnect on connection_id_d | order by connection_id_d);
let general_log = (AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMYSQL"
and Category == 'MySqlAuditLogs' and event_class_s == "general_log"
and TimeGenerated > startdt and TimeGenerated < enddt
| project TimeGenerated, event_class_s, event_subclass_s, user_s , ip_s , thread_id_d, db_s);
connection_log | join kind=leftanti general_log on $left.connection_id_d == $right.thread_id_d | order by connection_id_d