{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"crossComponentResources": [
"value::all"
],
"parameters": [
{
"id": "c4475a86-95ea-46b8-a5c0-3ee1c3f528bc",
"version": "KqlParameterItem/1.0",
"name": "TimeRange",
"type": 4,
"value": {
"durationMs": 43200000
},
"typeSettings": {
"selectableValues": [
{
"durationMs": 300000
},
{
"durationMs": 900000
},
{
"durationMs": 1800000
},
{
"durationMs": 3600000
},
{
"durationMs": 14400000
},
{
"durationMs": 43200000
},
{
"durationMs": 86400000
},
{
"durationMs": 172800000
},
{
"durationMs": 259200000
},
{
"durationMs": 604800000
},
{
"durationMs": 1209600000
},
{
"durationMs": 2419200000
},
{
"durationMs": 2592000000
},
{
"durationMs": 5184000000
},
{
"durationMs": 7776000000
}
]
},
"timeContext": {
"durationMs": 86400000
}
},
{
"id": "94b22c78-91c8-47e6-8833-70d458e199c3",
"version": "KqlParameterItem/1.0",
"name": "Subscriptions",
"type": 6,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"typeSettings": {
"additionalResourceOptions": [
"value::1",
"value::all"
],
"includeAll": false,
"showDefault": false
},
"defaultValue": "value::all",
"value": [
"value::all"
]
},
{
"id": "3dbc1029-1a17-4a4d-a133-2d47c1e7844e",
"version": "KqlParameterItem/1.0",
"name": "Synapse",
"type": 5,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "Resources\r\n| where type == 'microsoft.synapse/workspaces'\r\n| project id\r\n",
"crossComponentResources": [
"{Subscriptions}"
],
"typeSettings": {
"additionalResourceOptions": [
"value::1",
"value::all"
],
"showDefault": false
},
"timeContext": {
"durationMs": 86400000
},
"defaultValue": "value::all",
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources",
"value": [
"value::all"
]
},
{
"id": "4d1459db-354e-4019-8e86-015d946ce892",
"version": "KqlParameterItem/1.0",
"name": "LogAnalyticsWorkspace",
"type": 5,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "Resources\r\n| where type == 'microsoft.operationalinsights/workspaces'\r\n| project id\r\n",
"crossComponentResources": [
"value::all"
],
"value": [
"value::all"
],
"typeSettings": {
"additionalResourceOptions": [
"value::1",
"value::all"
],
"showDefault": false
},
"defaultValue": "value::all",
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources"
}
],
"style": "pills",
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources"
},
"name": "parameters - 1"
},
{
"type": 1,
"content": {
"json": "All costs listed in this workbook are approximate based on $5/TB and data processed is set to a minimum of 10MB. All calculations in the query results are using the charged data processed as opposed to the actual data processed. ",
"style": "warning"
},
"name": "text - 4"
},
{
"type": 11,
"content": {
"version": "LinkItem/1.0",
"style": "tabs",
"links": [
{
"id": "4ee15681-1a46-4207-ac59-efab22cd6d9b",
"cellValue": "GroupSelection",
"linkTarget": "parameter",
"linkLabel": "Summary",
"subTarget": "Summary",
"style": "link"
},
{
"id": "82d489b0-87d6-4875-a5de-c7eb2d1c8c4f",
"cellValue": "GroupSelection",
"linkTarget": "parameter",
"linkLabel": "Data Processed",
"subTarget": "DataProcessed",
"style": "link"
},
{
"id": "7c0e9efd-a60a-40d7-a290-12133a09a440",
"cellValue": "GroupSelection",
"linkTarget": "parameter",
"linkLabel": "Query Performance",
"subTarget": "QueryPerformance",
"style": "link"
}
]
},
"name": "links - 4"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 10,
"content": {
"chartId": "workbook9cfd0268-d682-40bb-870c-61e05013f973",
"version": "MetricsItem/2.0",
"size": 4,
"chartType": -1,
"resourceType": "microsoft.synapse/workspaces",
"metricScope": 0,
"resourceParameter": "Synapse",
"resourceIds": [
"{Synapse}"
],
"timeContextFromParameter": "TimeRange",
"timeContext": {
"durationMs": 86400000
},
"metrics": [
{
"namespace": "microsoft.synapse/workspaces",
"metric": "microsoft.synapse/workspaces-Builtin SQL Pool-BuiltinSqlPoolLoginAttempts",
"aggregation": 1
}
],
"title": "Login Attempts",
"gridFormatType": 1,
"tileSettings": {
"showBorder": false,
"titleContent": {
"columnMatch": "Name",
"formatter": 13
},
"leftContent": {
"columnMatch": "Value",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
},
"timeBrushParameterName": "TimeRange",
"gridSettings": {
"rowLimit": 10000
}
},
"customWidth": "33",
"showPin": true,
"name": "LoginAttemptsChart - Copy"
},
{
"type": 10,
"content": {
"chartId": "workbookdc90b0c5-4f40-4242-89b8-87eac7b9acaf",
"version": "MetricsItem/2.0",
"size": 4,
"chartType": -1,
"resourceType": "microsoft.synapse/workspaces",
"metricScope": 0,
"resourceParameter": "Synapse",
"resourceIds": [
"{Synapse}"
],
"timeContextFromParameter": "TimeRange",
"timeContext": {
"durationMs": 86400000
},
"metrics": [
{
"namespace": "microsoft.synapse/workspaces",
"metric": "microsoft.synapse/workspaces-Builtin SQL Pool-BuiltinSqlPoolRequestsEnded",
"aggregation": 1,
"columnName": "Requests Completed"
}
],
"title": "Completed Requests",
"gridFormatType": 1,
"tileSettings": {
"showBorder": false,
"titleContent": {
"columnMatch": "Name",
"formatter": 13
},
"leftContent": {
"columnMatch": "Value",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
},
"timeBrushParameterName": "TimeRange",
"timeBrushExportOnlyWhenBrushed": true,
"gridSettings": {
"rowLimit": 10000
}
},
"customWidth": "33",
"showPin": true,
"name": "CompletedRequestCountTile"
},
{
"type": 10,
"content": {
"chartId": "workbook1f0054e6-9715-47bb-a7fc-e92812c07403",
"version": "MetricsItem/2.0",
"size": 4,
"chartType": -1,
"resourceType": "microsoft.synapse/workspaces",
"metricScope": 0,
"resourceParameter": "Synapse",
"resourceIds": [
"{Synapse}"
],
"timeContextFromParameter": "TimeRange",
"timeContext": {
"durationMs": 86400000
},
"metrics": [
{
"namespace": "microsoft.synapse/workspaces",
"metric": "microsoft.synapse/workspaces-Builtin SQL Pool-BuiltinSqlPoolDataProcessedBytes",
"aggregation": 1
}
],
"title": "Data Processed",
"gridFormatType": 1,
"tileSettings": {
"showBorder": false,
"titleContent": {
"columnMatch": "Name",
"formatter": 13
},
"leftContent": {
"columnMatch": "Value",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
},
"timeBrushParameterName": "TimeRange",
"timeBrushExportOnlyWhenBrushed": true,
"gridSettings": {
"rowLimit": 10000
}
},
"customWidth": "33",
"showPin": true,
"name": "DataProcessed - Copy"
},
{
"type": 10,
"content": {
"chartId": "workbookdc90b0c5-4f40-4242-89b8-87eac7b9acaf",
"version": "MetricsItem/2.0",
"size": 0,
"chartType": 1,
"resourceType": "microsoft.synapse/workspaces",
"metricScope": 0,
"resourceParameter": "Synapse",
"resourceIds": [
"{Synapse}"
],
"timeContextFromParameter": "TimeRange",
"timeContext": {
"durationMs": 86400000
},
"metrics": [
{
"namespace": "microsoft.synapse/workspaces",
"metric": "microsoft.synapse/workspaces-Builtin SQL Pool-BuiltinSqlPoolRequestsEnded",
"aggregation": 1,
"splitBy": null,
"columnName": "Requests Completed"
}
],
"title": "Completed Requests",
"timeBrushParameterName": "TimeRange",
"timeBrushExportOnlyWhenBrushed": true,
"gridSettings": {
"rowLimit": 10000
}
},
"customWidth": "50",
"showPin": true,
"name": "CompletedRequestCount"
},
{
"type": 10,
"content": {
"chartId": "workbook1f0054e6-9715-47bb-a7fc-e92812c07403",
"version": "MetricsItem/2.0",
"size": 0,
"chartType": 2,
"resourceType": "microsoft.synapse/workspaces",
"metricScope": 0,
"resourceParameter": "Synapse",
"resourceIds": [
"{Synapse}"
],
"timeContextFromParameter": "TimeRange",
"timeContext": {
"durationMs": 86400000
},
"metrics": [
{
"namespace": "microsoft.synapse/workspaces",
"metric": "microsoft.synapse/workspaces-Builtin SQL Pool-BuiltinSqlPoolDataProcessedBytes",
"aggregation": 1,
"splitBy": null
}
],
"title": "Data Processed",
"timeBrushParameterName": "TimeRange",
"timeBrushExportOnlyWhenBrushed": true,
"gridSettings": {
"rowLimit": 10000
}
},
"customWidth": "50",
"showPin": true,
"name": "DataProcessed"
},
{
"type": 10,
"content": {
"chartId": "workbook9cfd0268-d682-40bb-870c-61e05013f973",
"version": "MetricsItem/2.0",
"size": 0,
"chartType": 2,
"resourceType": "microsoft.synapse/workspaces",
"metricScope": 0,
"resourceParameter": "Synapse",
"resourceIds": [
"{Synapse}"
],
"timeContextFromParameter": "TimeRange",
"timeContext": {
"durationMs": 86400000
},
"metrics": [
{
"namespace": "microsoft.synapse/workspaces",
"metric": "microsoft.synapse/workspaces-Builtin SQL Pool-BuiltinSqlPoolLoginAttempts",
"aggregation": 1,
"splitBy": null
}
],
"title": "Login Attempts",
"timeBrushParameterName": "TimeRange",
"gridSettings": {
"rowLimit": 10000
}
},
"showPin": true,
"name": "LoginAttemptsChart"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Queries by completion type\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| where _ResourceId in~ ({Synapse})\r\n| summarize count() by ResultType\r\n| render piechart ",
"size": 3,
"showAnalytics": true,
"title": "Queries by Result Type",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
],
"gridSettings": {
"sortBy": [
{
"itemKey": "$gen_link_Properties_9",
"sortOrder": 1
}
]
},
"sortBy": [
{
"itemKey": "$gen_link_Properties_9",
"sortOrder": 1
}
]
},
"customWidth": "33",
"showPin": true,
"name": "QueriesByResultType"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Queries by query type\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| where _ResourceId in~ ({Synapse})\r\n| project CommandType=tostring(Properties.command)\r\n| summarize count() by CommandType\r\n| render piechart \r\n\r\n",
"size": 3,
"showAnalytics": true,
"title": "Queries by Query Type",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
]
},
"customWidth": "33",
"showPin": true,
"name": "Queries by Query Type"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Queries by completion type\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| where _ResourceId in~ ({Synapse})\r\n| project BytesProcessed=Properties.dataProcessedBytes,tostring(Identity)\r\n| summarize sum(toint(BytesProcessed)) by Identity\r\n| render piechart \r\n\r\n",
"size": 3,
"showAnalytics": true,
"title": "Data Processed By User",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
]
},
"customWidth": "33",
"showPin": true,
"name": "Data Processed By User"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Most Recent Query Runs\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| order by TimeGenerated\r\n| where _ResourceId in~ ({Synapse})\r\n| project \r\n StartTime=Properties.startTime,\r\n EndTime=Properties.endTime, \r\n Identity,\r\n CommandType=Properties.command,\r\n Result=ResultType,\r\n MbProcessed=(Properties.dataProcessedBytes/1000/1000),\r\n QueryText=Properties.queryText,\r\n _ResourceId\r\n| extend elapsedTime_sec =(todatetime(EndTime) - StartTime)/1s\r\n| extend Approx_Cost=((MbProcessed)*5.0/100000.0)/100 //multiply by price per mb ($5 per TB from Microsoft Docs: https://azure.microsoft.com/en-us/pricing/details/synapse-analytics/)\r\n| project-reorder StartTime,EndTime,elapsedTime_sec,Identity,CommandType,Result,MbProcessed,Approx_Cost\r\n",
"size": 2,
"showAnalytics": true,
"title": "Recent Queries",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
],
"gridSettings": {
"formatters": [
{
"columnMatch": "StartTime",
"formatter": 6
},
{
"columnMatch": "EndTime",
"formatter": 6
},
{
"columnMatch": "elapsedTime_sec",
"formatter": 8,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "Result",
"formatter": 18,
"formatOptions": {
"thresholdsOptions": "icons",
"thresholdsGrid": [
{
"operator": "Default",
"thresholdValue": null,
"representation": "success",
"text": "{0}{1}"
}
]
}
},
{
"columnMatch": "MbProcessed",
"formatter": 3,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "Approx_Cost",
"formatter": 8,
"formatOptions": {
"palette": "yellow"
},
"numberFormat": {
"unit": 0,
"options": {
"style": "decimal",
"minimumFractionDigits": 2,
"maximumFractionDigits": 6
}
}
},
{
"columnMatch": "QueryText",
"formatter": 7,
"formatOptions": {
"linkTarget": "CellDetails",
"linkIsContextBlade": true,
"customColumnWidthSetting": "100ch"
}
},
{
"columnMatch": "BytesProcessed",
"formatter": 8,
"formatOptions": {
"palette": "red"
}
}
]
}
},
"showPin": true,
"name": "Recent Queries"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Overall concurrency\r\nlet snapshotTelemetryInterval = 1m;//1m;//1h;//30m;//Do not change\r\nlet minTelemetryDate = toscalar(SynapseSqlPoolExecRequests | summarize min(StartTime));\r\nlet maxTelemetryDate = toscalar(SynapseSqlPoolExecRequests | summarize max(EndTime));\r\nlet timeIntervals = range SnapshotTimeStamp from bin(minTelemetryDate, snapshotTelemetryInterval) to bin(maxTelemetryDate, snapshotTelemetryInterval) step snapshotTelemetryInterval;\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| order by TimeGenerated\r\n| where _ResourceId in~ ({Synapse})\r\n| project \r\n StartTime=todatetime(Properties.startTime),\r\n EndTime=todatetime(Properties.endTime), \r\n MbProcessed=(Properties.dataProcessedBytes/1000/1000)\r\n| extend elapsedTime_sec =(EndTime - StartTime)/1s\r\n| mv-expand TimeInterval=range(bin(StartTime, snapshotTelemetryInterval), bin(EndTime, snapshotTelemetryInterval) , snapshotTelemetryInterval) limit 100000\r\n| summarize ActiveQueryCount=count(),sum(MbProcessed),avg(elapsedTime_sec) by todatetime(TimeInterval)\r\n",
"size": 2,
"showAnalytics": true,
"title": "Concurrency",
"timeContextFromParameter": "TimeRange",
"timeBrushParameterName": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
],
"visualization": "linechart",
"gridSettings": {
"formatters": [
{
"columnMatch": "StartTime",
"formatter": 6
},
{
"columnMatch": "EndTime",
"formatter": 6
},
{
"columnMatch": "elapsedTime_sec",
"formatter": 8,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "Result",
"formatter": 18,
"formatOptions": {
"thresholdsOptions": "icons",
"thresholdsGrid": [
{
"operator": "Default",
"thresholdValue": null,
"representation": "success",
"text": "{0}{1}"
}
]
}
},
{
"columnMatch": "MbProcessed",
"formatter": 3,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "Approx_Cost",
"formatter": 8,
"formatOptions": {
"palette": "yellow"
},
"numberFormat": {
"unit": 0,
"options": {
"style": "decimal",
"minimumFractionDigits": 2,
"maximumFractionDigits": 6
}
}
},
{
"columnMatch": "QueryText",
"formatter": 7,
"formatOptions": {
"linkTarget": "CellDetails",
"linkIsContextBlade": true,
"customColumnWidthSetting": "100ch"
}
},
{
"columnMatch": "BytesProcessed",
"formatter": 8,
"formatOptions": {
"palette": "red"
}
}
]
},
"graphSettings": {
"type": 0
}
},
"showPin": true,
"name": "Concurrency"
}
]
},
"conditionalVisibility": {
"parameterName": "GroupSelection",
"comparison": "isEqualTo",
"value": "Summary"
},
"name": "SummaryGroup"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 10,
"content": {
"chartId": "workbook1f0054e6-9715-47bb-a7fc-e92812c07403",
"version": "MetricsItem/2.0",
"size": 4,
"chartType": -1,
"resourceType": "microsoft.synapse/workspaces",
"metricScope": 0,
"resourceParameter": "Synapse",
"resourceIds": [
"{Synapse}"
],
"timeContextFromParameter": "TimeRange",
"timeContext": {
"durationMs": 0
},
"metrics": [
{
"namespace": "microsoft.synapse/workspaces",
"metric": "microsoft.synapse/workspaces-Builtin SQL Pool-BuiltinSqlPoolDataProcessedBytes",
"aggregation": 1
}
],
"title": "Total Data Processed",
"gridFormatType": 1,
"tileSettings": {
"showBorder": false,
"titleContent": {
"columnMatch": "Name",
"formatter": 13
},
"leftContent": {
"columnMatch": "Value",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
},
"timeBrushParameterName": "TimeRange",
"timeBrushExportOnlyWhenBrushed": true,
"gridSettings": {
"rowLimit": 10000
}
},
"showPin": true,
"name": "DataProcessed"
},
{
"type": 10,
"content": {
"chartId": "workbook1f0054e6-9715-47bb-a7fc-e92812c07403",
"version": "MetricsItem/2.0",
"size": 0,
"chartType": 2,
"resourceType": "microsoft.synapse/workspaces",
"metricScope": 0,
"resourceParameter": "Synapse",
"resourceIds": [
"{Synapse}"
],
"timeContextFromParameter": "TimeRange",
"timeContext": {
"durationMs": 86400000
},
"metrics": [
{
"namespace": "microsoft.synapse/workspaces",
"metric": "microsoft.synapse/workspaces-Builtin SQL Pool-BuiltinSqlPoolDataProcessedBytes",
"aggregation": 1,
"splitBy": null
}
],
"title": "Data Processed and Request Completed Count",
"showOpenInMe": true,
"timeBrushParameterName": "TimeRange",
"timeBrushExportOnlyWhenBrushed": true,
"gridSettings": {
"rowLimit": 10000
}
},
"showPin": true,
"name": "DataProcessed - Copy"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Queries by completion type\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| where _ResourceId in~ ({Synapse})\r\n| project \r\n BytesProcessed=Properties.dataProcessedBytes,\r\n tostring(Identity),\r\n QueryHash=tostring(Properties.queryHash),\r\n QueryText=tostring(Properties.queryText),\r\n _ResourceId\r\n| extend MbProcessed = case (BytesProcessed < 10000000,10000000,\r\n\t\t\t\t\t\t\tBytesProcessed)/1000/1000\r\n| summarize \r\n MaxSingleQueryMBProcessed=max(toint(MbProcessed)),\r\n TotalMBProcessed=sum(toint(MbProcessed)),\r\n QueryCount=count(),\r\n QueryHash=any(QueryHash) ,\r\n Resource=any(_ResourceId)\r\n by QueryText //using queryText because queryHash doesn’t change if only literals are different\r\n| extend Approx_Accumulated_Cost=((TotalMBProcessed)*5.0/100000.0)/100 \r\n| extend Approx_Cost_Per_Execution=((TotalMBProcessed)*5.0/100000.0)/100/QueryCount\r\n| order by TotalMBProcessed\r\n",
"size": 0,
"showAnalytics": true,
"title": "Data Processed By Query",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
],
"visualization": "table",
"gridSettings": {
"formatters": [
{
"columnMatch": "QueryText",
"formatter": 7,
"formatOptions": {
"linkTarget": "CellDetails",
"linkIsContextBlade": true
}
},
{
"columnMatch": "MaxSingleQueryMBProcessed",
"formatter": 8,
"formatOptions": {
"palette": "orange"
}
},
{
"columnMatch": "TotalMBProcessed",
"formatter": 3,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "QueryCount",
"formatter": 8,
"formatOptions": {
"min": 0,
"palette": "turquoise"
}
},
{
"columnMatch": "Approx_Accumulated_Cost",
"formatter": 8,
"formatOptions": {
"palette": "yellow"
},
"numberFormat": {
"unit": 0,
"options": {
"style": "decimal",
"minimumFractionDigits": 2,
"maximumFractionDigits": 4
}
}
},
{
"columnMatch": "Approx_Cost_Per_Execution",
"formatter": 0,
"numberFormat": {
"unit": 0,
"options": {
"style": "decimal",
"minimumFractionDigits": 2,
"maximumFractionDigits": 6
}
}
},
{
"columnMatch": "MaxSingleQueryBytesProcessed",
"formatter": 8,
"formatOptions": {
"palette": "orange"
}
},
{
"columnMatch": "TotalBytesProcessed",
"formatter": 3,
"formatOptions": {
"palette": "blue",
"compositeBarSettings": {
"labelText": "",
"columnSettings": []
},
"customColumnWidthSetting": "866.007px"
}
},
{
"columnMatch": "I",
"formatter": 3,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "any_QueryText",
"formatter": 7,
"formatOptions": {
"linkTarget": "CellDetails",
"linkIsContextBlade": true
}
}
]
},
"sortBy": []
},
"showPin": true,
"name": "Data Processed By Query",
"styleSettings": {
"showBorder": true
}
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Queries by completion type\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| where _ResourceId in~ ({Synapse})\r\n| project BytesProcessed=Properties.dataProcessedBytes\r\n\t\t, tostring(Identity)\r\n| extend MbProcessed = case (BytesProcessed < 10000000,10000000,\r\n BytesProcessed)/1000/1000\r\n| summarize\r\n TotalMBProcessed=sum(toint(MbProcessed)),\r\n QueryCount=count(),\r\n MaxSingleQueryMBProcessed=max(toint(MbProcessed) )\r\n by Identity\r\n| extend Approx_Cost=((TotalMBProcessed) *5.0/100000.0) / 100 \r\n| order by TotalMBProcessed",
"size": 0,
"showAnalytics": true,
"title": "Data Processed By User with $5 per TB cost",
"timeContextFromParameter": "TimeRange",
"exportFieldName": "Identity",
"exportParameterName": "Identity",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
],
"visualization": "table",
"gridSettings": {
"formatters": [
{
"columnMatch": "TotalMBProcessed",
"formatter": 3,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "QueryCount",
"formatter": 8,
"formatOptions": {
"min": 0,
"palette": "turquoise"
}
},
{
"columnMatch": "MaxSingleQueryMBProcessed",
"formatter": 8,
"formatOptions": {
"palette": "orange"
}
},
{
"columnMatch": "Approx_Cost",
"formatter": 0,
"numberFormat": {
"unit": 0,
"options": {
"style": "decimal",
"useGrouping": true,
"minimumFractionDigits": 2,
"maximumFractionDigits": 4
}
}
},
{
"columnMatch": "TotalBytesProcessed",
"formatter": 3,
"formatOptions": {
"min": 0,
"palette": "blue",
"compositeBarSettings": {
"labelText": "",
"columnSettings": []
},
"customColumnWidthSetting": "866.007px"
}
},
{
"columnMatch": "MaxSingleQueryBytesProcessed",
"formatter": 8,
"formatOptions": {
"min": 0,
"palette": "orange"
}
}
]
}
},
"showPin": true,
"name": "Data Processed By User - With Cost",
"styleSettings": {
"showBorder": true
}
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Query runs by user sorted by bytes processed\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| where _ResourceId in~ ({Synapse})\r\n| where Identity == '{Identity}'\r\n| project \r\n StartTime=Properties.startTime,\r\n EndTime=Properties.endTime, \r\n Identity,\r\n CommandType=Properties.command,\r\n Result=ResultType,\r\n BytesProcessed=Properties.dataProcessedBytes,\r\n QueryText=Properties.queryText\r\n| order by toint(BytesProcessed)\r\n",
"size": 2,
"title": "Query Summary for User by Data Processed",
"noDataMessage": "Select a user to see their query history",
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
],
"gridSettings": {
"formatters": [
{
"columnMatch": "StartTime",
"formatter": 6
},
{
"columnMatch": "EndTime",
"formatter": 6
},
{
"columnMatch": "Result",
"formatter": 18,
"formatOptions": {
"thresholdsOptions": "icons",
"thresholdsGrid": [
{
"operator": "Default",
"thresholdValue": null,
"representation": "success",
"text": "{0}{1}"
}
]
}
},
{
"columnMatch": "BytesProcessed",
"formatter": 8,
"formatOptions": {
"min": 0,
"palette": "blue"
}
},
{
"columnMatch": "QueryText",
"formatter": 7,
"formatOptions": {
"linkTarget": "CellDetails",
"linkIsContextBlade": true,
"customColumnWidthSetting": "100ch"
}
}
]
}
},
"conditionalVisibility": {
"parameterName": "Identity",
"comparison": "isNotEqualTo"
},
"name": "Query Summary for User by Data Processed"
}
]
},
"conditionalVisibility": {
"parameterName": "GroupSelection",
"comparison": "isEqualTo",
"value": "DataProcessed"
},
"name": "DataProcessed"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"title": "Query Performance",
"items": [
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Most Recent Query Runs\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| order by TimeGenerated\r\n| where _ResourceId in~ ({Synapse})\r\n| project \r\n StartTime=Properties.startTime,\r\n EndTime=Properties.endTime, \r\n Identity,\r\n CommandType=Properties.command,\r\n Result=ResultType,\r\n MbProcessed=(Properties.dataProcessedBytes/1000/1000),\r\n QueryText=Properties.queryText,\r\n QueryHash=Properties.queryHash,\r\n _ResourceId\r\n| extend elapsedTime_sec =(todatetime(EndTime) - StartTime)/1s\r\n| extend Approx_Cost=((MbProcessed)*5.0/100000.0)/100 //multiply by price per mb ($5 per TB from Microsoft Docs: https://azure.microsoft.com/en-us/pricing/details/synapse-analytics/)\r\n| project-reorder StartTime,EndTime,elapsedTime_sec,Identity,CommandType,Result,MbProcessed,Approx_Cost\r\n",
"size": 2,
"showAnalytics": true,
"title": "Recent Queries",
"exportFieldName": "QueryHash",
"exportParameterName": "QueryHash",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
],
"gridSettings": {
"formatters": [
{
"columnMatch": "StartTime",
"formatter": 6
},
{
"columnMatch": "EndTime",
"formatter": 6
},
{
"columnMatch": "elapsedTime_sec",
"formatter": 8,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "Result",
"formatter": 18,
"formatOptions": {
"thresholdsOptions": "icons",
"thresholdsGrid": [
{
"operator": "Default",
"thresholdValue": null,
"representation": "success",
"text": "{0}{1}"
}
]
}
},
{
"columnMatch": "MbProcessed",
"formatter": 3,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "Approx_Cost",
"formatter": 8,
"formatOptions": {
"palette": "yellow"
},
"numberFormat": {
"unit": 0,
"options": {
"style": "decimal",
"minimumFractionDigits": 2,
"maximumFractionDigits": 6
}
}
},
{
"columnMatch": "QueryText",
"formatter": 7,
"formatOptions": {
"linkTarget": "CellDetails",
"linkIsContextBlade": true,
"customColumnWidthSetting": "100ch"
}
},
{
"columnMatch": "BytesProcessed",
"formatter": 8,
"formatOptions": {
"palette": "red"
}
}
]
}
},
"showPin": true,
"name": "Recent Queries - Copy"
},
{
"type": 1,
"content": {
"json": "Select a query to view graphical history of each run"
},
"name": "text - 2"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//Most Recent Query Runs\r\nSynapseBuiltinSqlPoolRequestsEnded\r\n| order by TimeGenerated\r\n| where _ResourceId in~ ({Synapse})\r\n| where Properties.queryHash == {QueryHash}\r\n| project \r\n StartTime=Properties.startTime,\r\n EndTime=Properties.endTime, \r\n Identity,\r\n RequestId=Properties.clientRequestId,\r\n StatementId = Properties.distributedStatementId,\r\n CommandType=Properties.command,\r\n Result=ResultType,\r\n MbProcessed=(Properties.dataProcessedBytes/1000/1000),\r\n QueryText=Properties.queryText,\r\n QueryHash=Properties.queryHash,\r\n _ResourceId\r\n| extend elapsedTime_sec =(todatetime(EndTime) - StartTime)/1s\r\n| extend Approx_Cost=((MbProcessed)*5.0/100000.0)/100 \r\n| project-reorder StartTime,EndTime,elapsedTime_sec,Identity,RequestId,StatementId,CommandType,Result,MbProcessed,Approx_Cost\r\n",
"size": 2,
"showAnalytics": true,
"title": "Selected Query History",
"noDataMessage": "Please select a query to view run history",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{LogAnalyticsWorkspace}"
],
"visualization": "linechart",
"gridSettings": {
"formatters": [
{
"columnMatch": "StartTime",
"formatter": 6
},
{
"columnMatch": "EndTime",
"formatter": 6
},
{
"columnMatch": "elapsedTime_sec",
"formatter": 8,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "Result",
"formatter": 18,
"formatOptions": {
"thresholdsOptions": "icons",
"thresholdsGrid": [
{
"operator": "Default",
"thresholdValue": null,
"representation": "success",
"text": "{0}{1}"
}
]
}
},
{
"columnMatch": "MbProcessed",
"formatter": 3,
"formatOptions": {
"palette": "blue"
}
},
{
"columnMatch": "Approx_Cost",
"formatter": 8,
"formatOptions": {
"palette": "yellow"
},
"numberFormat": {
"unit": 0,
"options": {
"style": "decimal",
"minimumFractionDigits": 2,
"maximumFractionDigits": 6
}
}
},
{
"columnMatch": "QueryText",
"formatter": 7,
"formatOptions": {
"linkTarget": "CellDetails",
"linkIsContextBlade": true,
"customColumnWidthSetting": "100ch"
}
},
{
"columnMatch": "BytesProcessed",
"formatter": 8,
"formatOptions": {
"palette": "red"
}
}
]
},
"chartSettings": {
"xAxis": "EndTime",
"yAxis": [
"elapsedTime_sec",
"MbProcessed"
],
"showDataPoints": true
}
},
"conditionalVisibility": {
"parameterName": "QueryHash",
"comparison": "isNotEqualTo",
"value": ""
},
"showPin": true,
"name": "SelectedQueryHistory"
}
]
},
"conditionalVisibility": {
"parameterName": "GroupSelection",
"comparison": "isEqualTo",
"value": "QueryPerformance"
},
"name": "QueryPerformance"
}
],
"fallbackResourceIds": [
"Azure Monitor"
],
"$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}