last sync: 2021-Oct-25 16:02:14 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 2.0.0
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-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) *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
{
  "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": "2.0.0",
    "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/workspaceId",
              "equals": "[parameters('logAnalytics')]"
            },
            {
              "count": {
                "field": "Microsoft.Insights/diagnosticSettings/logs[*]",
                "where": {
                  "anyOf": [
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "SQLInsights"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('SQLInsightsEnabled')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "AutomaticTuning"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('AutomaticTuningEnabled')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "QueryStoreRuntimeStatistics"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('QueryStoreRuntimeStatisticsEnabled')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "QueryStoreWaitStatistics"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('QueryStoreWaitStatisticsEnabled')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "Errors"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('ErrorsEnabled')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "DatabaseWaitStatistics"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('DatabaseWaitStatisticsEnabled')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "Timeouts"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('TimeoutsEnabled')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "Blocks"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('BlocksEnabled')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].category",
                          "equals": "Deadlocks"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/logs[*].enabled",
                          "equals": "[parameters('DeadlocksEnabled')]"
                        }
                      ]
                    }
                  ]
                }
              },
              "equals": 9
            },
            {
              "count": {
                "field": "Microsoft.Insights/diagnosticSettings/metrics[*]",
                "where": {
                  "anyOf": [
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/metrics[*].category",
                          "equals": "Basic"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/metrics[*].enabled",
                          "equals": "[parameters('Basic')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/metrics[*].category",
                          "equals": "InstanceAndAppAdvanced"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/metrics[*].enabled",
                          "equals": "[parameters('InstanceAndAppAdvanced')]"
                        }
                      ]
                    },
                    {
                      "allOf": [
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/metrics[*].category",
                          "equals": "WorkloadManagement"
                        },
                        {
                          "field": "Microsoft.Insights/diagnosticSettings/metrics[*].enabled",
                          "equals": "[parameters('WorkloadManagement')]"
                        }
                      ]
                    }
                  ]
                }
              },
              "equals": 3
            }
          ]
        },
        "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')]"
              }
            }
          }
        }
      }
    }
  }
}