Log Analytics Query for SQL on Azure VM

Question

I have SQL installed on Azure VM , which is connected to Log Analytics workspace, for logging/monitoring.

I am aware of the MS-SQL Query that i need to write to achieve this.

But my question is how to write this MS-SQL (T-SQL Query) to query the SQL database on Azure VM, using Log analytics query or Azure monitor ?

Answer 1

@MS Techie Apologies for delay in getting back to this thread.

Firstly to query something from Log Analytics workspace , you need to send data to the workspace. Given the current state of the product , features like Azure Diagnostic extensions or the Log Analytics agent will not be collecting the actual SQL tables and sending the data to Log Analytics workspace. You can enable the diagnostic settings for SQL database as stand alone and get below information.

However if you want to do anything custom, you might have to write some automation to read SQL tables and make them as custom logs so that agent can pick it up or send it to Log analytics workspace as customlogs using the data collector api.

Please be aware that you will be only able to query Log Analytics workspace using Kusto queries not T-SQL Query.

Note: If your SQL tables are huge or you are running production workload , the custom approaches might not be recommended.

Hope the above information helps. Please let us know if you need any further information. Thank you