{ "edges": [ { "to": "check-if-data-exists", "from": "format-report", "toPort": "0", "fromPort": "0" }, { "to": "merge-and-process-data", "from": "get-cost-data", "toPort": "0", "fromPort": "0" }, { "to": "export-to-excel", "from": "output-report", "toPort": "0", "fromPort": "0" }, { "to": "prepare-powerbi-data", "from": "output-report", "toPort": "0", "fromPort": "0" }, { "to": "respond-to-webhook", "from": "output-report", "toPort": "0", "fromPort": "0" }, { "to": "set-configuration", "from": "manual-trigger", "toPort": "0", "fromPort": "0" }, { "to": "query-azure-resources", "from": "set-configuration", "toPort": "0", "fromPort": "0" }, { "to": "get-cost-data", "from": "set-configuration", "toPort": "0", "fromPort": "0" }, { "to": "output-report", "from": "check-if-data-exists", "toPort": "0", "fromPort": "0" }, { "to": "no-data-found", "from": "check-if-data-exists", "toPort": "0", "fromPort": "1" }, { "to": "send-to-powerbi", "from": "prepare-powerbi-data", "toPort": "0", "fromPort": "0" }, { "to": "merge-and-process-data", "from": "query-azure-resources", "toPort": "0", "fromPort": "0" }, { "to": "format-report", "from": "merge-and-process-data", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "manual-trigger", "name": "Manual Trigger", "type": "manualTrigger", "params": {}, "position": [ 820, 680 ] }, { "id": "set-configuration", "name": "Set Configuration", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "subscription-id", "name": "subscriptionId", "type": "string", "value": "=YOUR_SUBSCRIPTION_ID" }, { "id": "tenant-id", "name": "tenantId", "type": "string", "value": "=YOUR_TENANT_ID" }, { "id": "billing-period", "name": "billingPeriod", "type": "string", "value": "={{ $now.format('yyyyMM') }}" }, { "id": "start-date", "name": "startDate", "type": "string", "value": "={{ $now.startOf('month').format('yyyy-MM-dd') }}" }, { "id": "end-date", "name": "endDate", "type": "string", "value": "={{ $now.endOf('day').format('yyyy-MM-dd') }}" } ] } }, "position": [ 1040, 680 ] }, { "id": "query-azure-resources", "name": "Query Azure Resources", "type": "http", "params": { "url": "=https://management.azure.com/subscriptions/{{ $json.subscriptionId }}/providers/Microsoft.ResourceGraph/resources?api-version=2021-03-01", "options": {}, "jsonBody": "={\n \"query\": \"Resources | project name, type, location, resourceGroup, tags, sku, properties, id\",\n \"subscriptions\": [\"{{ $json.subscriptionId }}\"]\n}", "sendBody": true, "specifyBody": "json", "authentication": "predefinedCredentialType", "nodeCredentialType": "oAuth2Api" }, "position": [ 1480, 680 ] }, { "id": "get-cost-data", "name": "Get Cost Data", "type": "http", "params": { "url": "=https://management.azure.com/subscriptions/{{ $('Set Configuration').item.json.subscriptionId }}/providers/Microsoft.CostManagement/query?api-version=2023-11-01", "options": {}, "jsonBody": "={\n \"type\": \"ActualCost\",\n \"timeframe\": \"Custom\",\n \"timePeriod\": {\n \"from\": \"{{ $('Set Configuration').item.json.startDate }}\",\n \"to\": \"{{ $('Set Configuration').item.json.endDate }}\"\n },\n \"dataset\": {\n \"granularity\": \"Daily\",\n \"aggregation\": {\n \"totalCost\": {\n \"name\": \"Cost\",\n \"function\": \"Sum\"\n },\n \"totalCostUSD\": {\n \"name\": \"CostUSD\",\n \"function\": \"Sum\"\n }\n },\n \"grouping\": [\n {\n \"type\": \"Dimension\",\n \"name\": \"ResourceId\"\n },\n {\n \"type\": \"Dimension\",\n \"name\": \"ServiceName\"\n },\n {\n \"type\": \"Dimension\",\n \"name\": \"ResourceType\"\n }\n ]\n }\n}", "sendBody": true, "specifyBody": "json", "authentication": "predefinedCredentialType", "nodeCredentialType": "oAuth2Api" }, "position": [ 1480, 880 ] }, { "id": "merge-and-process-data", "name": "Merge and Process Data", "type": "code", "params": { "jsCode": "const resourcesData = $input.first().json.data;\nconst resources = resourcesData.rows.map(row => {\n const columns = resourcesData.columns.map(col => col.name);\n let resource = {};\n columns.forEach((col, idx) => {\n resource[col] = row[idx];\n });\n return resource;\n});\n\nconst costData = $input.last().json.properties;\nconst costs = costData.rows.map(row => {\n const columns = costData.columns.map(col => col.name);\n let cost = {};\n columns.forEach((col, idx) => {\n cost[col] = row[idx];\n });\n return cost;\n});\n\nconst merged = resources.map(resource => {\n const resourceCosts = costs.filter(cost => \n cost.ResourceId && cost.ResourceId.toLowerCase().includes(resource.name.toLowerCase())\n );\n \n const totalCost = resourceCosts.reduce((sum, cost) => sum + (cost.Cost || 0), 0);\n \n return {\n resourceName: resource.name,\n resourceType: resource.type,\n resourceGroup: resource.resourceGroup,\n location: resource.location,\n sku: resource.sku,\n tags: resource.tags,\n cost: totalCost.toFixed(2),\n costDetails: resourceCosts\n };\n});\n\nmerged.sort((a, b) => parseFloat(b.cost) - parseFloat(a.cost));\n\nconst totalCost = merged.reduce((sum, r) => sum + parseFloat(r.cost), 0);\nconst resourceCount = merged.length;\nconst topCostResources = merged.slice(0, 10);\n\nconst costByType = {};\nmerged.forEach(r => {\n const type = r.resourceType;\n if (!costByType[type]) {\n costByType[type] = { count: 0, cost: 0 };\n }\n costByType[type].count++;\n costByType[type].cost += parseFloat(r.cost);\n});\n\nconst summary = {\n totalCost: totalCost.toFixed(2),\n resourceCount: resourceCount,\n period: `${$('Set Configuration').item.json.startDate} to ${$('Set Configuration').item.json.endDate}`,\n topCostResources: topCostResources.map(r => ({\n name: r.resourceName,\n type: r.resourceType,\n cost: r.cost\n })),\n costByType: Object.entries(costByType)\n .map(([type, data]) => ({\n type,\n count: data.count,\n cost: data.cost.toFixed(2)\n }))\n .sort((a, b) => parseFloat(b.cost) - parseFloat(a.cost))\n};\n\nreturn [{\n json: {\n summary,\n allResources: merged\n }\n}];" }, "position": [ 1920, 780 ] }, { "id": "format-report", "name": "Format Report", "type": "code", "params": { "jsCode": "const data = $input.first().json;\nconst summary = data.summary;\n\nlet report = `# Azure Resource & Cost Report\\n\\n`;\nreport += `**Period:** ${summary.period}\\n`;\nreport += `**Total Cost:** $${summary.totalCost}\\n`;\nreport += `**Total Resources:** ${summary.resourceCount}\\n\\n`;\nreport += `## Top 10 Most Expensive Resources\\n\\n`;\nsummary.topCostResources.forEach((r, idx) => {\n report += `${idx + 1}. **${r.name}** (${r.type}) - $${r.cost}\\n`;\n});\nreport += `\\n## Cost by Resource Type\\n\\n`;\nsummary.costByType.slice(0, 10).forEach((t, idx) => {\n report += `${idx + 1}. **${t.type}** - ${t.count} resources - $${t.cost}\\n`;\n});\nreport += `\\n---\\n_Generated by n8n on ${new Date().toLocaleString()}_`;\n\nlet htmlReport = `\n\n\n \n\n\n

Azure Resource & Cost Report

Period: ${summary.period}

Total Cost: $${summary.totalCost}

Total Resources: ${summary.resourceCount}

Top 10 Most Expensive Resources

\n \n \n`;\nsummary.topCostResources.forEach((r, idx) => {\n htmlReport += ` \\n`;\n});\nhtmlReport += `\n

#	Resource Name	Type	Cost
${idx + 1}	${r.name}	${r.type}	$${r.cost}

Cost by Resource Type

\n \n \n`;\nsummary.costByType.slice(0, 10).forEach((t, idx) => {\n htmlReport += ` \\n`;\n});\nhtmlReport += `\n

#	Resource Type	Count	Total Cost
${idx + 1}	${t.type}	${t.count}	$${t.cost}

Generated by n8n on ${new Date().toLocaleString()}

\n\n\n`;\n\nreturn [{\n json: {\n textReport: report,\n htmlReport: htmlReport,\n summary: summary,\n allData: data\n }\n}];" }, "position": [ 2160, 780 ] }, { "id": "check-if-data-exists", "name": "Check If Data Exists", "type": "if", "params": { "conditions": { "string": [ { "value1": "={{ $json.summary.totalCost }}", "operation": "isNotEmpty" } ] } }, "position": [ 2400, 780 ] }, { "id": "output-report", "name": "Output Report", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "output", "name": "report", "type": "string", "value": "={{ $json.textReport }}" }, { "id": "html", "name": "htmlReport", "type": "string", "value": "={{ $json.htmlReport }}" }, { "id": "total-cost", "name": "totalCost", "type": "string", "value": "={{ $json.summary.totalCost }}" }, { "id": "resource-count", "name": "resourceCount", "type": "number", "value": "={{ $json.summary.resourceCount }}" } ] } }, "position": [ 2640, 680 ] }, { "id": "export-to-excel", "name": "Export to Excel", "type": "integration", "params": { "json": "={{ $json.allData.allResources }}", "mode": "jsonToSpreadsheet", "options": { "fileName": "azure-cost-report-{{ $now.format('yyyy-MM-dd') }}.xlsx", "headerRow": true }, "service": "spreadsheetfile", "operation": "toJson", "__originalType": "n8n-nodes-base.spreadsheetFile" }, "position": [ 2880, 580 ] }, { "id": "prepare-powerbi-data", "name": "Prepare Power BI Data", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "summary", "name": "summary", "type": "object", "value": "={{ $json.allData.summary }}" }, { "id": "resources", "name": "resources", "type": "array", "value": "={{ $json.allData.allResources }}" }, { "id": "timestamp", "name": "timestamp", "type": "string", "value": "={{ $now.toISO() }}" }, { "id": "reportType", "name": "reportType", "type": "string", "value": "azure-cost-analysis" } ] } }, "position": [ 2880, 720 ] }, { "id": "send-to-powerbi", "name": "Send to Power BI", "type": "http", "params": { "url": "https://api.powerbi.com/beta/YOUR_WORKSPACE/datasets/YOUR_DATASET/rows?key=YOUR_TOKEN_HERE", "method": "POST", "options": {}, "jsonBody": "={{ $json }}", "sendBody": true, "specifyBody": "json" }, "position": [ 3120, 720 ] }, { "id": "respond-to-webhook", "name": "Respond to Webhook", "type": "passthrough", "params": { "mode": "noOp", "options": { "responseCode": 200, "responseHeaders": { "entries": [ { "name": "Content-Type", "value": "application/json" }, { "name": "Access-Control-Allow-Origin", "value": "*" } ] } }, "respondWith": "json", "responseBody": "={{ { \"status\": \"success\", \"data\": { \"summary\": $json.summary, \"totalCost\": $json.totalCost, \"resourceCount\": $json.resourceCount, \"report\": $json.report }, \"timestamp\": $now.toISO() } }}" }, "position": [ 2880, 860 ] }, { "id": "no-data-found", "name": "No Data Found", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "error", "name": "error", "type": "string", "value": "No cost data found for the specified period" } ] } }, "position": [ 2640, 880 ] } ] }

Monitor Azure subscription resources with cost and usage tracking and reports