Azure Arc Connected K8s Billing usage workbook error - 'union' operator: Failed to resolve table expression named 'ContainerLogV2'
Using ‘Container Insights’ for Kubernetes can get expensive, I recently saw a customer creating 19TB of logs a month from logging container insights from an AKS cluster.
You may want to check what logs you are capturing as suggested through Microsoft guides Monitoring cost for Container insights - Azure Monitor | Microsoft Docs which suggests one of the first steps to check billing usage.
As of writing this if you enable container insights and try to view Data Usage you get an error. ‘union' operator: Failed to resolve table expression named 'ContainerLogV2’. ContainerLogV2 is in preview or if you are just aren’t ready to deploy that, here is how to fix the report manually. I also have included the Full report at the end.
However, depending on when you deploy this and how you deployed insight metrics you may hit this error.
As of writing this, ContainerLogV2 is in preview and you can follow this guide to deploy it. Configure the ContainerLogV2 schema (preview) for Container Insights - Azure Monitor | Microsoft Docs. If you want to gain some quick insight into your usage you can edit the workbook. We just need to get through the maze of edits to get to the underlying query.
Select ‘Azure Arc’ services
select ‘Kubernetes clusters’ under infrastructure
select your K8s cluster
select ‘Workbooks’ under Monitoring
select ‘Container Insights Usage’ workbook
you can either edit this one or ‘Save As’ and create a new name. I will create a copy.
Open your chosen workbook
you can now edit the group item.
by Namespace
Editing the Namespace selection Dropdown parameter query
Hopefully, you get the gist of the process. There are a few more queries to edit.
And
This will now work for Data Usage. This is likely a temporary solution until ContainerLogV2 comes out of preview. Configure the ContainerLogV2 schema (preview) for Container Insights - Azure Monitor | Microsoft Docs
Gallery Template code
{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"crossComponentResources": [
"{resource}"
],
"parameters": [
{
"id": "670aac26-0ffe-4f29-81c2-d48911bc64b6",
"version": "KqlParameterItem/1.0",
"name": "timeRange",
"label": "Time Range",
"type": 4,
"description": "Select time-range for data selection",
"isRequired": true,
"value": {
"durationMs": 21600000
},
"typeSettings": {
"selectableValues": [
{
"durationMs": 300000
},
{
"durationMs": 900000
},
{
"durationMs": 3600000
},
{
"durationMs": 14400000
},
{
"durationMs": 43200000
},
{
"durationMs": 86400000
},
{
"durationMs": 172800000
},
{
"durationMs": 259200000
},
{
"durationMs": 604800000
},
{
"durationMs": 1209600000
},
{
"durationMs": 2419200000
},
{
"durationMs": 2592000000
},
{
"durationMs": 5184000000
},
{
"durationMs": 7776000000
}
],
"allowCustom": true
}
},
{
"id": "bfc96857-81df-4f0d-b958-81f96d28ddeb",
"version": "KqlParameterItem/1.0",
"name": "resource",
"type": 5,
"isRequired": true,
"isHiddenWhenLocked": true,
"typeSettings": {
"additionalResourceOptions": [
"value::1"
],
"showDefault": false
},
"timeContext": {
"durationMs": 21600000
},
"timeContextFromParameter": "timeRange",
"defaultValue": "value::1"
},
{
"id": "8d48ec94-fde6-487c-98bf-f1295f5d8b81",
"version": "KqlParameterItem/1.0",
"name": "resourceType",
"type": 7,
"description": "Resource type of resource",
"isRequired": true,
"query": "{\"version\":\"1.0.0\",\"content\":\"\\\"{resource:resourcetype}\\\"\",\"transformers\":null}",
"isHiddenWhenLocked": true,
"typeSettings": {
"additionalResourceOptions": [
"value::1"
],
"showDefault": false
},
"timeContext": {
"durationMs": 21600000
},
"timeContextFromParameter": "timeRange",
"defaultValue": "value::1",
"queryType": 8
},
{
"id": "1b826776-ab99-45ab-86db-cced05e8b36d",
"version": "KqlParameterItem/1.0",
"name": "clusterId",
"type": 1,
"description": "Used to identify the cluster name when the cluster type is AKS Engine",
"isHiddenWhenLocked": true,
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "timeRange"
},
{
"id": "67227c35-eab8-4518-9212-1c3c3d564b20",
"version": "KqlParameterItem/1.0",
"name": "masterNodeExists",
"type": 1,
"query": "let MissingTable = view () { print isMissing=1 };\r\nlet masterNodeExists = toscalar(\r\nunion isfuzzy=true MissingTable, (\r\nAzureDiagnostics \r\n| getschema \r\n| summarize c=count() \r\n| project isMissing=iff(c > 0, 0, 1)\r\n) \r\n| top 1 by isMissing asc\r\n);\r\nprint(iif(masterNodeExists == 0, 'yes', 'no'))\r\n",
"crossComponentResources": [
"{resource}"
],
"isHiddenWhenLocked": true,
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "timeRange",
"queryType": 0,
"resourceType": "{resourceType}"
}
],
"style": "pills",
"queryType": 0,
"resourceType": "{resourceType}"
},
"name": "pills"
},
{
"type": 1,
"content": {
"json": "Please note that the Container Insights Usage workbook for AKS Engine clusters shows your billable data usage for your cluster's entire workspace ({resource:name}), and not just the cluster itself ({clusterId}). ",
"style": "info"
},
"conditionalVisibility": {
"parameterName": "resourceType",
"comparison": "isEqualTo",
"value": "microsoft.operationalinsights/workspaces"
},
"name": "aks-engine-billable-data-shown-applies-to-entire-workspace-not-just-the-cluster-info-message",
"styleSettings": {
"showBorder": true
}
},
{
"type": 11,
"content": {
"version": "LinkItem/1.0",
"style": "tabs",
"links": [
{
"id": "3b7d39f2-38c5-4586-a155-71e28e333020",
"cellValue": "selectedTab",
"linkTarget": "parameter",
"linkLabel": "Overview",
"subTarget": "overview",
"style": "link"
},
{
"id": "7163b764-7ab2-48b3-b417-41af3eea7ef0",
"cellValue": "selectedTab",
"linkTarget": "parameter",
"linkLabel": "By Table",
"subTarget": "table",
"style": "link"
},
{
"id": "960bbeea-357f-4c07-bdd9-6f3f123d56fd",
"cellValue": "selectedTab",
"linkTarget": "parameter",
"linkLabel": "By Namespace",
"subTarget": "namespace",
"style": "link"
},
{
"id": "7fbcc5bd-62d6-4aeb-b55e-7b1fab65716a",
"cellValue": "selectedTab",
"linkTarget": "parameter",
"linkLabel": "By Log Source",
"subTarget": "logSource",
"style": "link"
},
{
"id": "3434a63b-f3d5-4c11-9620-1e731983041c",
"cellValue": "selectedTab",
"linkTarget": "parameter",
"linkLabel": "By Diagnostic Master Node ",
"subTarget": "diagnosticMasterNode",
"style": "link"
}
]
},
"name": "tabs"
},
{
"type": 1,
"content": {
"json": "Due to querying limitation on Basic Logs, if you are using Basic Logs, this workbook provides partial data on your container log usage.",
"style": "info"
},
"conditionalVisibility": {
"parameterName": "selectedTab",
"comparison": "isEqualTo",
"value": "overview"
},
"name": "basic-logs-info-text"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"title": "Container Insights Billing Usage",
"items": [
{
"type": 1,
"content": {
"json": "<br/>\r\nThis workbook provides you a summary and source for billable data collected by [Container Insights solution.](https://docs.microsoft.com/en-us/azure/azure-monitor/insights/container-insights-overview)\r\n\r\nThe best way to understand how Container Insights ingests data in Log Analytics workspace is from this [article.](https://medium.com/microsoftazure/azure-monitor-for-containers-optimizing-data-collection-settings-for-cost-ce6f848aca32)\r\n\r\nIn this workbook you can:\r\n\r\n* View billable data ingested by **solution**.\r\n* View billable data ingested by **Container logs (application logs)**\r\n* View billable container logs data ingested segregated by **Kubernetes namespace**\r\n* View billable container logs data ingested segregated by **Cluster name**\r\n* View billable container log data ingested by **logsource entry**\r\n* View billable diagnostic data ingested by **diagnostic master node logs**\r\n\r\nYou can fine tune and control logging by turning off logging on the above mentioned vectors. [Learn how to fine-tune logging](https://docs.microsoft.com/en-us/azure/azure-monitor/insights/container-insights-agent-config)\r\n\r\nYou can control your master node's logs by updating diagnostic settings. [Learn how to update diagnostic settings](https://docs.microsoft.com/en-us/azure/aks/view-master-logs)"
},
"name": "workbooks-explanation-text"
},
{
"type": 1,
"content": {
"json": "`Master node logs` are not enabled. [Learn how to enable](https://docs.microsoft.com/en-us/azure/aks/view-master-logs)"
},
"conditionalVisibility": {
"parameterName": "masterNodeExists",
"comparison": "isEqualTo",
"value": "no"
},
"name": "master-node-logs-are-not-enabled-msg"
}
]
},
"conditionalVisibility": {
"parameterName": "selectedTab",
"comparison": "isEqualTo",
"value": "overview"
},
"name": "workbook-explanation"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "union withsource = SourceTable AzureDiagnostics, AzureActivity, AzureMetrics, ContainerLog, Perf, KubePodInventory, ContainerInventory, InsightsMetrics, KubeEvents, KubeServices, KubeNodeInventory, ContainerNodeInventory, KubeMonAgentEvents, ContainerServiceLog, Heartbeat, KubeHealth, ContainerImageInventory\r\n| where _IsBillable == true\r\n| project _BilledSize, TimeGenerated, SourceTable\r\n| summarize BillableDataBytes = sum(_BilledSize) by bin(TimeGenerated, {timeRange:grain}), SourceTable\r\n| render piechart\r\n\r\n\r\n",
"size": 3,
"showAnnotations": true,
"showAnalytics": true,
"title": "Billable Data from Container Insights",
"timeContextFromParameter": "timeRange",
"queryType": 0,
"resourceType": "{resourceType}",
"crossComponentResources": [
"{resource}"
],
"chartSettings": {
"xAxis": "TimeGenerated",
"createOtherGroup": 100,
"seriesLabelSettings": [
{
"seriesName": "LogManagement",
"label": "LogManagementSolution(GB)"
},
{
"seriesName": "ContainerInsights",
"label": "ContainerInsightsSolution(GB)"
}
],
"ySettings": {
"numberFormatSettings": {
"unit": 2,
"options": {
"style": "decimal"
}
}
}
}
},
"customWidth": "50",
"showPin": true,
"name": "billable-data-from-ci"
}
]
},
"conditionalVisibility": {
"parameterName": "selectedTab",
"comparison": "isEqualTo",
"value": "table"
},
"name": "by-datatable-tab"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "KubePodInventory\r\n| distinct ContainerID, Namespace\r\n| join kind=innerunique (\r\nContainerLog\r\n| where _IsBillable == true\r\n| summarize BillableDataBytes = sum(_BilledSize) by ContainerID\r\n) on ContainerID\r\n| union (\r\nKubePodInventory\r\n| distinct ContainerID, Namespace\r\n)\r\n| summarize Total=sum(BillableDataBytes) by Namespace\r\n| render piechart\r\n",
"size": 3,
"showAnalytics": true,
"title": "Billable Container Log Data Per Namespace",
"timeContextFromParameter": "timeRange",
"queryType": 0,
"resourceType": "{resourceType}",
"crossComponentResources": [
"{resource}"
],
"tileSettings": {
"showBorder": false,
"titleContent": {
"columnMatch": "Namespace",
"formatter": 1
},
"leftContent": {
"columnMatch": "Total",
"formatter": 12,
"formatOptions": {
"palette": "auto"
},
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
},
"graphSettings": {
"type": 0,
"topContent": {
"columnMatch": "Namespace",
"formatter": 1
},
"centerContent": {
"columnMatch": "Total",
"formatter": 1,
"numberFormat": {
"unit": 17,
"options": {
"maximumSignificantDigits": 3,
"maximumFractionDigits": 2
}
}
}
},
"chartSettings": {
"createOtherGroup": 100,
"ySettings": {
"numberFormatSettings": {
"unit": 2,
"options": {
"style": "decimal"
}
}
}
}
},
"name": "billable-data-per-namespace"
}
]
},
"conditionalVisibility": {
"parameterName": "selectedTab",
"comparison": "isEqualTo",
"value": "namespace"
},
"name": "by-namespace-tab"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"crossComponentResources": [
"{resource}"
],
"parameters": [
{
"id": "d5701caa-0486-4e6f-adad-6fa5b8496a7d",
"version": "KqlParameterItem/1.0",
"name": "namespace",
"label": "Namespace",
"type": 2,
"description": "Filter data by namespace",
"isRequired": true,
"query": "KubePodInventory\r\n| distinct ContainerID, Namespace\r\n| join kind=innerunique (\r\nContainerLog\r\n| project ContainerID\r\n) on ContainerID\r\n| union (\r\nKubePodInventory\r\n| distinct ContainerID, Namespace\r\n)\r\n| distinct Namespace\r\n| project value = Namespace, label = Namespace, selected = false\r\n| sort by label asc",
"crossComponentResources": [
"{resource}"
],
"typeSettings": {
"additionalResourceOptions": [
"value::1"
],
"showDefault": false
},
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "timeRange",
"defaultValue": "value::1",
"queryType": 0,
"resourceType": "{resourceType}"
}
],
"style": "pills",
"queryType": 0,
"resourceType": "{resourceType}"
},
"name": "namespace-pill"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "KubePodInventory\r\n| where Namespace == '{namespace}'\r\n| distinct ContainerID, Namespace\r\n| join hint.strategy=shuffle (\r\nContainerLog\r\n| where _IsBillable == true\r\n| summarize BillableDataBytes = sum(_BilledSize) by LogEntrySource, ContainerID\r\n) on ContainerID\r\n| union (\r\nKubePodInventory\r\n| where Namespace == '{namespace}'\r\n| distinct ContainerID, Namespace\r\n)\r\n| extend sourceNamespace = strcat(LogEntrySource, \"/\", Namespace)\r\n| summarize Total=sum(BillableDataBytes) by sourceNamespace\r\n| render piechart",
"size": 0,
"showAnnotations": true,
"showAnalytics": true,
"title": "Billable Data Per Log-Source Type for Namespace Selected",
"timeContextFromParameter": "timeRange",
"queryType": 0,
"resourceType": "{resourceType}",
"crossComponentResources": [
"{resource}"
],
"chartSettings": {
"createOtherGroup": 100,
"ySettings": {
"numberFormatSettings": {
"unit": 2,
"options": {
"style": "decimal"
}
}
}
}
},
"customWidth": "35",
"showPin": true,
"name": "billable-data-per-log-source",
"styleSettings": {
"showBorder": true
}
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "KubePodInventory\r\n| where Namespace == '{namespace}'\r\n| distinct ContainerID, Namespace\r\n| project ContainerID\r\n| join hint.strategy=shuffle ( \r\nContainerLog\r\n| project _BilledSize, ContainerID, TimeGenerated, LogEntrySource\r\n) on ContainerID\r\n| union (\r\nKubePodInventory\r\n| where Namespace == '{namespace}'\r\n| distinct ContainerID, Namespace\r\n| project ContainerID\r\n)\r\n| summarize ContainerLogData = sum(_BilledSize) by bin(TimeGenerated, {timeRange:grain}), LogEntrySource\r\n| render linechart\r\n\r\n",
"size": 0,
"showAnnotations": true,
"showAnalytics": true,
"title": "Billable Container Log Data for Namespace Selected",
"timeContextFromParameter": "timeRange",
"queryType": 0,
"resourceType": "{resourceType}",
"crossComponentResources": [
"{resource}"
],
"chartSettings": {
"xSettings": {},
"ySettings": {
"numberFormatSettings": {
"unit": 2,
"options": {
"style": "decimal"
}
}
}
}
},
"customWidth": "65",
"conditionalVisibility": {
"parameterName": "namespace:label",
"comparison": "isNotEqualTo",
"value": "<unset>"
},
"showPin": true,
"name": "billable-container-log-data-for-namespace-selected",
"styleSettings": {
"showBorder": true
}
}
]
},
"conditionalVisibility": {
"parameterName": "selectedTab",
"comparison": "isEqualTo",
"value": "logSource"
},
"name": "by-log-source-tab"
},
{
"type": 12,
"content": {
"version": "NotebookGroup/1.0",
"groupType": "editable",
"items": [
{
"type": 1,
"content": {
"json": "`Master node logs` are not enabled. [Learn how to enable](https://docs.microsoft.com/en-us/azure/aks/view-master-logs)"
},
"conditionalVisibility": {
"parameterName": "masterNodeExists",
"comparison": "isEqualTo",
"value": "no"
},
"name": "master-node-logs-are-not-enabled-msg-under-masternode-tab"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "AzureDiagnostics\r\n| summarize Total=sum(_BilledSize) by bin(TimeGenerated, {timeRange:grain}), Category\r\n| render barchart\r\n\r\n\r\n",
"size": 0,
"showAnnotations": true,
"showAnalytics": true,
"title": "Billable Diagnostic Master Node Log Data",
"timeContextFromParameter": "timeRange",
"queryType": 0,
"resourceType": "{resourceType}",
"crossComponentResources": [
"{resource}"
],
"chartSettings": {
"ySettings": {
"unit": 2,
"min": null,
"max": null
}
}
},
"customWidth": "75",
"conditionalVisibility": {
"parameterName": "masterNodeExists",
"comparison": "isEqualTo",
"value": "yes"
},
"showPin": true,
"name": "billable-data-from-diagnostic-master-node-logs"
}
]
},
"conditionalVisibility": {
"parameterName": "selectedTab",
"comparison": "isEqualTo",
"value": "diagnosticMasterNode"
},
"name": "by-diagnostic-master-node-tab"
}
],
"fallbackResourceIds": [
"/subscriptions/357e7522-d1ad-433c-9545-7d8b8d72d61a/resourceGroups/ArcResources/providers/Microsoft.Kubernetes/connectedClusters/sv5-k8sdemo"
],
"fromTemplateId": "community-Workbooks/AKS/Billing Usage Divided",
"$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}