Last active
December 20, 2016 08:28
-
-
Save krnese/240d2e080e30cabe37ba3104aad26160 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#", | |
| "contentVersion": "1.0.0.0", | |
| "parameters": { | |
| "location": { | |
| "type": "string", | |
| "defaultValue": "" | |
| }, | |
| "resourcegroup": { | |
| "type": "string", | |
| "defaultValue": "" | |
| }, | |
| "subscriptionId": { | |
| "type": "string", | |
| "defaultValue": "" | |
| }, | |
| "workspace": { | |
| "type": "string", | |
| "defaultValue": "" | |
| }, | |
| "workspaceapiversion": { | |
| "type": "string", | |
| "defaultValue": "2015-11-01-preview" | |
| } | |
| }, | |
| "variables": { | |
| "searches": { | |
| "group1": { | |
| "category": "Azure SQL", | |
| "queries": [ | |
| { | |
| "Name": "Azure SQL1", | |
| "Description": "A Database Transaction Unit (DTU) is a blended measure of CPU, memory, data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads. This alert is indicator that one of your databases maybe underperforming and unhealthy.", | |
| "severity": "Critical", | |
| "query": "Type=AzureMetrics ResourceId=*\"DATABASES\"* MetricName=dtu_consumption_percent | measure avg(Average) by Resource interval 1minute", | |
| "alert": true | |
| } | |
| ] | |
| } | |
| }, | |
| "alerts": { | |
| "group1": [ | |
| { | |
| "savedSearchId": "[concat(toLower(variables('searches').group1.category), '|', toLower(variables('searches').group1.queries[0].Name))]", | |
| "savedSearchName": "[variables('searches').group1.queries[0].Name]", | |
| "scheduleID": "[toLower(concat('sqlazure-schedule-', uniqueString(toLower(variables('searches').group1.category), '|', toLower(variables('searches').group1.queries[0].Name) )))]", | |
| "alertId": "[toLower(concat('sqlazure-alert-', uniqueString(toLower(variables('searches').group1.category), '|', toLower(variables('searches').group1.queries[0].Name) )))]", | |
| "scheduleIntervalMinutes": 15, | |
| "scheduleQueryTimeSpan": 15, | |
| "thresholdOperator": "gt", | |
| "thresholdValue": 15, | |
| "alertThrottleMinutes": 15, | |
| "severity": "Critical", | |
| "alertDescription": "A Database Transaction Unit (DTU) is a blended measure of CPU, memory, data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads. This alert is indicator that one of your databases maybe underperforming and unhealthy." | |
| } | |
| ] | |
| } | |
| }, | |
| "resources": [ | |
| { | |
| "apiVersion": "[parameters('workspaceapiversion')]", | |
| "name": "[parameters('workspace')]", | |
| "type": "Microsoft.OperationalInsights/workspaces", | |
| "location": "[parameters('location')]", | |
| "id": "[Concat('/subscriptions/', parameters('subscriptionId'), '/resourceGroups/', parameters('resourcegroup'), '/providers/Microsoft.OperationalInsights/workspaces/', parameters('workspace'))]", | |
| "resources": [ | |
| { | |
| "apiVersion": "2015-11-01-preview", | |
| "name": "Azure SQL Analytics", | |
| "type": "views", | |
| "location": "[parameters('location')]", | |
| "id": "[Concat('/subscriptions/', parameters('subscriptionId'), '/resourceGroups/', parameters('resourcegroup'), '/providers/Microsoft.OperationalInsights/workspaces/', parameters('workspace'),'/views/Azure SQL Analytics')]", | |
| "dependson": [ | |
| "[Concat('/subscriptions/', parameters('subscriptionId'), '/resourceGroups/', parameters('resourcegroup'), '/providers/Microsoft.OperationalInsights/workspaces/', parameters('workspace'))]" | |
| ], | |
| "properties": { | |
| "Id": "Azure SQL Analytics", | |
| "Name": "Azure SQL Analytics", | |
| "Description": "Azure SQL Monitoring and Analytics Solution", | |
| "Author": "satyavel@microsoft.com", | |
| "Source": "Local", | |
| "Dashboard": [ | |
| { | |
| "Id": "NumberTileListBuilderBlade", | |
| "Type": "Blade", | |
| "Version": 0, | |
| "Configuration": { | |
| "General": { | |
| "title": "SQL Azure Database Analytics", | |
| "newGroup": true, | |
| "icon": "", | |
| "useIcon": false | |
| }, | |
| "Tile": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/DATABASES/\"* MetricName-dtu_consumption_percent | measure avg(Average) by Resource | Where AggregatedValue>90", | |
| "Legend": "Top N Databases by DTU Utilization > 90%" | |
| }, | |
| "List": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/DATABASES/\"* MetricName=dtu_consumption_percent | measure avg(Average) by Resource", | |
| "HideGraph": false, | |
| "enableSparklines": true, | |
| "operation": "Summary", | |
| "ColumnsTitle": { | |
| "Name": "SQL Database Name", | |
| "Value": "Average % DTU" | |
| }, | |
| "Color": "#00bcf2", | |
| "thresholds": { | |
| "isEnabled": true, | |
| "values": [ | |
| { | |
| "name": "Normal", | |
| "threshold": "Default", | |
| "color": "#009e49", | |
| "isDefault": true | |
| }, | |
| { | |
| "name": "Warning", | |
| "threshold": "85", | |
| "color": "#fcd116", | |
| "isDefault": false | |
| }, | |
| { | |
| "name": "Error", | |
| "threshold": "90", | |
| "color": "#ba141a", | |
| "isDefault": false | |
| } | |
| ] | |
| }, | |
| "NameDSVSeparator": "", | |
| "NavigationQuery": "{selected item}" | |
| } | |
| } | |
| }, | |
| { | |
| "Id": "NumberTileListBuilderBlade", | |
| "Type": "Blade", | |
| "Version": 0, | |
| "Configuration": { | |
| "General": { | |
| "title": "", | |
| "newGroup": false, | |
| "icon": "", | |
| "useIcon": false | |
| }, | |
| "Tile": { | |
| "Legend": "Top N Databases by CPU Utilization > 90%", | |
| "Query": "Type=AzureMetrics ResourceId=*\"/DATABASES/\"* MetricName=cpu_percent | measure avg(Average) by Resource | Where AggregatedValue>90" | |
| }, | |
| "List": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/DATABASES/\"* MetricName=cpu_percent | measure avg(Average) by Resource", | |
| "HideGraph": false, | |
| "enableSparklines": true, | |
| "ColumnsTitle": { | |
| "Name": "SQL Database Name", | |
| "Value": "AVERAGE % CPU" | |
| }, | |
| "Color": "#007233", | |
| "operation": "Summary", | |
| "thresholds": { | |
| "isEnabled": true, | |
| "values": [ | |
| { | |
| "name": "Normal", | |
| "threshold": "Default", | |
| "color": "#009e49", | |
| "isDefault": true | |
| }, | |
| { | |
| "name": "Warning", | |
| "threshold": "85", | |
| "color": "#fcd116", | |
| "isDefault": false | |
| }, | |
| { | |
| "name": "Error", | |
| "threshold": "90", | |
| "color": "#ba141a", | |
| "isDefault": false | |
| } | |
| ] | |
| }, | |
| "NameDSVSeparator": "", | |
| "NavigationQuery": "{selected item}" | |
| } | |
| } | |
| }, | |
| { | |
| "Id": "NumberTileListBuilderBlade", | |
| "Type": "Blade", | |
| "Version": 0, | |
| "Configuration": { | |
| "General": { | |
| "title": "", | |
| "newGroup": false, | |
| "icon": "", | |
| "useIcon": false | |
| }, | |
| "Tile": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/DATABASES/\"* MetricName=storage_percent | measure avg(Average) by Resource | Where AggregatedValue>90", | |
| "Legend": "Top N Databases by Storage Consumption > 90%" | |
| }, | |
| "List": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/DATABASES/\"* MetricName=storage_percent | measure avg(Average) by Resource", | |
| "HideGraph": false, | |
| "enableSparklines": true, | |
| "operation": "Summary", | |
| "ColumnsTitle": { | |
| "Name": "SQL Database Name", | |
| "Value": "Average % Storage" | |
| }, | |
| "Color": "#002050", | |
| "thresholds": { | |
| "isEnabled": true, | |
| "values": [ | |
| { | |
| "name": "Normal", | |
| "threshold": "Default", | |
| "color": "#009e49", | |
| "isDefault": true | |
| }, | |
| { | |
| "name": "Warning", | |
| "threshold": "85", | |
| "color": "#fcd116", | |
| "isDefault": false | |
| }, | |
| { | |
| "name": "Error", | |
| "threshold": "90", | |
| "color": "#ba141a", | |
| "isDefault": false | |
| } | |
| ] | |
| }, | |
| "NameDSVSeparator": "", | |
| "NavigationQuery": "{selected item}" | |
| } | |
| } | |
| }, | |
| { | |
| "Id": "NumberTileListBuilderBlade", | |
| "Type": "Blade", | |
| "Version": 0, | |
| "Configuration": { | |
| "General": { | |
| "title": "SQL Azure Elastic Pools", | |
| "newGroup": true, | |
| "icon": "", | |
| "useIcon": false | |
| }, | |
| "Tile": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/ELASTICPOOLS/\"* MetricName-dtu_consumption_percent | measure avg(Average) by Resource | Where AggregatedValue>90", | |
| "Legend": "Top N Elastic Pools by DTU > 90%" | |
| }, | |
| "List": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/ELASTICPOOLS/\"* MetricName=dtu_consumption_percent | measure avg(Average) by Resource", | |
| "HideGraph": false, | |
| "enableSparklines": true, | |
| "operation": "Summary", | |
| "ColumnsTitle": { | |
| "Name": "SQL Elastic Pool Name", | |
| "Value": "Average % DTU" | |
| }, | |
| "Color": "#ff8c00", | |
| "thresholds": { | |
| "isEnabled": true, | |
| "values": [ | |
| { | |
| "name": "Normal", | |
| "threshold": "Default", | |
| "color": "#009e49", | |
| "isDefault": true | |
| }, | |
| { | |
| "name": "Warning", | |
| "threshold": "85", | |
| "color": "#fcd116", | |
| "isDefault": false | |
| }, | |
| { | |
| "name": "Error", | |
| "threshold": "90", | |
| "color": "#ba141a", | |
| "isDefault": false | |
| } | |
| ] | |
| }, | |
| "NameDSVSeparator": "", | |
| "NavigationQuery": "{selected item}" | |
| } | |
| } | |
| }, | |
| { | |
| "Id": "NumberTileListBuilderBlade", | |
| "Type": "Blade", | |
| "Version": 0, | |
| "Configuration": { | |
| "General": { | |
| "title": "", | |
| "newGroup": false, | |
| "icon": "", | |
| "useIcon": false | |
| }, | |
| "Tile": { | |
| "Legend": "Top N Elastic Pools by CPU >90%", | |
| "Query": "Type=AzureMetrics ResourceId=*\"/ELASTICPOOLS/\"* MetricName=cpu_percent | measure avg(Average) by Resource | Where AggregatedValue>90" | |
| }, | |
| "List": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/ELASTICPOOLS/\"* MetricName=cpu_percent | measure avg(Average) by Resource", | |
| "HideGraph": false, | |
| "enableSparklines": true, | |
| "ColumnsTitle": { | |
| "Name": "SQL ELASTIC POOL NAME", | |
| "Value": "AVERAGE % CPU" | |
| }, | |
| "Color": "#008272", | |
| "operation": "Summary", | |
| "thresholds": { | |
| "isEnabled": true, | |
| "values": [ | |
| { | |
| "name": "Normal", | |
| "threshold": "Default", | |
| "color": "#009e49", | |
| "isDefault": true | |
| }, | |
| { | |
| "name": "Warning", | |
| "threshold": "85", | |
| "color": "#fcd116", | |
| "isDefault": false | |
| }, | |
| { | |
| "name": "Error", | |
| "threshold": "90", | |
| "color": "#ba141a", | |
| "isDefault": false | |
| } | |
| ] | |
| }, | |
| "NameDSVSeparator": "", | |
| "NavigationQuery": "{selected item}" | |
| } | |
| } | |
| }, | |
| { | |
| "Id": "NumberTileListBuilderBlade", | |
| "Type": "Blade", | |
| "Version": 0, | |
| "Configuration": { | |
| "General": { | |
| "title": "", | |
| "newGroup": false, | |
| "icon": "", | |
| "useIcon": false | |
| }, | |
| "Tile": { | |
| "Legend": "Top N Elastic Pools by Storage Consumption > 90%", | |
| "Query": "Type=AzureMetrics ResourceId=*\"/ELASTICPOOLS/\"* MetricName=storage_percent | measure avg(Average) by Resource | Where AggregatedValue>90" | |
| }, | |
| "List": { | |
| "Query": "Type=AzureMetrics ResourceId=*\"/ELASTICPOOLS/\"* MetricName=storage_percent | measure avg(Average) by Resource", | |
| "HideGraph": false, | |
| "enableSparklines": true, | |
| "ColumnsTitle": { | |
| "Name": "SQL Elastic Pool Name", | |
| "Value": "Average % Storage" | |
| }, | |
| "Color": "#68217a", | |
| "operation": "Summary", | |
| "thresholds": { | |
| "isEnabled": true, | |
| "values": [ | |
| { | |
| "name": "Normal", | |
| "threshold": "Default", | |
| "color": "#009e49", | |
| "isDefault": true | |
| }, | |
| { | |
| "name": "Warning", | |
| "threshold": "85", | |
| "color": "#fcd116", | |
| "isDefault": false | |
| }, | |
| { | |
| "name": "Error", | |
| "threshold": "90", | |
| "color": "#ba141a", | |
| "isDefault": false | |
| } | |
| ] | |
| }, | |
| "NameDSVSeparator": "", | |
| "NavigationQuery": "{selected item}" | |
| } | |
| } | |
| }, | |
| { | |
| "Id": "NumberTileListBuilderBlade", | |
| "Type": "Blade", | |
| "Version": 0, | |
| "Configuration": { | |
| "General": { | |
| "title": "SQL Azure Activity Logs", | |
| "newGroup": true, | |
| "icon": "", | |
| "useIcon": false | |
| }, | |
| "Tile": { | |
| "Legend": "SQL Azure Activity Audit", | |
| "Query": "Type=AzureActivity ResourceProvider= \"Microsoft SQL\" | measure count() by OperationName" | |
| }, | |
| "List": { | |
| "Query": "Type=AzureActivity ResourceProvider= \"Microsoft SQL\" | measure count() by OperationName", | |
| "HideGraph": false, | |
| "enableSparklines": false, | |
| "ColumnsTitle": { | |
| "Name": "SQL Operation Name", | |
| "Value": "Count" | |
| }, | |
| "Color": "#0072c6", | |
| "operation": "Summary", | |
| "thresholds": { | |
| "isEnabled": false, | |
| "values": [ | |
| { | |
| "name": "Normal", | |
| "threshold": "Default", | |
| "color": "#009e49", | |
| "isDefault": true | |
| }, | |
| { | |
| "name": "Warning", | |
| "threshold": "60", | |
| "color": "#fcd116", | |
| "isDefault": false | |
| }, | |
| { | |
| "name": "Error", | |
| "threshold": "90", | |
| "color": "#ba141a", | |
| "isDefault": false | |
| } | |
| ] | |
| }, | |
| "NameDSVSeparator": "", | |
| "NavigationQuery": "{selected item}" | |
| } | |
| } | |
| }, | |
| { | |
| "Id": "NotableQueriesBuilderBlade", | |
| "Type": "Blade", | |
| "Version": 0, | |
| "Configuration": { | |
| "General": { | |
| "title": "List of popular SQL Azure Search Queries", | |
| "newGroup": true, | |
| "preselectedFilters": "Type, Computer", | |
| "renderMode": "grid" | |
| }, | |
| "queries": [ | |
| { | |
| "query": "Type=AzureMetrics ResourceProvider=\"MICROSOFT.SQL\"", | |
| "displayName": "All SQL Azure Data" | |
| }, | |
| { | |
| "query": "Type=AzureMetrics ResourceProvider=\"MICROSOFT.SQL\" ResourceId=*\"/DATABASES/\"* | measure count() by MetricName", | |
| "displayName": "List of all SQL Azure Database Performance Metrics" | |
| }, | |
| { | |
| "query": "Type=AzureMetrics ResourceProvider=\"MICROSOFT.SQL\" ResourceId=*\"/ELASTICPOOLS/\"* | measure count() by MetricName", | |
| "displayName": "List of all SQL Azure Elastic Pool Performance Metrics" | |
| }, | |
| { | |
| "query": "Type=AzureMetrics ResourceId=*\"/DATABASES/\"* MetricName=dtu_consumption_percent | measure avg(Average) by MetricName interval 1minute", | |
| "displayName": "Database DTU Consumption over time" | |
| }, | |
| { | |
| "query": "Type=AzureMetrics ResourceId=*\"/ELASTICPOOLS/\"* MetricName=dtu_consumption_percent | measure avg(Average) by MetricName interval 1minute", | |
| "displayName": "Elastic Pool DTU Consumption over time" | |
| } | |
| ] | |
| } | |
| } | |
| ], | |
| "OverviewTile": { | |
| "Id": "DoubleNumberBuilderTile", | |
| "Type": "OverviewTile", | |
| "Version": 0, | |
| "Configuration": { | |
| "TileOne": { | |
| "Legend": "Total SQL Azure Databases", | |
| "Query": "Type=AzureMetrics ResourceId=*\"/DATABASES/\"* | measure countdistinct(Resource) by Resource | Top 100000" | |
| }, | |
| "TileTwo": { | |
| "Legend": "Total SQL Azure Elastic Pools", | |
| "Query": "Type=AzureMetrics ResourceId=*\"/ELASTICPOOLS/\"* | measure countdistinct(Resource) by Resource | Top 100000" | |
| }, | |
| "Advanced": { | |
| "DataFlowVerification": { | |
| "Enabled": false, | |
| "Query": "*", | |
| "Message": "" | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "apiVersion": "2015-11-01-preview", | |
| "type": "Microsoft.OperationalInsights/workspaces/savedSearches", | |
| "name": "[concat(parameters('workspace'), '/', toLower(variables('searches').group1.category), '|', toLower(variables('searches').group1.queries[copyIndex()].Name))]", | |
| "dependsOn": [], | |
| "copy": { | |
| "name": "[concat(replace(parameters('workspace'), ' ', '') , 'search1Copy')]", | |
| "count": "[length(variables('searches').group1.queries)]" | |
| }, | |
| "properties": { | |
| "query": "[variables('searches').group1.queries[copyIndex()].query]", | |
| "displayName": "[variables('searches').group1.queries[copyIndex()].Name]", | |
| "category": "[variables('searches').group1.category]", | |
| "Etag": "*" | |
| } | |
| }, | |
| { | |
| "apiVersion": "2015-11-01-preview", | |
| "type": "Microsoft.OperationalInsights/workspaces/savedSearches/schedules/", | |
| "name": "[concat(parameters('workspace'), '/', variables('alerts').group1[copyIndex()].savedSearchId, '/', variables('alerts').group1[copyIndex()].scheduleId)]", | |
| "dependsOn": [ | |
| "[concat(replace(parameters('workspace'), ' ', '') , 'search1Copy')]" | |
| ], | |
| "copy": { | |
| "name": "[concat(replace(parameters('workspace'), ' ', '') , 'schedule1Copy')]", | |
| "count": "[length(variables('alerts').group1)]" | |
| }, | |
| "properties": { | |
| "Interval": "[variables('alerts').group1[copyIndex()].scheduleIntervalMinutes]", | |
| "QueryTimeSpan": "[variables('alerts').group1[copyIndex()].scheduleQueryTimeSpan]", | |
| "enabled": true, | |
| "Etag": "*" | |
| } | |
| }, | |
| { | |
| "apiVersion": "2015-11-01-preview", | |
| "type": "Microsoft.OperationalInsights/workspaces/savedSearches/schedules/actions", | |
| "name": "[concat(parameters('workspace'), '/', variables('alerts').group1[copyIndex()].savedSearchId, '/', variables('alerts').group1[copyIndex()].scheduleId, '/', variables('alerts').group1[copyIndex()].alertId)]", | |
| "dependsOn": [ | |
| "[concat(replace(parameters('workspace'), ' ', '') , 'search1Copy')]", | |
| "[concat(replace(parameters('workspace'), ' ', '') , 'schedule1Copy')]" | |
| ], | |
| "copy": { | |
| "name": "[concat(replace(parameters('workspace'), ' ', '') , 'alert1Copy')]", | |
| "count": "[length(variables('alerts').group1)]" | |
| }, | |
| "properties": { | |
| "Type": "Alert", | |
| "Name": "[variables('alerts').group1[copyIndex()].savedSearchName]", | |
| "Description": "[variables('alerts').group1[copyIndex()].alertDescription]", | |
| "Severity": "[variables('alerts').group1[copyIndex()].Severity]", | |
| "Throttling": { | |
| "DurationInMinutes": 15 | |
| }, | |
| "MetricsTrigger": { | |
| "TriggerCondition": "Total", | |
| "Operator": "[variables('alerts').group1[copyIndex()].thresholdOperator]", | |
| "Value": "[variables('alerts').group1[copyIndex()].thresholdValue]" | |
| }, | |
| "ETag": "*" | |
| } | |
| } | |
| ], | |
| "outputs": {} | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment