Introspection PostgreSQL

Objectif du workflow

Découvrir l’introspection par l’usage de PostgreSQL et savoir envoyer un courriel

Le “workflow” doit envoyer un courriel avec des informations sur l’installation et des statistiques d’utilisation des workflows.

Description du workflow

Il est nécessaire de lancer des requête select par le biais du composant “Postgresql” sur les tables “workflow_statistics” et “projet”.

Consignes de l’exercice

Voici les données attendues par courriel :

Configuration N8N

Installation

ID: uhVxJMFIVsAZYAgd
Type: personal
Date: 2024-06-12T15:47:41.515Z

Statistiques

Données chargées: 9
Erreurs en manuel: 166
Succès en manuel: 447
Erreurs production: 5352
Succès production: 259

Correction de l’exercice

Introspection PostgreSQL

{
  "name": "autoinspect",
  "nodes": [
    {
      "parameters": {},
      "id": "360fc90a-b2f6-44c3-9622-0ac7d8f8d191",
      "name": "When clicking ‘Test workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "typeVersion": 1,
      "position": [
        280,
        220
      ]
    },
    {
      "parameters": {
        "operation": "select",
        "schema": {
          "__rl": true,
          "value": "public",
          "mode": "list",
          "cachedResultName": "public"
        },
        "table": {
          "__rl": true,
          "value": "project",
          "mode": "list",
          "cachedResultName": "project"
        },
        "options": {}
      },
      "id": "ae18f17d-dac8-4fc9-ae96-c68a145b6cfd",
      "name": "Postgres2",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.4,
      "position": [
        500,
        320
      ],
      "credentials": {
        "postgres": {
          "id": "9DMYRCcVXp7aX3WV",
          "name": "Postgres account"
        }
      }
    },
    {
      "parameters": {
        "fromEmail": "=nico@ia86.cc",
        "toEmail": "={{ $json.data[5].name }}",
        "subject": "My n8n installation",
        "html": "=<h1>Configuration N8N</h1>\n<h2>Installation</h2>\n<p>\n<b>ID:</b> {{ $json.data[5].id }}<br>\n<b>Type:</b> {{ $json.data[5].type }}<br>\n<b>Date:</b> {{ $json.data[5].createdAt }}<br>\n</p>\n<h2>Statistiques</h2>\n<p>\n<b>Données chargées:</b> {{ $json.data[0].sum }}<br>\n<b>Erreurs en manuel:</b> {{ $json.data[1].sum }}<br>\n<b>Succès en manuel:</b> {{ $json.data[2].sum }}<br>\n<b>Erreurs production:</b> {{ $json.data[3].sum }}<br>\n<b>Succès production:</b> {{ $json.data[4].sum }}<br>",
        "options": {}
      },
      "id": "1cb68d10-a296-4c18-8cd6-b64daa69e9ab",
      "name": "Send Email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 2.1,
      "position": [
        1040,
        220
      ],
      "credentials": {
        "smtp": {
          "id": "d4ZAN48vF9GTpVON",
          "name": "SMTP account"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "select distinct \"name\" as info, sum(count) from workflow_statistics group by name order by name",
        "options": {}
      },
      "id": "1af5f9a4-0d5e-42d1-89c7-a8e27b3f4982",
      "name": "Postgres3",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.4,
      "position": [
        500,
        140
      ],
      "credentials": {
        "postgres": {
          "id": "9DMYRCcVXp7aX3WV",
          "name": "Postgres account"
        }
      }
    },
    {
      "parameters": {},
      "id": "4290eb5e-82b0-429c-9412-023b43fc4235",
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 2.1,
      "position": [
        720,
        220
      ]
    },
    {
      "parameters": {
        "aggregate": "aggregateAllItemData",
        "options": {}
      },
      "id": "a26f4968-f54c-4899-89b8-6890edffaaba",
      "name": "Aggregate",
      "type": "n8n-nodes-base.aggregate",
      "typeVersion": 1,
      "position": [
        880,
        220
      ]
    }
  ],
  "pinData": {},
  "connections": {
    "When clicking ‘Test workflow’": {
      "main": [
        [
          {
            "node": "Postgres3",
            "type": "main",
            "index": 0
          },
          {
            "node": "Postgres2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres2": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Postgres3": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "Aggregate",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send Email": {
      "main": [
        []
      ]
    },
    "Aggregate": {
      "main": [
        [
          {
            "node": "Send Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "41d227f8-2c4f-4115-b225-41a89b4ff9f2",
  "meta": {
    "templateCredsSetupCompleted": true,
    "instanceId": "366d92472788798a2a8c91dff5608f9b248c880970d12dcd224edfaeeb4a3df8"
  },
  "id": "MOOkHvSIRpzMukzY",
  "tags": []
}

Retour en haut