AzPolicyAdvertizer

last sync: 2025-May-27 20:12:32 UTC

Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace

Azure BuiltIn Policy definition

Source

Azure Portal

Display name

Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace

b79fa14e-238a-4c2d-b376-442ce508fc84

Version

4.0.0
Details on versioning

Versioning

Versions supported for Versioning: 1
4.0.0
Built-in Versioning [Preview]

Category

SQL
Microsoft Learn

Description

Deploys the diagnostic settings for SQL Databases to stream resource logs to a Log Analytics workspace when any SQL Database which is missing this diagnostic settings is created or updated.

Cloud environments

AzureCloud = true
AzureUSGovernment = true
AzureChinaCloud = unknown

Available in AzUSGov

The Policy is available in AzureUSGovernment cloud. Version: '4.*.*'

Mode

Indexed

Type

BuiltIn

Preview

False

Deprecated

False

Effect

Default
DeployIfNotExists
Allowed
DeployIfNotExists, Disabled

RBAC role(s)

Role Name	Role Id
Monitoring Contributor	749f88d5-cbae-40b8-bcfc-e573ddc772fa
Log Analytics Contributor	92aaf0da-9dab-42b6-94a3-d43ce8d16293

Rule aliases

THEN-ExistenceCondition (7)

Alias	Namespace	ResourceType	Path	PathIsDefault	Modifiable
Microsoft.Insights/diagnosticSettings/logs[*]	microsoft.insights	diagnosticSettings	properties.logs[*]	True	False
Microsoft.Insights/diagnosticSettings/logs[*].category	microsoft.insights	diagnosticSettings	properties.logs[*].category	True	False
Microsoft.Insights/diagnosticSettings/logs[*].enabled	microsoft.insights	diagnosticSettings	properties.logs[*].enabled	True	False
Microsoft.Insights/diagnosticSettings/metrics[*]	microsoft.insights	diagnosticSettings	properties.metrics[*]	True	False
Microsoft.Insights/diagnosticSettings/metrics[*].category	microsoft.insights	diagnosticSettings	properties.metrics[*].category	True	False
Microsoft.Insights/diagnosticSettings/metrics[*].enabled	microsoft.insights	diagnosticSettings	properties.metrics[*].enabled	True	False
Microsoft.Insights/diagnosticSettings/workspaceId	microsoft.insights	diagnosticSettings	properties.workspaceId	True	False

Rule resource types

IF (1)

Microsoft.Sql/servers/databases

Compliance

The following 1 compliance controls are associated with this Policy definition 'Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace' (b79fa14e-238a-4c2d-b376-442ce508fc84)

Rows: 1-1 / 1
Columns:
Select all:
Control Domain
Control
Name
MetadataId
Category
Title
Owner
Requirements
Description
Info
Policy#
Close
Columns▼Records:
Use the filters above each column to filter and limit table data. Advanced searches can be performed by using the following operators:
<, <=, >, >=, =, *, !, {, }, ||,&&, **[empty]**, **[nonempty]**, **rgx:**
Learn more
TableFilter v0.7.3
https://www.tablefilter.com/
©2015-2025 Max Guglielmi
Close
?
Page of 1
Control Domain	Control	Name	MetadataId	Category	Title	Owner	Requirements	Description	Info	Policy#

RMiT_v1.0	10.66	RMiT_v1.0_10.66	RMiT 10.66	Security of Digital Services	Security of Digital Services - 10.66	Shared	n/a	A financial institution must implement robust technology security controls in providing digital services which assure the following: (a) confidentiality and integrity of customer and counterparty information and transactions; (b) reliability of services delivered via channels and devices with minimum disruption to services; (c) proper authentication of users or devices and authorisation of transactions; (d) sufficient audit trail and monitoring of anomalous transactions; (e) ability to identify and revert to the recovery point prior to incident or service disruption; and (f) strong physical control and logical control measures	link	30

Initiatives usage

Rows: 1-3 / 3
Records:
Use the filters above each column to filter and limit table data. Advanced searches can be performed by using the following operators:
<, <=, >, >=, =, *, !, {, }, ||,&&, **[empty]**, **[nonempty]**, **rgx:**
Learn more
TableFilter v0.7.3
https://www.tablefilter.com/
©2015-2025 Max Guglielmi
Close
?
Page of 1
Initiative DisplayName	Initiative Id	Initiative Category	State	Type	polSet in AzUSGov

[Deprecated]: Deploy Diagnostic Settings to Azure Services	Deploy-Diagnostics-LogAnalytics	Monitoring	Deprecated	ALZ
[Preview]: Control the use of diagnostic settings for specific resources in a Virtual Enclave	0a9ea1cb-7925-47fc-b0fe-8bb0a8190423	VirtualEnclaves	Preview	BuiltIn	true
RMIT Malaysia	97a6d4f1-3bed-4cf4-ac5b-0e444c0408d6	Regulatory Compliance	GA	BuiltIn	unknown

