Log Analytics query for event data

Question

Log Analytics query for event data

Priya Jha 901

Hi All,

I have enabled Azure Log Analytics on my Azure SQL DB. I have a requirement wherein i need to display the queries and the executed by person details in an hourly slot in an excel as below:

1 answer

Your answer

Answer 1

SSingh-MSFT 16,461 Moderator

Hi @Priya Jha ,

Thanks for posting question in Microsoft Q&A platform.

As I understand from the question, you want an excel with the columns mentioned in the screenshot.

You can achieve this using System Dynamic Management Views.

For example in order to fetch Query and execution time:

You may combine the DMVs as per the requirement and generate output. Reference: sys-dm-exec-sql-text-transact-sql

There is an option to export data in Azure portal in Query editor:

Hope this helps. Let us know if further queries.

Priya Jha 901 Reputation points

2023-01-05T07:18:36.787+00:00

Hi @SSingh-MSFT ,

Thank you for your input. Can you please provide the minimum time that these logs would be retained within the database that can be consumed via DMV, my understanding is DMV queries would only provide logs for past 30 days. Is there any way to go beyond that?
SSingh-MSFT 16,461 Reputation points Moderator

2023-01-06T10:30:13.82+00:00

Hi @Priya Jha ,

If I understand correctly, you want logs of older months too.
For older logs, you may try to store the result of the executed queries by using Azure Data Factory pipeline and schedule it according to the requirement.

Let me know if I understood otherwise.

Thanks
Priya Jha 901 Reputation points

2023-01-13T12:53:29.4666667+00:00

HI @SSingh-MSFT

Thank you for your inputs.

I couldn't find a way to get a common output with the query executed and the user who executed that query.

Is there any query either via Metadata or log analytics via which i can get the user name and the query details in a single output? (If possible also the host name and the IP from which the query was executed)
SSingh-MSFT 16,461 Reputation points Moderator

2023-01-18T08:49:44.3333333+00:00
Hi @Priya Jha ,

Azure SQL Auditing should provide you the application name, login name and client IP address that executed the query. Please read this article to enable Auditing and look for the event type BATCH_COMPLETED.

Set-AzureRmSqlDatabaseAuditing ` -State Enabled ` -ResourceGroupName "rgYourResourceGroup" ` -ServerName "yourservername" ` -StorageAccountName "StorageAccountForAuditing" ` -DatabaseName "YourDatabaseName"

Share via

Log Analytics query for event data

1 answer

Your answer