Consolidated Report of Executions

Describe the issue/error/question

How to retrieve a list of all executions?

What is the error message (if any)?

none

Hello,
I’ve just switched to n8n from Zapier. I have to say I LOVE this product! It’s absolutely amazing, and I love that all my data is back on my servers.

One feature that Zapier had, was a weekly mailing of the Zap history for the week. Is there any way for me to get a list of all executions for the past week? I can’t seem to find a node that will do that.

Thanks,
Daniel

Here’s a start at least, would love others feedback on this process. This basically queries your n8n instance until it reaches executions older than a week ago. Unfortunately I don’t know how to combine the outputs of multiple executions within a workflow. This means you can’t just pop the entire results in an email or slack message (at this stage).

You can however drop them as rows into a Google Sheets or a DB and work from there?


As you can see I have a lot of executions within a week

Workflow JSON
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -480,
        100
      ]
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "http://localhost:5678/rest/executions",
        "options": {},
        "queryParametersUi": {
          "parameter": [
            {
              "name": "limit",
              "value": "500"
            },
            {
              "name": "lastId",
              "value": "={{$json.id }}"
            }
          ]
        }
      },
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        200,
        100
      ],
      "credentials": {
        "httpBasicAuth": {
          "id": "3",
          "name": "n8n local"
        }
      }
    },
    {
      "parameters": {
        "value": "={{$json[\"data\"]}}",
        "toFormat": "YYYY-MM-DD",
        "options": {}
      },
      "name": "Format",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        -20,
        100
      ]
    },
    {
      "parameters": {
        "fieldToSplitOut": "data.results",
        "options": {}
      },
      "name": "Split Results",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        440,
        100
      ]
    },
    {
      "parameters": {
        "operation": "sort",
        "sortFieldsUi": {
          "sortField": [
            {
              "fieldName": "startedAt"
            }
          ]
        },
        "options": {}
      },
      "name": "Sort by Start Time",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        700,
        100
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [],
          "dateTime": [
            {
              "value1": "={{$json[\"startedAt\"]}}",
              "value2": "={{$node[\"Format\"].json[\"data\"]}}"
            }
          ]
        }
      },
      "name": "Need More Data?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        940,
        180
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "conditions": {
          "string": [],
          "dateTime": [
            {
              "value1": "={{$json[\"startedAt\"]}}",
              "value2": "={{ $item(0).$node[\"Format\"].json.data }}"
            }
          ]
        }
      },
      "name": "Check Date",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        940,
        20
      ],
      "executeOnce": false
    },
    {
      "parameters": {},
      "name": "Add to Spreadsheet",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        1200,
        0
      ]
    },
    {
      "parameters": {
        "action": "calculate",
        "value": "={{ Date.now() }}",
        "operation": "subtract",
        "duration": 7,
        "options": {}
      },
      "name": "Date Last Week",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        -240,
        100
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Date Last Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request": {
      "main": [
        [
          {
            "node": "Split Results",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Results": {
      "main": [
        [
          {
            "node": "Sort by Start Time",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sort by Start Time": {
      "main": [
        [
          {
            "node": "Need More Data?",
            "type": "main",
            "index": 0
          },
          {
            "node": "Check Date",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Need More Data?": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Date": {
      "main": [
        [
          {
            "node": "Add to Spreadsheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Date Last Week": {
      "main": [
        [
          {
            "node": "Format",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
2 Likes

Thank you for this. That gives me a big jump start on my workflow!

1 Like

Just a note, it might actually be more precise if you change the Sort by Start Time node to use id instead of startedAt.

Another option, depending on your deployment is querying the database directly, which could get all the results in a single execution. Something like this for postgres:

SELECT id, mode, "startedAt", "stoppedAt", "workflowId", "workflowData" FROM "execution_entity" WHERE "startedAt" > '<YOUR DATE>' ORDER BY "startedAt" ASC;

OK I have a solution! The problem before is you can’t explicitly join all executions cleanly if the function node doing the joining gets executed multiple times itself!

So in this revision instead of forking we use the false output from Need More Data? node to run a function to merge all our results from Split Results. We can then sort and format this as for example a markdown table!

Workflow JSON
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "http://localhost:5678/rest/executions",
        "options": {},
        "queryParametersUi": {
          "parameter": [
            {
              "name": "limit",
              "value": "500"
            },
            {
              "name": "lastId",
              "value": "={{$json.id }}"
            }
          ]
        }
      },
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        880,
        300
      ],
      "credentials": {
        "httpBasicAuth": {
          "id": "3",
          "name": "n8n local"
        }
      }
    },
    {
      "parameters": {
        "value": "={{$json[\"data\"]}}",
        "toFormat": "YYYY-MM-DD",
        "options": {}
      },
      "name": "Format",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        660,
        300
      ]
    },
    {
      "parameters": {
        "fieldToSplitOut": "data.results",
        "options": {}
      },
      "name": "Split Results",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        1120,
        100
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [],
          "dateTime": [
            {
              "value1": "={{$json[\"startedAt\"]}}",
              "value2": "={{$node[\"Format\"].json[\"data\"]}}"
            }
          ]
        }
      },
      "name": "Need More Data?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1120,
        540
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "action": "calculate",
        "value": "={{ Date.now() }}",
        "operation": "subtract",
        "duration": 7,
        "options": {}
      },
      "name": "Date Last Week",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        440,
        300
      ]
    },
    {
      "parameters": {
        "operation": "sort",
        "sortFieldsUi": {
          "sortField": [
            {
              "fieldName": "id"
            }
          ]
        },
        "options": {}
      },
      "name": "Sort Results",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        1120,
        320
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [],
          "dateTime": [
            {
              "value1": "={{$json[\"startedAt\"]}}",
              "value2": "={{ $items(\"Format\", 0)[0].json.data }}"
            }
          ]
        }
      },
      "name": "Check Date",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1660,
        300
      ],
      "executeOnce": false
    },
    {
      "parameters": {
        "functionCode": "const allData = []\n\nlet counter = 0;\ndo {\n  try {\n    allData.push.apply(allData, $items(\"Split Results\", 0, counter));\n  } catch (error) {\n    return allData;  \n  }\n\n  counter++;\n} while(true);\n\n\n"
      },
      "name": "Merge Executions",
      "type": "n8n-nodes-base.function",
      "position": [
        1420,
        300
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "operation": "sort",
        "sortFieldsUi": {
          "sortField": [
            {
              "fieldName": "startedAt"
            }
          ]
        },
        "options": {}
      },
      "name": "Sort All Executions",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        1940,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "const allData = []\n\n// Markdown header\nconst header = Object.keys(items[0].json)\nallData.push(`| ${header.join(' | ')} |`)\nallData.push('---|'.repeat(header.length))\n\nfor (item of items) {\n  allData.push(Object.values(item.json).join(' | '));\n}\n\nreturn [{json: {markdown: allData.join('\\n')}}];\n"
      },
      "name": "Format Markdown Table",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        2160,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Date Last Week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request": {
      "main": [
        [
          {
            "node": "Split Results",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Results": {
      "main": [
        [
          {
            "node": "Sort Results",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Need More Data?": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Merge Executions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Date Last Week": {
      "main": [
        [
          {
            "node": "Format",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sort Results": {
      "main": [
        [
          {
            "node": "Need More Data?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Date": {
      "main": [
        [
          {
            "node": "Sort All Executions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge Executions": {
      "main": [
        [
          {
            "node": "Check Date",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sort All Executions": {
      "main": [
        [
          {
            "node": "Format Markdown Table",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

And for completeness we check if we have no more results, otherwise if we don’t have executions that go back long enough we won’t get output.

Workflow JSON
{
  "nodes": [
    {
      "parameters": {
        "value": "={{$json[\"data\"]}}",
        "toFormat": "YYYY-MM-DD",
        "options": {}
      },
      "name": "Format",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        660,
        300
      ]
    },
    {
      "parameters": {
        "fieldToSplitOut": "data.results",
        "options": {}
      },
      "name": "Split Results",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        1120,
        180
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [],
          "dateTime": [
            {
              "value1": "={{$json[\"startedAt\"]}}",
              "operation": "before",
              "value2": "={{$node[\"Format\"].json[\"data\"]}}"
            }
          ]
        }
      },
      "name": "Need More Data?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1120,
        580
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "action": "calculate",
        "value": "={{ Date.now() }}",
        "operation": "subtract",
        "duration": 14,
        "options": {}
      },
      "name": "Date Last Week",
      "type": "n8n-nodes-base.dateTime",
      "typeVersion": 1,
      "position": [
        440,
        300
      ]
    },
    {
      "parameters": {
        "operation": "sort",
        "sortFieldsUi": {
          "sortField": [
            {
              "fieldName": "id"
            }
          ]
        },
        "options": {}
      },
      "name": "Sort Results",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        1120,
        380
      ]
    },
    {
      "parameters": {
        "functionCode": "const allData = []\n\nlet counter = 0;\ndo {\n  try {\n    allData.push.apply(allData, $items(\"Split Results\", 0, counter));\n  } catch (error) {\n    return allData;  \n  }\n\n  counter++;\n} while(true);\n\n\n"
      },
      "name": "Merge Executions",
      "type": "n8n-nodes-base.function",
      "position": [
        1420,
        300
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "operation": "sort",
        "sortFieldsUi": {
          "sortField": [
            {
              "fieldName": "startedAt"
            }
          ]
        },
        "options": {}
      },
      "name": "Sort All Executions",
      "type": "n8n-nodes-base.itemLists",
      "typeVersion": 1,
      "position": [
        1920,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "const allData = []\n\n// Markdown header\nconst header = Object.keys(items[0].json)\nallData.push(`| ${header.join(' | ')} |`)\nallData.push('---|'.repeat(header.length))\n\nfor (item of items) {\n  allData.push(Object.values(item.json).join(' | '));\n}\n\nreturn [{json: {markdown: allData.join('\\n')}}];\n"
      },
      "name": "Format Markdown Table",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        2160,
        300
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [],
          "dateTime": [
            {
              "value1": "={{$json[\"startedAt\"]}}",
              "value2": "={{ $items(\"Format\", 0)[0].json.data }}"
            }
          ],
          "boolean": [
            {
              "value1": "={{$json.mode != 'trigger' || $json.finished != true }}",
              "value2": true
            }
          ]
        }
      },
      "name": "Check Conditions",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1660,
        300
      ],
      "executeOnce": false
    },
    {
      "parameters": {
        "authentication": "basicAuth",
        "url": "http://localhost:5678/rest/executions",
        "options": {},
        "queryParametersUi": {
          "parameter": [
            {
              "name": "limit",
              "value": "1000"
            },
            {
              "name": "lastId",
              "value": "={{$json.id }}"
            }
          ]
        }
      },
      "name": "Get Executions",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        880,
        300
      ],
      "credentials": {
        "httpBasicAuth": {
          "id": "3",
          "name": "n8n local"
        }
      }
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$json.data.results.length > 0}}"
            }
          ]
        }
      },
      "name": "No More Executions",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1120,
        -40
      ]
    }
  ],
  "connections": {
    "Format": {
      "main": [
        [
          {
            "node": "Get Executions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Results": {
      "main": [
        [
          {
            "node": "Sort Results",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Need More Data?": {
      "main": [
        [
          {
            "node": "Merge Executions",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Get Executions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Date Last Week": {
      "main": [
        [
          {
            "node": "Format",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sort Results": {
      "main": [
        [
          {
            "node": "Need More Data?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge Executions": {
      "main": [
        [
          {
            "node": "Check Conditions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Sort All Executions": {
      "main": [
        [
          {
            "node": "Format Markdown Table",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Conditions": {
      "main": [
        [
          {
            "node": "Sort All Executions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get Executions": {
      "main": [
        [
          {
            "node": "No More Executions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "No More Executions": {
      "main": [
        [
          {
            "node": "Merge Executions",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Split Results",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
2 Likes

This is amazing work!

Thank you so much, I’ll be working on implementing it for my setup.