History

Date/Time (UTC ymd) (i)	Change type	Change detail
2022-03-11 18:16:48	change	Major (3.0.0 > 4.0.0)
2022-02-04 18:25:37	change	Major (2.0.0 > 3.0.0)
2021-10-22 15:42:38	change	Major (1.0.1 > 2.0.0)
2021-02-10 14:43:58	change	Patch (1.0.0 > 1.0.1)
2021-02-03 15:09:01	add	b79fa14e-238a-4c2d-b376-442ce508fc84

JSON compare

compare mode: version left: version right:

3.0.0 → 4.0.0 RENAMED Viewed

@@ -3,9 +3,9 @@
     "policyType": "BuiltIn",
     "mode": "Indexed",
     "description": "Deploys the diagnostic settings for SQL Databases to stream resource logs to a Log Analytics workspace when any SQL Database which is missing this diagnostic settings is created or updated.",
     "metadata": {
-        "version": "3.0.0",
         "category": "SQL"
     },
     "parameters": {
         "effect": {
@@ -332,13 +332,25 @@
                                                     "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                                                     "equals": "[parameters('DeadlocksEnabled')]"
                                                 }
                                             ]
                                         }
                                     ]
                                 }
                             },
-                            "equals": 9
                         },
                         {
                             "count": {
                                 "field": "Microsoft.Insights/diagnosticSettings/metrics[*]",
@@ -505,8 +517,12 @@
                                             },
                                             {
                                                 "category": "Deadlocks",
                                                 "enabled": "[parameters('DeadlocksEnabled')]"
                                             }
                                         ]
                                     }
                                 }

     "policyType": "BuiltIn",
     "mode": "Indexed",
     "description": "Deploys the diagnostic settings for SQL Databases to stream resource logs to a Log Analytics workspace when any SQL Database which is missing this diagnostic settings is created or updated.",
     "metadata": {
+        "version": "4.0.0",
         "category": "SQL"
     },
     "parameters": {
         "effect": {
                                                     "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                                                     "equals": "[parameters('DeadlocksEnabled')]"
                                                 }
                                             ]
+                                        },
+                                        {
+                                            "allOf": [
+                                                {
+                                                    "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
+                                                    "equals": "SQLSecurityAuditEvents"
+                                                },
+                                                {
+                                                    "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
+                                                    "equals": "[parameters('SQLSecurityAuditEventsEnabled')]"
+                                                }
+                                            ]
                                         }
                                     ]
                                 }
                             },
+                            "equals": 10
                         },
                         {
                             "count": {
                                 "field": "Microsoft.Insights/diagnosticSettings/metrics[*]",
                                             },
                                             {
                                                 "category": "Deadlocks",
                                                 "enabled": "[parameters('DeadlocksEnabled')]"
+                                            },
+                                            {
+                                                "category": "SQLSecurityAuditEvents",
+                                                "enabled": "[parameters('SQLSecurityAuditEventsEnabled')]"
                                             }
                                         ]
                                     }
                                 }

JSON

api-version=2021-06-01
EPAC

