Forum Discussion

Syed_Aman's avatar
Syed_Aman
Copper Contributor
Apr 18, 2019

Azure Log Analytics for Azure MySQL database (PaaS Service)

Hello Everyone, I need some help in the below issue: I have enabled the diagnostic setting for Azure MySQL database (PaaS Service) in our environment and configured the alert to send the logs to Azur...
azure monitor
Query Language
CliveWatson's avatar
CliveWatson
Former Employee
May 10, 2019

Syed_Aman 

 

Did you get this working?   If not, check this first 

 

 

// check to see which resource providers you have
AzureDiagnostics
| where TimeGenerated > ago(7d)
| summarize by ResourceProvider

If you add a count() to the above, you can see how many records you have by each ResourceProvider

 

Then, your query should work (adjust the days/hours to suit)

 

 

AzureDiagnostics
| where TimeGenerated > ago(1d)
| where ResourceProvider == "Microsoft.DBforMySQL"

 

 

 

 

Syed_Aman's avatar
Syed_Aman
Copper Contributor
May 13, 2019

ThanksCliveWatson.

 

I am able to get the logs for MySql database in Azure Log Analytics workspace now and i have written a query to get the failed connections details. below is the query

 

AzureDiagnostics
| where SubscriptionId != ""
| where TimeGenerated > ago(10m)
| where ResourceProvider == "MICROSOFT.DBFORMYSQL"
| where Category == "MySqlAuditLogs" and event_class_s == "connection_log"
| where event_subclass_s == "DISCONNECT"
| summarize logoncount= count(), TenantId=arg_max(TenantId, SubscriptionId, ip_s, TimeGenerated, LogicalServerName_s, db_s, ResourceGroup)
| where logoncount > 3
| project TenantId, SubscriptionId, ip_s, logoncount, TimeGenerated, LogicalServerName_s, db_s, ResourceGroup

 

 

but the issue which i am facing in the query is it is just giving me over all DISCONNECT counts and i need help in modifying the filter to get the failed connection count.

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    May 13, 2019

    Hi Syed_Aman 

    I don't have any DBforMYSQL records 

     

    AzureDiagnostics
| where SubscriptionId != ""
| where TimeGenerated > ago(10m)
| where ResourceProvider == "MICROSOFT.DBFORMYSQL"
| where Category == "MySqlAuditLogs" and event_class_s == "connection_log"
| summarize count() by  event_subclass_s

     

    The above should enable you to see what other states, like 'failed' have occurred - sorry I don't have the data so this is a guess!

     

     

Resources