last sync: 2021-May-17 14:22:45 UTC

Azure Policy definition

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

Name Deploy - Configure diagnostic settings for SQL Databases to Log Analytics workspace
Azure Portal
Id b79fa14e-238a-4c2d-b376-442ce508fc84
Version 1.0.1
details on versioning
Category SQL
Microsoft docs
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.
Mode Indexed
Type BuiltIn
Preview FALSE
Deprecated FALSE
Effect Default: DeployIfNotExists
Allowed: (DeployIfNotExists, Disabled)
Used RBAC Role
Role Name Role Id
Monitoring Contributor 749f88d5-cbae-40b8-bcfc-e573ddc772fa
Log Analytics Contributor 92aaf0da-9dab-42b6-94a3-d43ce8d16293
History
Date/Time (UTC ymd) (i) Change type Change detail
2021-02-10 14:43:58 change Patch (1.0.0 > 1.0.1) *changes on text case sensitivity are not tracked
2021-02-03 15:09:01 add b79fa14e-238a-4c2d-b376-442ce508fc84
Used in Initiatives none
JSON Changes

JSON
{
  "properties": {
    "displayName": "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": {
      "version": "1.0.1",
      "category": "SQL"
    },
    "parameters": {
      "effect": {
        "type": "String",
        "metadata": {
          "displayName": "Effect",
          "description": "Enable or disable the execution of the policy"
        },
        "allowedValues": [
          "DeployIfNotExists",
          "Disabled"
        ],
        "defaultValue": "DeployIfNotExists"
      },
      "diagnosticsSettingNameToUse": {
        "type": "String",
        "metadata": {
          "displayName": "Setting name",
          "description": "Name of the diagnostic settings."
        },
        "defaultValue": "SQLDatabaseDiagnosticsLogsToWorkspace"
      },
      "logAnalytics": {
        "type": "String",
        "metadata": {
          "displayName": "Log Analytics workspace",
          "description": "Select the Log Analytics workspace from dropdown list",
          "strongType": "omsWorkspace",
          "assignPermissions": true
        }
      },
      "QueryStoreRuntimeStatisticsEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "QueryStoreRuntimeStatistics - Enabled",
          "description": "Whether to stream QueryStoreRuntimeStatistics logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "QueryStoreWaitStatisticsEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "QueryStoreWaitStatistics - Enabled",
          "description": "Whether to stream QueryStoreWaitStatistics logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "ErrorsEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "Errors - Enabled",
          "description": "Whether to stream Errors logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "DatabaseWaitStatisticsEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "DatabaseWaitStatistics - Enabled",
          "description": "Whether to stream DatabaseWaitStatistics logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "BlocksEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "Blocks - Enabled",
          "description": "Whether to stream Blocks logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "SQLInsightsEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "SQLInsights - Enabled",
          "description": "Whether to stream SQLInsights logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "SQLSecurityAuditEventsEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "SQLSecurityAuditEvents - Enabled",
          "description": "Whether to stream SQLSecurityAuditEvents logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "TimeoutsEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "Timeouts - Enabled",
          "description": "Whether to stream Timeouts logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "AutomaticTuningEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "AutomaticTuning - Enabled",
          "description": "Whether to stream AutomaticTuning logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "DeadlocksEnabled": {
        "type": "String",
        "metadata": {
          "displayName": "Deadlocks - Enabled",
          "description": "Whether to stream Deadlocks logs to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "Basic": {
        "type": "String",
        "metadata": {
          "displayName": "Basic (metric) - Enabled",
          "description": "Whether to stream Basic metrics to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "InstanceAndAppAdvanced": {
        "type": "String",
        "metadata": {
          "displayName": "InstanceAndAppAdvanced (metric) - Enabled",
          "description": "Whether to stream InstanceAndAppAdvanced metrics to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      },
      "WorkloadManagement": {
        "type": "String",
        "metadata": {
          "displayName": "WorkloadManagement (metric) - Enabled",
          "description": "Whether to stream WorkloadManagement metrics to the Log Analytics workspace - True or False"
        },
        "allowedValues": [
          "True",
          "False"
        ],
        "defaultValue": "True"
      }
    },
    "policyRule": {
      "if": {
        "field": "type",
        "equals": "Microsoft.Sql/servers/databases"
      },
      "then": {
      "effect": "[parameters('effect')]",
        "details": {
          "type": "Microsoft.Insights/diagnosticSettings",
          "roleDefinitionIds": [
            "/providers/microsoft.authorization/roleDefinitions/749f88d5-cbae-40b8-bcfc-e573ddc772fa",
            "/providers/microsoft.authorization/roleDefinitions/92aaf0da-9dab-42b6-94a3-d43ce8d16293"
          ],
          "existenceCondition": {
            "allOf": [
              {
                "field": "Microsoft.Insights/diagnosticSettings/logs.enabled",
                "equals": "True"
              },
              {
                "field": "Microsoft.Insights/diagnosticSettings/metrics.enabled",
                "equals": "True"
              },
              {
                "field": "Microsoft.Insights/diagnosticSettings/workspaceId",
              "matchInsensitively": "[parameters('logAnalytics')]"
              }
            ]
          },
          "deployment": {
            "properties": {
              "mode": "incremental",
              "template": {
                "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                "contentVersion": "1.0.0.0",
                "parameters": {
                  "diagnosticsSettingNameToUse": {
                    "type": "string"
                  },
                  "resourceName": {
                    "type": "string"
                  },
                  "logAnalytics": {
                    "type": "string"
                  },
                  "location": {
                    "type": "string"
                  },
                  "Basic": {
                    "type": "string"
                  },
                  "InstanceAndAppAdvanced": {
                    "type": "string"
                  },
                  "WorkloadManagement": {
                    "type": "string"
                  },
                  "QueryStoreRuntimeStatisticsEnabled": {
                    "type": "string"
                  },
                  "QueryStoreWaitStatisticsEnabled": {
                    "type": "string"
                  },
                  "ErrorsEnabled": {
                    "type": "string"
                  },
                  "DatabaseWaitStatisticsEnabled": {
                    "type": "string"
                  },
                  "BlocksEnabled": {
                    "type": "string"
                  },
                  "SQLInsightsEnabled": {
                    "type": "string"
                  },
                  "SQLSecurityAuditEventsEnabled": {
                    "type": "string"
                  },
                  "TimeoutsEnabled": {
                    "type": "string"
                  },
                  "AutomaticTuningEnabled": {
                    "type": "string"
                  },
                  "DeadlocksEnabled": {
                    "type": "string"
                  }
                },
                "variables": {
                  
                },
                "resources": [
                  {
                    "type": "Microsoft.Sql/servers/databases/providers/diagnosticSettings",
                    "apiVersion": "2017-05-01-preview",
                  "name": "[concat(parameters('resourceName'), '/', 'Microsoft.Insights/', parameters('diagnosticsSettingNameToUse'))]",
                  "location": "[parameters('location')]",
                    "dependsOn": [
                      
                    ],
                    "properties": {
                    "workspaceId": "[parameters('logAnalytics')]",
                      "metrics": [
                        {
                          "category": "Basic",
                        "enabled": "[parameters('Basic')]"
                        },
                        {
                          "category": "InstanceAndAppAdvanced",
                        "enabled": "[parameters('InstanceAndAppAdvanced')]"
                        },
                        {
                          "category": "WorkloadManagement",
                        "enabled": "[parameters('WorkloadManagement')]"
                        }
                      ],
                      "logs": [
                        {
                          "category": "SQLInsights",
                        "enabled": "[parameters('SQLInsightsEnabled')]"
                        },
                        {
                          "category": "AutomaticTuning",
                        "enabled": "[parameters('AutomaticTuningEnabled')]"
                        },
                        {
                          "category": "QueryStoreRuntimeStatistics",
                        "enabled": "[parameters('QueryStoreRuntimeStatisticsEnabled')]"
                        },
                        {
                          "category": "QueryStoreWaitStatistics",
                        "enabled": "[parameters('QueryStoreWaitStatisticsEnabled')]"
                        },
                        {
                          "category": "Errors",
                        "enabled": "[parameters('ErrorsEnabled')]"
                        },
                        {
                          "category": "DatabaseWaitStatistics",
                        "enabled": "[parameters('DatabaseWaitStatisticsEnabled')]"
                        },
                        {
                          "category": "Timeouts",
                        "enabled": "[parameters('TimeoutsEnabled')]"
                        },
                        {
                          "category": "Blocks",
                        "enabled": "[parameters('BlocksEnabled')]"
                        },
                        {
                          "category": "Deadlocks",
                        "enabled": "[parameters('DeadlocksEnabled')]"
                        }
                      ]
                    }
                  }
                ],
                "outputs": {
                  
                }
              },
              "parameters": {
                "Basic": {
                "value": "[parameters('Basic')]"
                },
                "InstanceAndAppAdvanced": {
                "value": "[parameters('InstanceAndAppAdvanced')]"
                },
                "diagnosticsSettingNameToUse": {
                "value": "[parameters('diagnosticsSettingNameToUse')]"
                },
                "WorkloadManagement": {
                "value": "[parameters('WorkloadManagement')]"
                },
                "logAnalytics": {
                "value": "[parameters('logAnalytics')]"
                },
                "location": {
                "value": "[field('location')]"
                },
                "resourceName": {
                "value": "[field('fullName')]"
                },
                "QueryStoreRuntimeStatisticsEnabled": {
                "value": "[parameters('QueryStoreRuntimeStatisticsEnabled')]"
                },
                "QueryStoreWaitStatisticsEnabled": {
                "value": "[parameters('QueryStoreWaitStatisticsEnabled')]"
                },
                "ErrorsEnabled": {
                "value": "[parameters('ErrorsEnabled')]"
                },
                "DatabaseWaitStatisticsEnabled": {
                "value": "[parameters('DatabaseWaitStatisticsEnabled')]"
                },
                "BlocksEnabled": {
                "value": "[parameters('BlocksEnabled')]"
                },
                "SQLInsightsEnabled": {
                "value": "[parameters('SQLInsightsEnabled')]"
                },
                "SQLSecurityAuditEventsEnabled": {
                "value": "[parameters('SQLSecurityAuditEventsEnabled')]"
                },
                "TimeoutsEnabled": {
                "value": "[parameters('TimeoutsEnabled')]"
                },
                "AutomaticTuningEnabled": {
                "value": "[parameters('AutomaticTuningEnabled')]"
                },
                "DeadlocksEnabled": {
                "value": "[parameters('DeadlocksEnabled')]"
                }
              }
            }
          }
        }
      }
    }
  },
  "id": "/providers/Microsoft.Authorization/policyDefinitions/b79fa14e-238a-4c2d-b376-442ce508fc84",
  "type": "Microsoft.Authorization/policyDefinitions",
  "name": "b79fa14e-238a-4c2d-b376-442ce508fc84"
}