opt-wb-01

{
  "version": "Notebook/1.0",
  "items": [
    {
      "type": 1,
      "content": {
        "json": "# Azure Virtual Machine Sku Optimisation #\r\n\r\n## Instructions for Use\r\n- Final panel does not show all fields - export to CSV to get everything including Sku recommendations\r\n\r\n## Caveats ##\r\n- No spot VM pricing\r\n- AUD only\r\n- No dedicated host\r\n- No ms|ls pricing or recommendations"
      },
      "name": "text - 4"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "{\"version\":\"CustomEndpoint/1.0\",\"data\":null,\"headers\":[],\"method\":\"GET\",\"url\":\"https://cmprstaw01.blob.core.windows.net/pricelist/opt.json?sp=r&st=2024-07-25T02:25:20Z&se=2024-12-19T09:25:20Z&spr=https&sv=2022-11-02&sr=b&sig=voqewNn90AYnzmK%2BzLuMul4njCdVBX%2BTKIBxlc5JH7s%3D\",\"contentType\":\"text/plain\",\"urlParams\":[],\"transformers\":[{\"type\":\"jsonpath\",\"settings\":{\"tablePath\":\"$.*\",\"columns\":[]}}]}",
        "size": 0,
        "showRefreshButton": true,
        "queryType": 10,
        "gridSettings": {
          "rowLimit": 1000
        }
      },
      "name": "opt"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "resources \r\n| where type == 'microsoft.compute/virtualmachines' \r\n| extend currentSku = tostring(properties.hardwareProfile.vmSize) \r\n| extend os = tostring(properties.storageProfile.osDisk.osType) \r\n| extend fq_sku = strcat(currentSku,\"_\",os)\r\n| summarize count() by currentSku,os,fq_sku | project currentSku,os,quantity=count_,fq_sku",
        "size": 0,
        "queryType": 1,
        "resourceType": "microsoft.resources/tenants",
        "crossComponentResources": [
          "value::tenant"
        ]
      },
      "name": "graph"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "{\"version\":\"Merge/1.0\",\"merges\":[{\"id\":\"d415c5f5-c63b-4e72-ad12-74f7ef950028\",\"mergeType\":\"innerunique\",\"leftTable\":\"graph\",\"rightTable\":\"opt\",\"leftColumn\":\"fq_sku\",\"rightColumn\":\"current\"}],\"projectRename\":[{\"originalName\":\"[graph].currentSku\",\"mergedName\":\"currentSku\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950028\"},{\"originalName\":\"[graph].os\",\"mergedName\":\"os\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950028\"},{\"originalName\":\"[graph].quantity\",\"mergedName\":\"quantity\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950028\"},{\"originalName\":\"[graph].fq_sku\",\"mergedName\":\"fq_sku\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950028\"},{\"originalName\":\"[opt].current\",\"mergedName\":\"current\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950028\"},{\"originalName\":\"[opt].target\",\"mergedName\":\"target\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950028\"},{\"originalName\":\"[opt].target_raw\",\"mergedName\":\"target_raw\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950028\"},{\"originalName\":\"[opt].currentRetail\",\"mergedName\":\"currentRetail\",\"fromId\":\"unknown\"},{\"originalName\":\"[opt].targetRetails\",\"mergedName\":\"targetRetails\",\"fromId\":\"unknown\"}]}",
        "size": 0,
        "queryType": 7
      },
      "name": "query - 3"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "{\"version\":\"Merge/1.0\",\"merges\":[{\"id\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\",\"mergeType\":\"table\",\"leftTable\":\"query - 3\"}],\"projectRename\":[{\"originalName\":\"[query - 3].currentSku\",\"mergedName\":\"currentSku\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[query - 3].os\",\"mergedName\":\"os\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[query - 3].quantity\",\"mergedName\":\"quantity\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[query - 3].fq_sku\",\"mergedName\":\"fq_sku\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[query - 3].current\",\"mergedName\":\"current\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[query - 3].target\",\"mergedName\":\"target\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[query - 3].target_raw\",\"mergedName\":\"target_raw\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[query - 3].currentRetail\",\"mergedName\":\"currentRetail\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[query - 3].targetRetails\",\"mergedName\":\"targetRetails\",\"fromId\":\"d415c5f5-c63b-4e72-ad12-74f7ef950030\"},{\"originalName\":\"[Added column]\",\"mergedName\":\"currentMonthly\",\"fromId\":null,\"isNewItem\":true,\"newItemData\":[{\"criteriaContext\":{\"operator\":\"Default\",\"rightValType\":\"column\",\"resultValType\":\"expression\",\"resultVal\":\"[\\\"quantity\\\"]*[\\\"currentRetail\\\"]*730\"}}]},{\"originalName\":\"[Added column]\",\"mergedName\":\"targetMonthly\",\"fromId\":null,\"isNewItem\":true,\"newItemData\":[{\"criteriaContext\":{\"operator\":\"Default\",\"rightValType\":\"column\",\"resultValType\":\"expression\",\"resultVal\":\"[\\\"quantity\\\"]*[\\\"targetRetails\\\"]*730\"}}]},{\"originalName\":\"[Added column]\",\"mergedName\":\"difference\",\"fromId\":null,\"isNewItem\":true,\"newItemData\":[{\"criteriaContext\":{\"operator\":\"Default\",\"rightValType\":\"column\",\"resultValType\":\"expression\",\"resultVal\":\"[\\\"currentMonthly\\\"]-[\\\"targetMonthly\\\"]\"}}]}]}",
        "size": 0,
        "showRefreshButton": true,
        "showExportToExcel": true,
        "queryType": 7,
        "visualization": "table",
        "gridSettings": {
          "formatters": [
            {
              "columnMatch": "fq_sku",
              "formatter": 5
            },
            {
              "columnMatch": "current",
              "formatter": 5
            },
            {
              "columnMatch": "target",
              "formatter": 5
            },
            {
              "columnMatch": "currentRetail",
              "formatter": 0,
              "numberFormat": {
                "unit": 0,
                "options": {
                  "style": "decimal"
                }
              }
            },
            {
              "columnMatch": "targetRetails",
              "formatter": 0,
              "numberFormat": {
                "unit": 0,
                "options": {
                  "style": "decimal"
                }
              }
            },
            {
              "columnMatch": "currentMonthly",
              "formatter": 0,
              "numberFormat": {
                "unit": 0,
                "options": {
                  "style": "decimal"
                }
              }
            },
            {
              "columnMatch": "targetMonthly",
              "formatter": 0,
              "numberFormat": {
                "unit": 0,
                "options": {
                  "style": "decimal"
                }
              }
            },
            {
              "columnMatch": "difference",
              "formatter": 0,
              "numberFormat": {
                "unit": 0,
                "options": {
                  "style": "decimal"
                }
              }
            }
          ],
          "sortBy": [
            {
              "itemKey": "$gen_number_difference_11",
              "sortOrder": 2
            }
          ],
          "labelSettings": [
            {
              "columnId": "currentSku",
              "label": "Current Sku"
            },
            {
              "columnId": "os",
              "label": "Operating System"
            },
            {
              "columnId": "quantity",
              "label": "Count"
            },
            {
              "columnId": "target_raw",
              "label": "Target Sku"
            },
            {
              "columnId": "currentRetail",
              "label": "Current Unit Price"
            },
            {
              "columnId": "targetRetails",
              "label": "Target Unit Price"
            },
            {
              "columnId": "currentMonthly",
              "label": "Current Monthly Total"
            },
            {
              "columnId": "targetMonthly",
              "label": "Target Monthly Total"
            },
            {
              "columnId": "difference",
              "label": "Difference"
            }
          ]
        },
        "sortBy": [
          {
            "itemKey": "$gen_number_difference_11",
            "sortOrder": 2
          }
        ]
      },
      "name": "Opt_Output",
      "styleSettings": {
        "showBorder": true
      }
    }
  ],
  "fallbackResourceIds": [
    "Azure Monitor"
  ],
  "$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}