{ "edges": [ { "to": "export-excel", "from": "output-report", "toPort": "0", "fromPort": "0" }, { "to": "respond-webhook", "from": "output-report", "toPort": "0", "fromPort": "0" }, { "to": "config-settings", "from": "trigger-manual", "toPort": "0", "fromPort": "0" }, { "to": "check-has-data", "from": "generate-report", "toPort": "0", "fromPort": "0" }, { "to": "query-all-data", "from": "config-settings", "toPort": "0", "fromPort": "0" }, { "to": "output-report", "from": "check-has-data", "toPort": "0", "fromPort": "0" }, { "to": "no-data-found", "from": "check-has-data", "toPort": "0", "fromPort": "1" }, { "to": "generate-report", "from": "correlate-data", "toPort": "0", "fromPort": "0" }, { "to": "correlate-data", "from": "query-all-data", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "trigger-manual", "name": "Manual Trigger", "type": "manualTrigger", "params": {}, "position": [ 780, 460 ] }, { "id": "config-settings", "name": "Set Configuration", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "appInsightsAppId", "name": "appInsightsAppId", "type": "string", "value": "YOUR_APP_INSIGHTS_APP_ID" }, { "id": "tenantId", "name": "tenantId", "type": "string", "value": "YOUR_TENANT_ID" }, { "id": "clientId", "name": "clientId", "type": "string", "value": "YOUR_CLIENT_ID" }, { "id": "clientSecret", "name": "clientSecret", "type": "string", "value": "YOUR_CLIENT_SECRET" }, { "id": "timeRange", "name": "timeRange", "type": "string", "value": "24h" }, { "id": "includeSuccessful", "name": "includeSuccessful", "type": "boolean", "value": false } ] } }, "position": [ 1000, 460 ] }, { "id": "query-all-data", "name": "Query Application Insights", "type": "code", "params": { "jsCode": "const config = $input.item.json;\nconst appId = config.appInsightsAppId;\nconst tenantId = config.tenantId;\nconst clientId = config.clientId;\nconst clientSecret = config.clientSecret;\nconst timeRange = config.timeRange;\nconst includeSuccessful = config.includeSuccessful;\n\n// Get OAuth2 token\nconst tokenResponse = await fetch(\n `https://login.microsoftonline.com/${tenantId}/oauth2/v2.0/token`,\n {\n method: 'POST',\n headers: { 'Content-Type': 'application/x-www-form-urlencoded' },\n body: new URLSearchParams({\n client_id: clientId,\n client_secret: clientSecret,\n scope: 'https://api.applicationinsights.io/.default',\n grant_type: 'client_credentials'\n })\n }\n);\n\nconst tokenData = await tokenResponse.json();\nconst accessToken = tokenData.access_token;\n\nif (!accessToken) {\n throw new Error('Failed to obtain access token: ' + JSON.stringify(tokenData));\n}\n\nconst headers = {\n 'Authorization': `Bearer ${accessToken}`,\n 'Content-Type': 'application/json'\n};\n\nconst baseUrl = `https://api.applicationinsights.io/v1/apps/${appId}/query`;\n\n// Query 1: APIM Requests\nconst apimQuery = `requests\n| where timestamp > ago(${timeRange})\n${includeSuccessful ? '' : '| where resultCode >= 400'}\n| extend operationId = tostring(operation_Id)\n| extend apimServiceName = tostring(customDimensions['apim-service-name'])\n| extend apimOperationName = tostring(customDimensions['apim-operation-name'])\n| extend apimSubscriptionId = tostring(customDimensions['apim-subscription-id'])\n| project timestamp, name, url, resultCode, duration, operationId, apimServiceName, apimOperationName, apimSubscriptionId, customDimensions, client_Type, client_City, client_CountryOrRegion\n| order by timestamp desc\n| take 500`;\n\nconst apimResponse = await fetch(baseUrl, {\n method: 'POST',\n headers,\n body: JSON.stringify({ query: apimQuery })\n});\nconst apimData = await apimResponse.json();\n\n// Query 2: Service Bus Traces \nconst sbQuery = `traces\n| where timestamp > ago(${timeRange})\n| where message contains 'ServiceBus' or customDimensions has 'MessageId'\n| extend operationId = tostring(operation_Id)\n| extend messageId = tostring(customDimensions['MessageId'])\n| extend entityName = tostring(customDimensions['EntityName'])\n| extend endpoint = tostring(customDimensions['Endpoint'])\n| project timestamp, message, severityLevel, operationId, messageId, entityName, endpoint, customDimensions\n| order by timestamp desc\n| take 500`;\n\nconst sbResponse = await fetch(baseUrl, {\n method: 'POST',\n headers,\n body: JSON.stringify({ query: sbQuery })\n});\nconst sbData = await sbResponse.json();\n\n// Query 3: Exceptions\nconst exceptionsQuery = `exceptions\n| where timestamp > ago(${timeRange})\n| extend operationId = tostring(operation_Id)\n| project timestamp, type, outerMessage, innermostMessage, outerMethod, innermostMethod, operationId, problemId, severityLevel, customDimensions, details\n| order by timestamp desc\n| take 500`;\n\nconst exceptionsResponse = await fetch(baseUrl, {\n method: 'POST',\n headers,\n body: JSON.stringify({ query: exceptionsQuery })\n});\nconst exceptionsData = await exceptionsResponse.json();\n\nreturn [{\n json: {\n apimData: apimData.tables?.[0] || {},\n sbData: sbData.tables?.[0] || {},\n exceptionsData: exceptionsData.tables?.[0] || {}\n }\n}];" }, "position": [ 1220, 460 ] }, { "id": "correlate-data", "name": "Correlate and Analyze Data", "type": "code", "params": { "jsCode": "const data = $input.first().json;\nconst apimData = data.apimData;\nconst sbData = data.sbData;\nconst exceptionsData = data.exceptionsData;\n\nfunction parseAppInsightsTable(table) {\n if (!table || !table.columns || !table.rows) return [];\n const columns = table.columns.map(c => c.name);\n return table.rows.map(row => {\n const obj = {};\n columns.forEach((col, idx) => { obj[col] = row[idx]; });\n return obj;\n });\n}\n\nconst apimRequests = parseAppInsightsTable(apimData);\nconst sbTraces = parseAppInsightsTable(sbData);\nconst exceptions = parseAppInsightsTable(exceptionsData);\n\nconst sbByOperationId = {};\nsbTraces.forEach(trace => {\n if (trace.operationId) {\n if (!sbByOperationId[trace.operationId]) sbByOperationId[trace.operationId] = [];\n sbByOperationId[trace.operationId].push(trace);\n }\n});\n\nconst exceptionsByOperationId = {};\nexceptions.forEach(ex => {\n if (ex.operationId) {\n if (!exceptionsByOperationId[ex.operationId]) exceptionsByOperationId[ex.operationId] = [];\n exceptionsByOperationId[ex.operationId].push(ex);\n }\n});\n\nconst correlatedData = apimRequests.map(request => ({\n timestamp: request.timestamp,\n apiName: request.name,\n url: request.url,\n resultCode: request.resultCode,\n duration: request.duration,\n operationId: request.operationId,\n apimServiceName: request.apimServiceName,\n apimOperationName: request.apimOperationName,\n apimSubscriptionId: request.apimSubscriptionId,\n clientType: request.client_Type,\n clientCity: request.client_City,\n clientCountry: request.client_CountryOrRegion,\n serviceBusTraces: sbByOperationId[request.operationId] || [],\n serviceBusMessageIds: (sbByOperationId[request.operationId] || []).map(t => t.messageId).filter(Boolean),\n exceptions: exceptionsByOperationId[request.operationId] || [],\n exceptionMessages: (exceptionsByOperationId[request.operationId] || []).map(e => e.outerMessage).filter(Boolean),\n hasException: !!exceptionsByOperationId[request.operationId],\n hasServiceBusTrace: !!sbByOperationId[request.operationId],\n isFailure: request.resultCode >= 400\n}));\n\nconst summary = {\n totalRequests: correlatedData.length,\n failedRequests: correlatedData.filter(r => r.isFailure).length,\n successfulRequests: correlatedData.filter(r => !r.isFailure).length,\n requestsWithExceptions: correlatedData.filter(r => r.hasException).length,\n requestsWithServiceBus: correlatedData.filter(r => r.hasServiceBusTrace).length,\n averageDuration: correlatedData.reduce((sum, r) => sum + (r.duration || 0), 0) / correlatedData.length,\n timeRange: $('Set Configuration').item.json.timeRange,\n analysisTimestamp: new Date().toISOString()\n};\n\nconst errorCounts = {};\ncorrelatedData.filter(r => r.isFailure).forEach(r => {\n const key = `${r.resultCode} - ${r.apiName}`;\n errorCounts[key] = (errorCounts[key] || 0) + 1;\n});\n\nconst topErrors = Object.entries(errorCounts)\n .map(([error, count]) => ({ error, count }))\n .sort((a, b) => b.count - a.count)\n .slice(0, 10);\n\nconst topSlowRequests = [...correlatedData]\n .sort((a, b) => (b.duration || 0) - (a.duration || 0))\n .slice(0, 10)\n .map(r => ({ apiName: r.apiName, duration: r.duration, resultCode: r.resultCode, timestamp: r.timestamp, operationId: r.operationId }));\n\nreturn [{ json: { summary, topErrors, topSlowRequests, correlatedData, rawData: { apimRequestCount: apimRequests.length, serviceBusTraceCount: sbTraces.length, exceptionCount: exceptions.length } } }];" }, "position": [ 1440, 460 ] }, { "id": "generate-report", "name": "Generate Report", "type": "code", "params": { "jsCode": "const data = $input.first().json;\nconst summary = data.summary;\nconst topErrors = data.topErrors;\nconst topSlowRequests = data.topSlowRequests;\n\nconst markdownReport = `# Azure API Failure Analysis Report\\n\\n**Analysis Period:** ${summary.timeRange}\\n**Generated:** ${new Date(summary.analysisTimestamp).toLocaleString()}\\n\\n---\\n\\n## Summary\\n\\n- **Total Requests:** ${summary.totalRequests}\\n- **Failed Requests:** ${summary.failedRequests} (${((summary.failedRequests/summary.totalRequests)*100).toFixed(1)}%)\\n- **Successful Requests:** ${summary.successfulRequests}\\n- **Requests with Exceptions:** ${summary.requestsWithExceptions}\\n- **Requests with Service Bus Traces:** ${summary.requestsWithServiceBus}\\n- **Average Duration:** ${summary.averageDuration.toFixed(2)}ms\\n\\n---\\n\\n## Top 10 Errors\\n\\n${topErrors.length > 0 ? topErrors.map((e, i) => `${i + 1}. **${e.error}** - ${e.count} occurrences`).join('\\\\n') : '_No errors found_'}\\n\\n---\\n\\n## Top 10 Slowest Requests\\n\\n${topSlowRequests.length > 0 ? topSlowRequests.map((r, i) => `${i + 1}. **${r.apiName}** - ${r.duration.toFixed(2)}ms - Status ${r.resultCode}`).join('\\\\n') : '_No slow requests identified_'}\\n\\n---\\n\\n_Report generated by n8n Azure App Insights Tracker_\\n`;\n\nconst htmlReport = `\\n\\n\\n Azure API Failure Analysis\\n \\n\\n\\n

\\n

Azure API Failure Analysis Report

\\n

Analysis Period: ${summary.timeRange} | Generated: ${new Date(summary.analysisTimestamp).toLocaleString()}

\\n

Total Requests

${summary.totalRequests}

\\n

Failed

${summary.failedRequests}

\\n

Success Rate

${((summary.successfulRequests/summary.totalRequests)*100).toFixed(1)}%

\\n

Avg Duration

${summary.averageDuration.toFixed(0)}ms

\\n

Top Errors

\\n ${topErrors.map(e => `

${e.error} - ${e.count} occurrences

`).join('')}\\n

Top Slowest Requests

\\n ${topSlowRequests.map(r => `

${r.apiName} - ${r.duration.toFixed(0)}ms - Status ${r.resultCode}

`).join('')}\\n

\\n\\n`;\n\nreturn [{ json: { ...data, markdownReport, htmlReport } }];" }, "position": [ 1660, 560 ] }, { "id": "check-has-data", "name": "Check If Data Exists", "type": "if", "params": { "options": {}, "conditions": { "options": { "leftValue": "", "caseSensitive": true, "typeValidation": "strict" }, "combinator": "and", "conditions": [ { "id": "has-data", "operator": { "type": "number", "operation": "gt" }, "leftValue": "={{ $json.correlatedData.length }}", "rightValue": "0" } ] } }, "position": [ 1880, 560 ] }, { "id": "output-report", "name": "Output Report", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "report", "name": "report", "type": "string", "value": "={{ $json.markdownReport }}" }, { "id": "htmlReport", "name": "htmlReport", "type": "string", "value": "={{ $json.htmlReport }}" }, { "id": "summary", "name": "summary", "type": "object", "value": "={{ $json.summary }}" }, { "id": "topErrors", "name": "topErrors", "type": "array", "value": "={{ $json.topErrors }}" }, { "id": "failedRequests", "name": "failedRequests", "type": "array", "value": "={{ $json.correlatedData.filter(r => r.isFailure) }}" } ] } }, "position": [ 2100, 460 ] }, { "id": "no-data-found", "name": "No Data Found", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "message", "name": "message", "type": "string", "value": "No data found for the specified time range and filters. Check your App Insights configuration." } ] } }, "position": [ 2100, 660 ] }, { "id": "export-excel", "name": "Export to Excel", "type": "integration", "params": { "options": { "fileName": "=azure-api-failures-{{ $now.format('yyyy-MM-dd-HHmmss') }}.xlsx", "headerRow": true }, "service": "spreadsheetfile", "operation": "toFile", "fileFormat": "xlsx", "__originalType": "n8n-nodes-base.spreadsheetFile" }, "position": [ 2320, 360 ] }, { "id": "respond-webhook", "name": "Respond to Webhook", "type": "passthrough", "params": { "mode": "noOp", "options": {}, "respondWith": "json", "responseBody": "={{ { status: 'success', data: { summary: $json.summary, topErrors: $json.topErrors, failedRequests: $json.failedRequests, report: $json.report }, timestamp: $now.toISO() } }}" }, "position": [ 2320, 560 ] } ] }

Track Azure API and Service Bus failures with Application Insights correlation