{ "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" }