Azure Log Analytics for Azure MySQL database (PaaS Service)

%3CLINGO-SUB%20id%3D%22lingo-sub-472615%22%20slang%3D%22en-US%22%3EAzure%20Log%20Analytics%20for%20Azure%20MySQL%20database%20(PaaS%20Service)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472615%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%20I%20need%20some%20help%20in%20the%20below%20issue%3A%20I%20have%20enabled%20the%20diagnostic%20setting%20for%20Azure%20MySQL%20database%20(PaaS%20Service)%20in%20our%20environment%20and%20configured%20the%20alert%20to%20send%20the%20logs%20to%20Azure%20Log%20Analytics.%20as%20per%20Microsoft%20SLA%20for%20data%20ingestion%20to%20Log%20Analytics%20(30%20minutes%20to%206%20hours).%20i%20tried%20running%20the%20Log%20analytics%20query%20to%20fetch%20the%20MySQL%20database%20details%20but%20i%20didn't%20get%20any%20results%20for%20that.%20Query%3A%20AzureDiagnostics%20%7C%20where%20TimeGenerated%20%26gt%3B%20ago(7h)%20%7C%20where%20ResourceProvider%20%3D%3D%20%22Microsoft.DBforMySQL%22%20and%20ResourceType%20%3D%3D%20%22Microsoft.DBforMySQL%2Fservers%22%20Note%3A%20MySQL%20database%20is%20in%20East%20US%20and%20Azure%20Log%20Analytics%20is%20in%20West%20US%20but%20the%20query%20is%20working%20for%20MS%20SQL%20database%20with%20little%20modification.%20I%20want%20answer%20whether%20the%20Azure%20Log%20Analytics%20is%20generally%20available%20for%20MySQL%20database%20%3F%20if%20yes%2C%20the%20can%20you%20help%20me%20with%20a%20sample%20query%20to%20get%20the%20output%20in%20log%20analytics.%20Thanks%20in%20Advance%20!!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-472615%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECustom%20Logs%20and%20Custom%20Fields%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-557977%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Log%20Analytics%20for%20Azure%20MySQL%20database%20(PaaS%20Service)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-557977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319881%22%20target%3D%22_blank%22%3E%40Syed_Aman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20get%20this%20working%3F%26nbsp%3B%20%26nbsp%3BIf%20not%2C%20check%20this%20first%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%2F%2F%20check%20to%20see%20which%20resource%20providers%20you%20have%0AAzureDiagnostics%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(7d)%0A%7C%20summarize%20by%20ResourceProvider%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FPRE%3E%0A%3CP%3EIf%20you%20add%20a%20count()%20to%20the%20above%2C%20you%20can%20see%20how%20many%20records%20you%20have%20by%20each%20ResourceProvider%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20688px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112925i47CF4F3BDBE5D758%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-05-10%20125850.png%22%20title%3D%22Annotation%202019-05-10%20125850.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%2C%20your%20query%20should%20work%20(adjust%20the%20days%2Fhours%20to%20suit)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EAzureDiagnostics%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%0A%7C%20where%20ResourceProvider%20%3D%3D%20%22Microsoft.DBforMySQL%22%20%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564241%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Log%20Analytics%20for%20Azure%20MySQL%20database%20(PaaS%20Service)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564241%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20able%20to%20get%20the%20logs%20for%20MySql%20database%20in%20Azure%20Log%20Analytics%20workspace%20now%20and%20i%20have%20written%20a%20query%20to%20get%20the%20failed%20connections%20details.%20below%20is%20the%20query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EAzureDiagnostics%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20SubscriptionId%20!%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3Em)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20ResourceProvider%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22MICROSOFT.DBFORMYSQL%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Category%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22MySqlAuditLogs%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20event_class_s%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22connection_log%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20event_subclass_s%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22DISCONNECT%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20logoncount%3D%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%2C%20TenantId%3Darg_max(TenantId%2C%20SubscriptionId%2C%20ip_s%2C%20TimeGenerated%2C%20LogicalServerName_s%2C%20db_s%2C%20ResourceGroup)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20logoncount%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20TenantId%2C%20SubscriptionId%2C%20ip_s%2C%20logoncount%2C%20TimeGenerated%2C%20LogicalServerName_s%2C%20db_s%2C%20ResourceGroup%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20the%20issue%20which%20i%20am%20facing%20in%20the%20query%20is%20it%20is%20just%20giving%20me%20over%20all%20DISCONNECT%20counts%20and%20i%20need%20help%20in%20modifying%20the%20filter%20to%20get%20the%20failed%20connection%20count.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564390%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Log%20Analytics%20for%20Azure%20MySQL%20database%20(PaaS%20Service)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564390%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319881%22%20target%3D%22_blank%22%3E%40Syed_Aman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20have%20any%20DBforMYSQL%20records%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EAzureDiagnostics%0A%7C%20where%20SubscriptionId%20!%3D%20%22%22%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(10m)%0A%7C%20where%20ResourceProvider%20%3D%3D%20%22MICROSOFT.DBFORMYSQL%22%0A%7C%20where%20Category%20%3D%3D%20%22MySqlAuditLogs%22%20and%20event_class_s%20%3D%3D%20%22connection_log%22%0A%7C%20summarize%20count()%20by%20%20event_subclass_s%20%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20above%20should%20enable%20you%20to%20see%20what%20other%20states%2C%20like%20'failed'%20have%20occurred%20-%20sorry%20I%20don't%20have%20the%20data%20so%20this%20is%20a%20guess!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673655%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Log%20Analytics%20for%20Azure%20MySQL%20database%20(PaaS%20Service)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673655%22%20slang%3D%22en-US%22%3EThis%20feature%20is%20in%20Private%20Preview%20at%20the%20moment...%20see%20more%20information%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fissues%2F26763%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fissues%2F26763%3C%2FA%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 Azure Log Analytics. as per Microsoft SLA for data ingestion to Log Analytics (30 minutes to 6 hours). i tried running the Log analytics query to fetch the MySQL database details but i didn't get any results for that. Query: AzureDiagnostics | where TimeGenerated > ago(7h) | where ResourceProvider == "Microsoft.DBforMySQL" and ResourceType == "Microsoft.DBforMySQL/servers" Note: MySQL database is in East US and Azure Log Analytics is in West US but the query is working for MS SQL database with little modification. I want answer whether the Azure Log Analytics is generally available for MySQL database ? if yes, the can you help me with a sample query to get the output in log analytics. Thanks in Advance !!!!!

4 Replies

@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

Annotation 2019-05-10 125850.png

 

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

 

 

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

 

 

 

 

Thanks@Clive Watson.

 

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.

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!

 

 

This feature is in Private Preview at the moment... see more information here: https://github.com/MicrosoftDocs/azure-docs/issues/26763