Forum Discussion
Log Analytics query to create a alert for IP's blocked by firewall in Azure SQl Database.
Hi All,
I need a Help in writing a Query to create a alert to get IP's Blocked by Firewall in Azure SQL Database.
Ah! I think you need https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure
"You can use SQL Database Auditing to audit server-level and database-level firewall changes."It also might be worth looking in the AzureActivity log - sorry I don't have any DBs configured with Auditing or DB level firewalls. If you get an example record we can help you parse for the IP address if needed.
- CliveWatson
Microsoft
I think we need to know more.
Are you trying to see what Firewall deny messages have occurred and to write them into SQL?
What Firewall are you using, as they store the logs in different tables in Log Analytics?
Azure Firewall - example
AzureDiagnostics | where ResourceType == "AZUREFIREWALLS" | where Category == "AzureFirewallApplicationRule" | where msg_s has "Deny" // only see deny | parse msg_s with * "from" ipa ":" * // get just IP address | project ipa, msg_s
Cisco - for example
CommonSecurityLog | where DeviceVendor == "Cisco"
Thanks you CliveWatson .
Let me explain. i have azure SQL database (PaaS) in our environment and i have enabled the diagnostics setting and configured to send the logs to Log analytics.
I have whitelisted few IP ranges in SQL firewall to access the database.
I need to write a query in Log Analytics to trigger a alert if any external user is trying to access the database and got blocked by the SQL firewall.
I need to fetch those details by using the Log Analytics query.
I had created a sample query to collect 3 consecutive failed connection while access the database.
Query:
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" | where ResourceType == "SERVERS/DATABASES" | where ResourceGroup == "AZRG-OC-TDS-STORAGE" | distinct LogicalServerName_s, event_time_t, action_name_s, client_ip_s, server_principal_name_s, application_name_s, host_name_s, TimeGenerated | where action_name_s == "DATABASE AUTHENTICATION FAILED" | where TimeGenerated > ago(5m) | summarize logoncount = count() by client_ip_s | where logoncount > 3Need help to create a query to fetch the IP details which got blocked by SQL firewall in order to trigger a alert.
Thanks in Advance :)
- CliveWatson
Ah! I think you need https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure
"You can use SQL Database Auditing to audit server-level and database-level firewall changes."It also might be worth looking in the AzureActivity log - sorry I don't have any DBs configured with Auditing or DB level firewalls. If you get an example record we can help you parse for the IP address if needed.
