Store log analytics query in SQL database

Question

Hello community,

We are monitoring every time a user fails to log into our SQL, and we are using log analytics to do it… our objective is to drive quick action whenever there is an authentication error, and to do that we want to store the logs into our SQL database, in a table.

These logs should be stored event wise, whenever they take place. Is this feasible? The query we are currently using is:

 

AzureDiagnostics
| where TimeGenerated >= ago(1d) 
| where action_name_s == "DATABASE AUTHENTICATION FAILED" | where Category == 'SQLSecurityAuditEvents' and true == true and true == true and true == true and true == true | project bin(event_time_t, 1ms), SubscriptionId, ResourceGroup, server_instance_name_s, database_name_s, is_server_level_audit_s, statement_s, succeeded_s, duration_milliseconds_d , response_rows_d , affected_rows_d , action_name_s , class_type_description_s ,client_ip_s , server_principal_name_s, database_principal_name_s, application_name_s, sequence_group_id_g ,additional_information_s ,data_sensitivity_information_s | sort by event_time_t desc

 

Alerts and Power BI are out of scope in this case.

Thanks,

Answer 1

One way to do that is to export the output of a Kusto query executed on the Azure Log Analytics to an Azure SQL Database by using the .export management command. This command allows you to run a query and have its results sent to a table in an SQL database, such as an Azure SQL Database. Below an example:

.export to sql LogAnalytics "Server=tcp:myserver.database.windows.net,1433;Database=mydatabase;User ID=mylogin@myserver;Password=mypassword;Encrypt=true;TrustServerCertificate=false;Connection Timeout=30;" <| AzureActivity | where OperationName == "Create or Update Virtual Machine" | project ResourceGroup, Resource, Caller, TimeGenerated

You can schedule the execution of a query like the above on Azure Automation.