{7 itemsdisplayName: "Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace",
policyType: "BuiltIn",
mode: "Indexed",
description: "Deploys the diagnostic settings for SQL Databases to stream resource logs to a Log Analytics workspace when any SQL Database which is missing this diagnostic settings is created or updated.",
metadata: {2 itemsversion: "4.0.0",
category: "SQL"
},
parameters: {16 itemseffect: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "Effect",
description: "Enable or disable the execution of the policy"
},
allowedValues: [2 items"DeployIfNotExists",
"Disabled"
],
defaultValue: "DeployIfNotExists"
},
diagnosticsSettingNameToUse: {3 itemstype: "String",
metadata: {2 itemsdisplayName: "Setting name",
description: "Name of the diagnostic settings."
},
defaultValue: "SQLDatabaseDiagnosticsLogsToWorkspace"
},
logAnalytics: {2 itemstype: "String",
metadata: {4 itemsdisplayName: "Log Analytics workspace",
description: "Select the Log Analytics workspace from dropdown list",
strongType: "omsWorkspace",
assignPermissions: true
}
},
QueryStoreRuntimeStatisticsEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "QueryStoreRuntimeStatistics - Enabled",
description: "Whether to stream QueryStoreRuntimeStatistics logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
QueryStoreWaitStatisticsEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "QueryStoreWaitStatistics - Enabled",
description: "Whether to stream QueryStoreWaitStatistics logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
ErrorsEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "Errors - Enabled",
description: "Whether to stream Errors logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
DatabaseWaitStatisticsEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "DatabaseWaitStatistics - Enabled",
description: "Whether to stream DatabaseWaitStatistics logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
BlocksEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "Blocks - Enabled",
description: "Whether to stream Blocks logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
SQLInsightsEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "SQLInsights - Enabled",
description: "Whether to stream SQLInsights logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
SQLSecurityAuditEventsEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "SQLSecurityAuditEvents - Enabled",
description: "Whether to stream SQLSecurityAuditEvents logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
TimeoutsEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "Timeouts - Enabled",
description: "Whether to stream Timeouts logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
AutomaticTuningEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "AutomaticTuning - Enabled",
description: "Whether to stream AutomaticTuning logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
DeadlocksEnabled: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "Deadlocks - Enabled",
description: "Whether to stream Deadlocks logs to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
Basic: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "Basic (metric) - Enabled",
description: "Whether to stream Basic metrics to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
InstanceAndAppAdvanced: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "InstanceAndAppAdvanced (metric) - Enabled",
description: "Whether to stream InstanceAndAppAdvanced metrics to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
},
WorkloadManagement: {4 itemstype: "String",
metadata: {2 itemsdisplayName: "WorkloadManagement (metric) - Enabled",
description: "Whether to stream WorkloadManagement metrics to the Log Analytics workspace - True or False"
},
allowedValues: [2 items"True",
"False"
],
defaultValue: "True"
}
},
policyRule: {2 itemsif: {1 itemallOf: [2 items{2 itemsfield: "type",
equals: "Microsoft.Sql/servers/databases"
},
{2 itemsfield: "name",
notEquals: "master"
}
]
},
then: {2 itemseffect: "[parameters('effect')]",
details: {4 itemstype: "Microsoft.Insights/diagnosticSettings",
roleDefinitionIds: [2 items"/providers/microsoft.authorization/roleDefinitions/749f88d5-cbae-40b8-bcfc-e573ddc772fa" Monitoring Contributor ,
"/providers/microsoft.authorization/roleDefinitions/92aaf0da-9dab-42b6-94a3-d43ce8d16293" Log Analytics Contributor 
],
existenceCondition: {1 itemallOf: [3 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/workspaceId",
equals: "[parameters('logAnalytics')]"
},
{2 itemscount: {2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*]",
where: {1 itemanyOf: [10 items{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "SQLInsights"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('SQLInsightsEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "AutomaticTuning"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('AutomaticTuningEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "QueryStoreRuntimeStatistics"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('QueryStoreRuntimeStatisticsEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "QueryStoreWaitStatistics"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('QueryStoreWaitStatisticsEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "Errors"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('ErrorsEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "DatabaseWaitStatistics"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('DatabaseWaitStatisticsEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "Timeouts"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('TimeoutsEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "Blocks"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('BlocksEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "Deadlocks"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('DeadlocksEnabled')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].category",
equals: "SQLSecurityAuditEvents"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
equals: "[parameters('SQLSecurityAuditEventsEnabled')]"
}
]
}
]
}
},
equals: 10
},
{2 itemscount: {2 itemsfield: "Microsoft.Insights/diagnosticSettings/metrics[*]",
where: {1 itemanyOf: [3 items{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/metrics[*].category",
equals: "Basic"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/metrics[*].enabled",
equals: "[parameters('Basic')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/metrics[*].category",
equals: "InstanceAndAppAdvanced"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/metrics[*].enabled",
equals: "[parameters('InstanceAndAppAdvanced')]"
}
]
},
{1 itemallOf: [2 items{2 itemsfield: "Microsoft.Insights/diagnosticSettings/metrics[*].category",
equals: "WorkloadManagement"
},
{2 itemsfield: "Microsoft.Insights/diagnosticSettings/metrics[*].enabled",
equals: "[parameters('WorkloadManagement')]"
}
]
}
]
}
},
equals: 3
}
]
},
deployment: {1 itemproperties: {3 itemsmode: "incremental",
template: {6 items$schema: "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
contentVersion: "1.0.0.0",
parameters: {17 itemsdiagnosticsSettingNameToUse: {1 itemtype: "string"
},
resourceName: {1 itemtype: "string"
},
logAnalytics: {1 itemtype: "string"
},
location: {1 itemtype: "string"
},
Basic: {1 itemtype: "string"
},
InstanceAndAppAdvanced: {1 itemtype: "string"
},
WorkloadManagement: {1 itemtype: "string"
},
QueryStoreRuntimeStatisticsEnabled: {1 itemtype: "string"
},
QueryStoreWaitStatisticsEnabled: {1 itemtype: "string"
},
ErrorsEnabled: {1 itemtype: "string"
},
DatabaseWaitStatisticsEnabled: {1 itemtype: "string"
},
BlocksEnabled: {1 itemtype: "string"
},
SQLInsightsEnabled: {1 itemtype: "string"
},
SQLSecurityAuditEventsEnabled: {1 itemtype: "string"
},
TimeoutsEnabled: {1 itemtype: "string"
},
AutomaticTuningEnabled: {1 itemtype: "string"
},
DeadlocksEnabled: {1 itemtype: "string"
}
},
variables: {},
resources: [1 item{6 itemstype: "Microsoft.Sql/servers/databases/providers/diagnosticSettings",
apiVersion: "2017-05-01-preview",
name: 🔍"[
    concat(
        parameters('resourceName'),
       '/',
        'Microsoft.Insights/',
         parameters('diagnosticsSettingNameToUse')
    )
]",
location: "[parameters('location')]",
dependsOn: [],
properties: {3 itemsworkspaceId: "[parameters('logAnalytics')]",
metrics: [3 items{2 itemscategory: "Basic",
enabled: "[parameters('Basic')]"
},
{2 itemscategory: "InstanceAndAppAdvanced",
enabled: "[parameters('InstanceAndAppAdvanced')]"
},
{2 itemscategory: "WorkloadManagement",
enabled: "[parameters('WorkloadManagement')]"
}
],
logs: [10 items{2 itemscategory: "SQLInsights",
enabled: "[parameters('SQLInsightsEnabled')]"
},
{2 itemscategory: "AutomaticTuning",
enabled: "[parameters('AutomaticTuningEnabled')]"
},
{2 itemscategory: "QueryStoreRuntimeStatistics",
enabled: "[parameters('QueryStoreRuntimeStatisticsEnabled')]"
},
{2 itemscategory: "QueryStoreWaitStatistics",
enabled: "[parameters('QueryStoreWaitStatisticsEnabled')]"
},
{2 itemscategory: "Errors",
enabled: "[parameters('ErrorsEnabled')]"
},
{2 itemscategory: "DatabaseWaitStatistics",
enabled: "[parameters('DatabaseWaitStatisticsEnabled')]"
},
{2 itemscategory: "Timeouts",
enabled: "[parameters('TimeoutsEnabled')]"
},
{2 itemscategory: "Blocks",
enabled: "[parameters('BlocksEnabled')]"
},
{2 itemscategory: "Deadlocks",
enabled: "[parameters('DeadlocksEnabled')]"
},
{2 itemscategory: "SQLSecurityAuditEvents",
enabled: "[parameters('SQLSecurityAuditEventsEnabled')]"
}
]
}
}
],
outputs: {}
},
parameters: {17 itemsBasic: {1 itemvalue: "[parameters('Basic')]"
},
InstanceAndAppAdvanced: {1 itemvalue: "[parameters('InstanceAndAppAdvanced')]"
},
diagnosticsSettingNameToUse: {1 itemvalue: "[parameters('diagnosticsSettingNameToUse')]"
},
WorkloadManagement: {1 itemvalue: "[parameters('WorkloadManagement')]"
},
logAnalytics: {1 itemvalue: "[parameters('logAnalytics')]"
},
location: {1 itemvalue: "[field('location')]"
},
resourceName: {1 itemvalue: "[field('fullName')]"
},
QueryStoreRuntimeStatisticsEnabled: {1 itemvalue: "[parameters('QueryStoreRuntimeStatisticsEnabled')]"
},
QueryStoreWaitStatisticsEnabled: {1 itemvalue: "[parameters('QueryStoreWaitStatisticsEnabled')]"
},
ErrorsEnabled: {1 itemvalue: "[parameters('ErrorsEnabled')]"
},
DatabaseWaitStatisticsEnabled: {1 itemvalue: "[parameters('DatabaseWaitStatisticsEnabled')]"
},
BlocksEnabled: {1 itemvalue: "[parameters('BlocksEnabled')]"
},
SQLInsightsEnabled: {1 itemvalue: "[parameters('SQLInsightsEnabled')]"
},
SQLSecurityAuditEventsEnabled: {1 itemvalue: "[parameters('SQLSecurityAuditEventsEnabled')]"
},
TimeoutsEnabled: {1 itemvalue: "[parameters('TimeoutsEnabled')]"
},
AutomaticTuningEnabled: {1 itemvalue: "[parameters('AutomaticTuningEnabled')]"
},
DeadlocksEnabled: {1 itemvalue: "[parameters('DeadlocksEnabled')]"
}
}
}
}
}
}
}
}

Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace

Azure BuiltIn Policy definition

TableFilter v0.7.3

TableFilter v0.7.3