KPI Reports
Shared 8/22/2025
427 views
Visual Workflow
JSON Code
{
"id": "IWmETJP2g5J6d6Rz",
"meta": {
"instanceId": "ff4e13e053d9da487c66660fc7a01fe99b8f8b8627cfb9c88198e6bdb2c0f239",
"templateCredsSetupCompleted": true
},
"name": "PNCT Report - PROD",
"tags": [],
"nodes": [
{
"id": "565ec973-c4e5-4d68-a162-86ae5685f244",
"name": "Tools",
"type": "n8n-nodes-base.httpRequest",
"position": [
40,
40
],
"parameters": {
"url": "https://makara.simaanalytics.co/api/v1/tools",
"options": {
"pagination": {
"pagination": {
"parameters": {
"parameters": [
{
"name": "cursor",
"value": "={{ $response.body[\"next_cursor\"] }}"
}
]
},
"maxRequests": 15,
"limitPagesFetched": true,
"completeExpression": "={{ !$response.body[\"has_next_page\"] }}",
"paginationCompleteWhen": "other"
}
}
},
"sendQuery": true,
"sendHeaders": true,
"queryParameters": {
"parameters": [
{}
]
},
"headerParameters": {
"parameters": [
{
"name": "cookie",
"value": "={{ $json.headers.cookie }}"
},
{
"name": "x-tenant-name",
"value": "={{ $json.headers['x-tenant-name'] }}"
}
]
}
},
"typeVersion": 4.2
},
{
"id": "329f430a-263b-4f5d-add2-eead7d747296",
"name": "Status History",
"type": "n8n-nodes-base.httpRequest",
"position": [
40,
340
],
"parameters": {
"url": "https://makara.simaanalytics.co/api/v1/tools/status-history",
"options": {
"pagination": {
"pagination": {
"parameters": {
"parameters": [
{
"name": "cursor",
"value": "={{ $response.body[\"next_cursor\"] }}"
},
{
"name": "fromDate",
"value": "={{ $json.query.historyFrom }}"
},
{
"name": "toDate",
"value": "={{ $json.query.to }}"
}
]
},
"maxRequests": 25,
"limitPagesFetched": true,
"completeExpression": "={{ !$response.body[\"has_next_page\"] }}",
"paginationCompleteWhen": "other"
}
}
},
"sendQuery": true,
"sendHeaders": true,
"queryParameters": {
"parameters": [
{
"name": "fromDate",
"value": "={{ $json.query.historyFrom }}"
},
{
"name": "toDate",
"value": "={{ $json.query.to }}"
}
]
},
"headerParameters": {
"parameters": [
{
"name": "cookie",
"value": "={{ $json.headers.cookie }}"
},
{
"name": "x-tenant-name",
"value": "={{ $json.headers['x-tenant-name'] }}"
}
]
}
},
"typeVersion": 4.2
},
{
"id": "2f165a32-cf55-4b1f-ba6c-97579602eace",
"name": "Merge",
"type": "n8n-nodes-base.merge",
"position": [
540,
180
],
"parameters": {},
"typeVersion": 3
},
{
"id": "706e2c2f-f5ca-4aa6-83b7-0fdbc88a6fd0",
"name": "Code",
"type": "n8n-nodes-base.code",
"position": [
780,
180
],
"parameters": {
"jsCode": "function hoursToSeconds(hours) {\n return hours * 3600;\n}\n\nconst notUsingRunningTypes = [188, 185, 187, 196, 191, 194, 192, 193];\n\nfunction getTotalTime(periodStart, periodEnd) {\n const diffInMs = periodEnd.getTime() - periodStart.getTime();\n const diffInDays = diffInMs / (1000 * 60 * 60 * 24);\n\n return Math.ceil(diffInDays * 24);\n}\n\nfunction calculateKPIs(statusHistory, toolDetails) {\nconst query = $(\"webhook\").all()[0].json.query\n\n const from = query.from;\n const to = query.to\n \n const periodStart = new Date(from);\n const periodEnd = new Date(to);\n const totalTime = getTotalTime(periodStart, periodEnd); \n const allowedTypes = query.types ? query.types.split(',').map(Number) : null; \n const entity = query.entity || \"tool\"; \n\n if (entity === \"toolType\") {\n return calculateToolTypeKPIs(statusHistory, toolDetails, totalTime, \n allowedTypes, periodStart, periodEnd);\n}\n // Create a mapping of equipment using the unique tool id.\n // We combine the status history events and current status details.\n const equipmentMap = {};\n\n // Process status history records\n statusHistory.forEach(record => {\n const event = record.json;\n const equipId = event.tool.id;\n if (!allowedTypes || allowedTypes.includes(event.tool.type.id)) {\n if (!equipmentMap[equipId]) {\n equipmentMap[equipId] = {\n events: [],\n currentStatus: null,\n typeId: event.tool.type.id,\n toolName: event.tool.name,\n toolTypeName: event.tool.type.name,\n toolId: equipId\n };\n }\n equipmentMap[equipId].events.push(event);\n }\n });\n\n // Process current equipment details\n toolDetails.forEach(record => {\n const detail = record.json;\n const equipId = detail.id;\n if (!allowedTypes || allowedTypes.includes(detail.type.id)) { \n if (!equipmentMap[equipId]) {\n equipmentMap[equipId] = {\n events: [],\n currentStatus: detail.current_status,\n toolName: detail.name,\n toolTypeName: detail.type.name,\n toolId: equipId\n };\n } else {\n equipmentMap[equipId].currentStatus = detail.current_status;\n }\n }\n });\n\n const results = [];\n\n // Process each equipment\n Object.keys(equipmentMap).forEach(equipId => {\n const data = equipmentMap[equipId];\n const events = data.events;\n\n // Sort events chronologically\n events.sort((a, b) => new Date(a.created_at) - new Date(b.created_at));\n\n // Determine the initial status at the start of the reporting period:\n // Look for the last event before the reporting window.\n let initialStatus = null;\n for (let i = events.length - 1; i >= 0; i--) {\n const eventTime = new Date(events[i].created_at);\n if (eventTime < periodStart) {\n initialStatus = events[i].state.name;\n break;\n }\n }\n // If no event before the period, use the first event in the window or fall back to current status.\n if (!initialStatus) {\n if (events.length > 0 && new Date(events[0].created_at) >= periodStart) {\n initialStatus = events[0].state.name;\n } else if (data.currentStatus) {\n initialStatus = data.currentStatus.state.name;\n } else {\n initialStatus = \"Running\"; // Default assumption if no data is available\n }\n }\n\n // Build the timeline intervals for the reporting period.\n // Start with an interval beginning at periodStart with the initial status.\n const timeline = [];\n let lastTime = periodStart;\n let lastStatus = initialStatus;\n\n events.forEach(event => {\n const eventTime = new Date(event.created_at);\n // Update initial status if event is before the window\n if (eventTime < periodStart) {\n lastStatus = event.state.name;\n } else if (eventTime >= periodStart && eventTime <= periodEnd) {\n // Create an interval from the last timestamp to the current event time.\n timeline.push({\n status: lastStatus,\n start: lastTime,\n end: eventTime\n });\n lastTime = eventTime;\n lastStatus = event.state.name;\n }\n // Ignore events after the reporting window\n });\n // Add a final interval from the last event (or periodStart) to periodEnd\n timeline.push({\n status: lastStatus,\n start: lastTime,\n end: periodEnd\n });\n\n // Sum durations (in hours) for each status.\n // The API returns statuses exactly as shown.\n const durations = {\n \"Running\": 0,\n \"Available\": 0,\n \"Breakdown\": 0,\n \"In Repair\": 0,\n \"Planned Maintenance\": 0,\n \"Damaged\": 0,\n \"Warranty\": 0,\n \"Rest\": 0,\n \"Pending Parts/Vendor\": 0,\n \"Field Services\": 0\n };\n\n timeline.forEach(interval => {\n const durationHours = (interval.end - interval.start) / 3600000;\n if (durations.hasOwnProperty(interval.status)) {\n durations[interval.status] += durationHours;\n }\n });\n\n // Calculate Failure Metrics.\n // Failure is defined only when:\n // 1. There is a direct transition from Running to In Repair, or\n // 2. A block of one or more Breakdown intervals immediately followed by an In Repair.\n let failureDowntime = 0;\n let failureCount = 0;\n let failureBlockActive = false;\n let accumulatedFailureTime = 0;\n let prevStatus = timeline[0].status;\n\n timeline.forEach(interval => {\n const currentStatus = interval.status;\n const duration = (interval.end - interval.start) / 3600000;\n\n if ( currentStatus === \"Breakdown\") {\n // If the previous status was Running or we are in an active failure block,\n // consider this breakdown interval as part of a failure.\n if (prevStatus || failureBlockActive) {\n failureBlockActive = true;\n accumulatedFailureTime += duration;\n } else {\n // Otherwise, reset any failure block.\n failureBlockActive = false;\n accumulatedFailureTime = 0;\n }\n } else if (currentStatus === \"In Repair\") {\n // If transitioning directly from Running to In Repair, count as a failure.\n if (prevStatus === \"Running\" || notUsingRunningTypes.includes(equipmentMap[equipId].typeId)) {\n failureCount++;\n failureDowntime += duration;\n } else if (failureBlockActive) {\n // If a failure block was active (i.e. previous breakdown events), add all accumulated time plus current repair duration.\n failureCount++;\n failureDowntime += accumulatedFailureTime + duration;\n failureBlockActive = false;\n accumulatedFailureTime = 0;\n }\n // Otherwise, if In Repair follows another status (e.g. Available), do not count.\n } else {\n // For any other status, if a failure block was active, it means the failure was not resolved.\n failureBlockActive = false;\n accumulatedFailureTime = 0;\n }\n prevStatus = currentStatus;\n });\n\n // Calculate Total Downtime:\n // Sum the hours spent in statuses that count as downtime.\n const downtimeStatuses = [\"In Repair\", \"Breakdown\", \"Damaged\", \"Field Services\", \"Warranty\", \"Pending Parts/Vendor\"];\n let totalDowntime = 0;\n downtimeStatuses.forEach(status => {\n totalDowntime += durations[status] || 0;\n });\n\n // Running and Available time metrics.\n const runningTime = durations[\"Running\"] || 0;\n let availableTime = 0;\n const availableStatuses = [\"Available\", \"Rest\"]; \n availableStatuses.forEach(status => { availableTime += durations[status] || 0; });\n\n // Compute Availability and Reliability.\n const availability = (runningTime + availableTime) / totalTime;\n const reliability = runningTime > 0 ? Math.max(0, Math.min(1, (runningTime - failureDowntime) / runningTime)) : 0;\n \n // Compute MTTR (Mean Time To Repair) and MTBF (Mean Time Between Failures).\n const MTTR = failureCount > 0 ? failureDowntime / failureCount : 0;\n const MTBF =\n failureCount > 0\n ? (runningTime - failureDowntime) >= 0\n ? (runningTime - failureDowntime) / failureCount\n : 0\n : 0;\n\n // Utilization: fraction of time in Running relative to Running+Available.\n const utilization = availableTime > 0 ? runningTime / availableTime : 0;\n\n // Compute percentage metrics: percentage of total time spent in each status.\n const percentageMetrics = {};\n const percentageMetricHours = {};\n\n Object.keys(durations).forEach(key => {\n percentageMetrics[key] = {value: durations[key] / totalTime, formatTo: '%'};\n percentageMetricHours[key + ' time'] = {value: hoursToSeconds(durations[key]), formatTo: 'hours'};\n\n });\n\n // Construct the KPI object for this equipment.\n results.push({\n id: { value: data.toolId },\n Equipment: { value: data.toolName },\n Type: {value: data.toolTypeName },\n \"Total Time\": { value: hoursToSeconds(totalTime), formatTo: 'hours' },\n \"Total Downtime\": { value: hoursToSeconds(totalDowntime), formatTo: 'hours' },\n \"Failure Downtime\": { value: hoursToSeconds(failureDowntime), formatTo: 'hours' },\n \"Availability\": { value: availability, formatTo: '%' },\n \"Reliability\": { value: reliability, formatTo: '%' },\n \"Utilization\": { value: utilization, formatTo: '%' },\n \"MTTR\": { value: hoursToSeconds(MTTR), formatTo: 'minutes' },\n \"MTBF\": { value: hoursToSeconds(MTBF), formatTo: \"hours\" },\n \"Failure Count\": { value: failureCount },\n \"Running Time\": { value: hoursToSeconds(runningTime), formatTo: 'hours' },\n \"Available Time\": { value: hoursToSeconds(availableTime), formatTo: 'hours' },\n ...percentageMetrics,\n ...percentageMetricHours,\n });\n });\n results.sort( (r1, r2) => r1.Equipment.value.localeCompare(r2.Equipment.value));\n\n return results;\n}\n\nfunction calculateToolTypeKPIs(statusHistory, toolDetails, totalTimePerTool, allowedTypes, periodStart, periodEnd) {\n const typeMap = new Map();\n\n // Step 1: Initialize types and collect tools per type\n toolDetails.forEach(record => {\n const detail = record.json;\n const typeId = detail.type.id;\n const typeName = detail.type.name;\n\n if (!allowedTypes || allowedTypes.includes(typeId)) {\n if (!typeMap.has(typeId)) {\n typeMap.set(typeId, {\n toolTypeName: typeName,\n toolTypeId: typeId,\n tools: new Map(),\n events: [],\n durations: {\n \"Running\": 0, \"Available\": 0, \"Breakdown\": 0, \"In Repair\": 0, \n \"Planned Maintenance\": 0, \"Damaged\": 0, \"Warranty\": 0, \n \"Rest\": 0, \"Pending Parts/Vendor\": 0, \"Field Services\": 0\n },\n failureCount: 0,\n failureDowntime: 0,\n toolCount: 0,\n });\n }\n\n const typeData = typeMap.get(typeId);\n typeData.tools.set(detail.id, {\n toolId: detail.id,\n currentStatus: detail.current_status.state.name,\n events: []\n });\n typeData.toolCount++;\n }\n });\n\n // Step 2: Collect status events per tool type\n statusHistory.forEach(record => {\n const event = record.json;\n const typeId = event.tool.type.id;\n const toolId = event.tool.id;\n const eventTime = new Date(event.created_at);\n\n if (!allowedTypes || allowedTypes.includes(typeId)) {\n if (typeMap.has(typeId) && typeMap.get(typeId).tools.has(toolId)) {\n typeMap.get(typeId).tools.get(toolId).events.push(event);\n }\n }\n });\n\n // Step 3: Process each tool type\n const results = [];\n\n typeMap.forEach((data, typeId) => {\n const totalTime = data.toolCount * totalTimePerTool;\n\n // Process each tool in this type\n data.tools.forEach(tool => {\n let events = tool.events;\n events.sort((a, b) => new Date(a.created_at) - new Date(b.created_at));\n\n // Determine initial status\n let initialStatus = tool.currentStatus;\n for (let i = events.length - 1; i >= 0; i--) {\n if (new Date(events[i].created_at) < periodStart) {\n initialStatus = events[i].state.name;\n break;\n }\n }\n\n // Build timeline\n const timeline = [];\n let lastTime = periodStart;\n let lastStatus = initialStatus;\n\n events.forEach(event => {\n const eventTime = new Date(event.created_at);\n if (eventTime >= periodStart && eventTime <= periodEnd) {\n timeline.push({\n status: lastStatus,\n start: lastTime,\n end: eventTime,\n });\n lastTime = eventTime;\n lastStatus = event.state.name;\n }\n });\n\n // Add final interval\n timeline.push({\n status: lastStatus,\n start: lastTime,\n end: periodEnd,\n });\n\n // Sum durations for this tool\n timeline.forEach(interval => {\n const durationHours = (interval.end - interval.start) / 3600000;\n if (data.durations.hasOwnProperty(interval.status)) {\n data.durations[interval.status] += durationHours;\n }\n });\n\n // Failure detection logic\n let failureBlockActive = false;\n let accumulatedFailureTime = 0;\n let prevStatus = timeline[0].status;\n\n timeline.forEach(interval => {\n const currentStatus = interval.status;\n const duration = (interval.end - interval.start) / 3600000;\n\n if (currentStatus === \"Breakdown\") {\n // If the previous status was Running or in an active failure block, track failure\n if (prevStatus || failureBlockActive) {\n failureBlockActive = true;\n accumulatedFailureTime += duration;\n } else {\n failureBlockActive = false;\n accumulatedFailureTime = 0;\n }\n } else if (currentStatus === \"In Repair\") {\n // If transitioning from Running to In Repair, count as a failure\n if (prevStatus === \"Running\" || notUsingRunningTypes.includes(typeId) ) {\n data.failureCount++;\n data.failureDowntime += duration;\n } else if (failureBlockActive) {\n // If we had Breakdown(s) before In Repair, count all as failure downtime\n data.failureCount++;\n data.failureDowntime += accumulatedFailureTime + duration;\n failureBlockActive = false;\n accumulatedFailureTime = 0;\n }\n } else {\n failureBlockActive = false;\n accumulatedFailureTime = 0;\n }\n prevStatus = currentStatus;\n });\n });\n\n // Compute KPIs\n const totalDowntime = [\"In Repair\", \"Breakdown\", \"Damaged\", \"Field Services\", \"Warranty\", \"Pending Parts/Vendor\"]\n .reduce((sum, status) => sum + (data.durations[status] || 0), 0);\n\n const runningTime = data.durations[\"Running\"] || 0;\n let availableTime = 0;\n const availableStatuses = [\"Available\", \"Rest\"]; \n availableStatuses.forEach(status => { availableTime += data.durations[status] || 0; });\n\n const availability = Math.min((runningTime + availableTime) / totalTime, 1);\n const reliability = runningTime > 0 ? Math.max(0, Math.min(1, (runningTime - data.failureDowntime) / runningTime)) : 0;\n const utilization = availableTime > 0 ? runningTime / availableTime : 0;\n const MTTR = data.failureCount > 0 ? data.failureDowntime / data.failureCount : 0;\n const MTBF = data.failureCount > 0\n ? (runningTime - data.failureDowntime) >= 0\n ? (runningTime - data.failureDowntime) / data.failureCount\n : 0\n : 0;\n\n const percentageMetrics = {};\n const percentageMetricHours = {};\n Object.keys(data.durations).forEach(key => {\n percentageMetrics[key] = { value: Math.min(data.durations[key] / totalTime, 1), formatTo: '%' };\n percentageMetricHours[key + ' time'] = {value: hoursToSeconds(data.durations[key]), formatTo: 'hours'};\n });\n\n results.push({\n id: { value: data.toolTypeId },\n Type: { value: data.toolTypeName },\n \"Total Time\": { value: hoursToSeconds(totalTime), formatTo: 'hours' },\n \"Total Downtime\": { value: hoursToSeconds(totalDowntime), formatTo: 'hours' },\n \"Failure Downtime\": { value: hoursToSeconds(data.failureDowntime), formatTo: 'hours' },\n \"Availability\": { value: availability, formatTo: '%' },\n \"Reliability\": { value: reliability, formatTo: '%' },\n \"Utilization\": { value: utilization, formatTo: '%' },\n \"MTTR\": { value: hoursToSeconds(MTTR), formatTo: 'minutes' },\n \"MTBF\": { value: hoursToSeconds(MTBF), formatTo: 'hours' },\n \"Failure Count\": { value: data.failureCount },\n \"Running Time\": { value: hoursToSeconds(runningTime), formatTo: 'hours' },\n \"Available Time\": { value: hoursToSeconds(availableTime), formatTo: 'hours' },\n ...percentageMetrics,\n ...percentageMetricHours,\n });\n });\n\n\n return results;\n}\n\n\nreturn calculateKPIs($(\"tooStatusHistory\").all(), $(\"toolDetails\").all())\n"
},
"typeVersion": 2
},
{
"id": "415b2a48-89f8-41ab-85d4-6ef5b58f62fa",
"name": "toolDetails",
"type": "n8n-nodes-base.code",
"position": [
260,
40
],
"parameters": {
"jsCode": "return $input.all().flatMap(a => a.json.data).filter( a=>!a.is_archived)"
},
"typeVersion": 2
},
{
"id": "bd08903c-6e86-4b9c-a38a-bea64bbe679e",
"name": "tooStatusHistory",
"type": "n8n-nodes-base.code",
"position": [
260,
340
],
"parameters": {
"jsCode": "return $input.all().flatMap(a => a.json.data)"
},
"typeVersion": 2
},
{
"id": "3e997cce-65f3-4156-a3cf-2495a9097e73",
"name": "webhook",
"type": "n8n-nodes-base.webhook",
"position": [
-320,
160
],
"webhookId": "87fdd070-31fe-4a53-8852-032d1e49220f",
"parameters": {
"path": "899dd070-31fe-4a53-8852-032d1e49220f",
"options": {},
"responseData": "allEntries",
"responseMode": "lastNode"
},
"typeVersion": 2
}
],
"active": true,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "f3634fb2-f23f-4e3e-ab35-3b912283a53a",
"connections": {
"Merge": {
"main": [
[
{
"node": "Code",
"type": "main",
"index": 0
}
]
]
},
"Tools": {
"main": [
[
{
"node": "toolDetails",
"type": "main",
"index": 0
}
]
]
},
"webhook": {
"main": [
[
{
"node": "Tools",
"type": "main",
"index": 0
},
{
"node": "Status History",
"type": "main",
"index": 0
}
]
]
},
"toolDetails": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"Status History": {
"main": [
[
{
"node": "tooStatusHistory",
"type": "main",
"index": 0
}
]
]
},
"tooStatusHistory": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 1
}
]
]
}
}
}