Collect SOC 2 AWS IAM evidence to Google Sheets with Gmail alerts

{
  "name": "Collect SOC 2 AWS IAM evidence to Google Sheets with Gmail alerts + BillionVerify",
  "nodes": [
    {
      "id": "b4dec251-3751-411d-ae2f-553ca9da5c0e",
      "name": "README",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3872,
        2160
      ],
      "parameters": {
        "width": 444,
        "height": 472,
        "content": "## SOC 2 Evidence Collector \n### How it works\n1. **Trigger**: Runs automatically every quarter (or manually).\n2. **Collect**: Fetches active IAM User Directory via native AWS node.\n3. **Logic**: Verifies if user data was successfully retrieved.\n4. **Export**: Appends the directory to a Google Sheet.\n5. **Notify**: Sends an executive summary email to the admin.\n\n### Setup steps\n- [ ] Connect AWS Credentials (`iam:ListUsers`)\n- [ ] Connect Google Sheets Account\n- [ ] Create a Google Sheet named 'IAM Access Review'\n- [ ] Add columns: Username, User ID, ARN, Create Date, Audit Date\n- [ ] Connect Gmail account to both Email nodes\n- [ ] Update 'Send To' email addresses\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "fc238ca2-16a6-4659-85e0-86c0d7be4ef8",
      "name": "Sticky Note Section 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        4352,
        2160
      ],
      "parameters": {
        "color": 7,
        "width": 424,
        "height": 480,
        "content": "### 1. TRIGGER & CONFIG\nSchedule → Timestamp\nInitiates the quarterly audit and tags \nthe run with a precise timestamp."
      },
      "typeVersion": 1
    },
    {
      "id": "95833ffb-4d0a-4525-87be-34841e68e014",
      "name": "Sticky Note Section 2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        4832,
        2160
      ],
      "parameters": {
        "color": 7,
        "width": 404,
        "height": 480,
        "content": "### 2. AWS IAM COLLECTION\nFetch → Format\nRetrieves the active AWS IAM User \nDirectory for User Access Reviews."
      },
      "typeVersion": 1
    },
    {
      "id": "4467920b-13d4-4a90-88fc-8d8de13e145e",
      "name": "Sticky Note Section 3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        5264,
        2160
      ],
      "parameters": {
        "color": 7,
        "width": 620,
        "height": 480,
        "content": "### 3. LOGIC & EXPORT\nBranch → Log → Aggregate\nRoutes data based on success. Appends \nto Sheets and summarizes the total count."
      },
      "typeVersion": 1
    },
    {
      "id": "088593ca-0c72-4131-9738-be66e5018d3f",
      "name": "Sticky Note Section 4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        5920,
        2160
      ],
      "parameters": {
        "color": 7,
        "width": 360,
        "height": 480,
        "content": "### 4. NOTIFICATIONS\nEmail Delivery\nSends professional alerts for both \nsuccessful audits and warnings."
      },
      "typeVersion": 1
    },
    {
      "id": "9ee8a30b-7541-4f8b-ad69-d8dc9305adbc",
      "name": "Quarterly Schedule",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        4416,
        2384
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 0 1 */3 *"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "e3f876c4-8933-4a10-9dda-7a871bef448c",
      "name": "Set Audit Data",
      "type": "n8n-nodes-base.code",
      "position": [
        4640,
        2384
      ],
      "parameters": {
        "jsCode": "return {\n  json: {\n    auditType: 'User Access Review (UAR)',\n    collectionTimestamp: new Date().toISOString()\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "af226c77-16df-4767-a19d-1724aeef2632",
      "name": "List IAM Users",
      "type": "n8n-nodes-base.awsIam",
      "onError": "continueRegularOutput",
      "position": [
        4864,
        2384
      ],
      "parameters": {
        "requestOptions": {},
        "additionalFields": {}
      },
      "typeVersion": 1
    },
    {
      "id": "faa2591d-9d1f-46e5-ad8f-ba3a50baff43",
      "name": "Format User Evidence",
      "type": "n8n-nodes-base.code",
      "position": [
        5088,
        2384
      ],
      "parameters": {
        "jsCode": "const inputData = $input.all();\nconst auditData = $('Set Audit Data').first().json;\n\nlet users = [];\nif (inputData.length > 0 && inputData[0].json && inputData[0].json.Users) {\n    users = inputData[0].json.Users;\n} else if (inputData.length > 0 && inputData[0].json && inputData[0].json.UserName) {\n    users = inputData.map(item => item.json);\n}\n\nif (users.length === 0) {\n  return [{ json: { username: 'NO_USERS_FOUND', auditDate: auditData.collectionTimestamp } }];\n}\n\nconst formatted = users.map(user => ({\n  json: {\n    username: user.UserName || 'N/A',\n    userId: user.UserId || 'N/A',\n    arn: user.Arn || 'N/A',\n    createDate: user.CreateDate || 'N/A',\n    auditDate: auditData.collectionTimestamp\n  }\n}));\n\nreturn formatted;"
      },
      "typeVersion": 2
    },
    {
      "id": "be83c2fb-d60b-4a66-86e8-8279f402e591",
      "name": "Users Found?",
      "type": "n8n-nodes-base.if",
      "position": [
        5312,
        2384
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 1,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "check",
              "operator": {
                "type": "string",
                "operation": "notEquals"
              },
              "leftValue": "={{ $json.username }}",
              "rightValue": "NO_USERS_FOUND"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "c6cddc58-555c-44d3-b970-3cd38b07c9f7",
      "name": "Export to Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        5536,
        2288
      ],
      "parameters": {
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": ""
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "a3783b1f-be0f-4993-9d8a-3f82c52d70cf",
      "name": "Summarize Run",
      "type": "n8n-nodes-base.code",
      "position": [
        5760,
        2288
      ],
      "parameters": {
        "jsCode": "const totalCount = $input.all().length;\nconst auditDate = $('Set Audit Data').first().json.collectionTimestamp;\nreturn [{ json: { totalUsers: totalCount, auditDate: auditDate.split('T')[0] } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "8fcd1f6d-8db8-4892-b1c7-1d85a720e822",
      "name": "Send Success Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        5984,
        2288
      ],
      "webhookId": "b9250838-5c4e-4a9e-9434-b69f4af32c22",
      "parameters": {
        "message": "=<div style=\"font-family: 'Segoe UI', Arial, sans-serif; color: #1f2937; max-width: 600px; margin: 0 auto; border: 1px solid #e5e7eb; border-radius: 12px; overflow: hidden;\"><div style=\"background-color: #059669; padding: 25px; color: #ffffff; text-align: center;\"><h1 style=\"margin: 0; font-size: 22px;\">AWS Access Review Complete</h1></div><div style=\"padding: 30px; background-color: #ffffff;\"><p style=\"font-size: 16px;\">Hello,</p><p style=\"font-size: 15px; color: #4b5563;\">Your quarterly AWS IAM User Access Review has been automatically executed and the evidence has been logged for SOC 2 compliance.</p><div style=\"background-color: #f9fafb; border: 1px solid #e5e7eb; border-radius: 8px; padding: 20px; margin: 25px 0; text-align: center;\"><p style=\"margin: 0; font-size: 14px; color: #6b7280; text-transform: uppercase;\">Active IAM Users Exported</p><h2 style=\"margin: 10px 0 0 0; color: #059669; font-size: 36px;\">{{ $json.totalUsers }}</h2></div><p style=\"font-size: 14px; color: #4b5563;\"><strong>Audit Date:</strong> {{ $json.auditDate }}</p><p style=\"font-size: 14px; color: #4b5563;\">All user details have been appended to your connected Google Sheet.</p></div><div style=\"background-color: #f3f4f6; padding: 15px; text-align: center; font-size: 11px; color: #9ca3af;\"><p style=\"margin: 0;\">Automated by n8n • SOC 2 Evidence Collector</p></div></div>",
        "options": {},
        "subject": "✅ SOC 2 Audit: AWS Access Review Complete"
      },
      "typeVersion": 2.2
    },
    {
      "id": "8d7beb4e-e725-47b9-bcc1-c4dd600fcb21",
      "name": "Send Warning Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        5984,
        2480
      ],
      "webhookId": "693529d4-d576-4e81-84ce-c3927ca1bb35",
      "parameters": {
        "message": "=<div style=\"font-family: 'Segoe UI', Arial, sans-serif; color: #1f2937; max-width: 600px; margin: 0 auto; border: 1px solid #fecdd3; border-radius: 12px; overflow: hidden;\"><div style=\"background-color: #e11d48; padding: 25px; color: #ffffff; text-align: center;\"><h1 style=\"margin: 0; font-size: 22px;\">⚠️ Audit Warning</h1></div><div style=\"padding: 30px; background-color: #ffffff;\"><p style=\"font-size: 16px;\">Hello,</p><p style=\"font-size: 15px; color: #4b5563;\">Your scheduled AWS IAM User Access Review just ran, but <strong>no users were found</strong> or the connection failed.</p><div style=\"background-color: #fff1f2; border: 1px solid #fecdd3; border-radius: 8px; padding: 20px; margin: 25px 0;\"><h3 style=\"margin: 0 0 10px 0; color: #be123c; font-size: 16px;\">Troubleshooting Steps:</h3><ul style=\"margin: 0; padding-left: 20px; font-size: 14px; color: #4b5563;\"><li>Check your AWS credentials in n8n.</li><li>Ensure the IAM user running this has the `iam:ListUsers` permission.</li></ul></div><p style=\"font-size: 14px; color: #4b5563;\">Please resolve this issue before your next auditor check-in.</p></div><div style=\"background-color: #f3f4f6; padding: 15px; text-align: center; font-size: 11px; color: #9ca3af;\"><p style=\"margin: 0;\">Automated by n8n • SOC 2 Evidence Collector</p></div></div>",
        "options": {},
        "subject": "⚠️ SOC 2 Audit Warning: No AWS Users Found"
      },
      "typeVersion": 2.2
    },
    {
      "parameters": {
        "operation": "verify",
        "email": "={{ $json.email || $json.Email }}",
        "additionalOptions": {}
      },
      "type": "n8n-nodes-billionverify.billionVerify",
      "typeVersion": 1,
      "position": [
        5624,
        2288
      ],
      "name": "Verify Email (BillionVerify)",
      "credentials": {
        "billionVerifyApi": {
          "id": "",
          "name": "BillionVerify account"
        }
      }
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "leftValue": "",
            "typeValidation": "loose"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "is-deliverable",
              "leftValue": "={{ $json.is_deliverable }}",
              "rightValue": "",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              }
            }
          ]
        }
      },
      "type": "n8n-nodes-base.if",
      "typeVersion": 2,
      "position": [
        5804,
        2288
      ],
      "name": "IF deliverable"
    },
    {
      "parameters": {
        "operation": "verify",
        "email": "={{ $json.email || $json.Email }}",
        "additionalOptions": {}
      },
      "type": "n8n-nodes-billionverify.billionVerify",
      "typeVersion": 1,
      "position": [
        5624,
        2480
      ],
      "name": "Verify Email (BillionVerify) 2",
      "credentials": {
        "billionVerifyApi": {
          "id": "",
          "name": "BillionVerify account"
        }
      }
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "leftValue": "",
            "typeValidation": "loose"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "is-deliverable",
              "leftValue": "={{ $json.is_deliverable }}",
              "rightValue": "",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              }
            }
          ]
        }
      },
      "type": "n8n-nodes-base.if",
      "typeVersion": 2,
      "position": [
        5804,
        2480
      ],
      "name": "IF deliverable 2"
    }
  ],
  "connections": {
    "Users Found?": {
      "main": [
        [
          {
            "node": "Export to Google Sheets",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Verify Email (BillionVerify) 2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Summarize Run": {
      "main": [
        [
          {
            "node": "Verify Email (BillionVerify)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "List IAM Users": {
      "main": [
        [
          {
            "node": "Format User Evidence",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Audit Data": {
      "main": [
        [
          {
            "node": "List IAM Users",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Quarterly Schedule": {
      "main": [
        [
          {
            "node": "Set Audit Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format User Evidence": {
      "main": [
        [
          {
            "node": "Users Found?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Export to Google Sheets": {
      "main": [
        [
          {
            "node": "Summarize Run",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Verify Email (BillionVerify)": {
      "main": [
        [
          {
            "node": "IF deliverable",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF deliverable": {
      "main": [
        [
          {
            "node": "Send Success Email",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    },
    "Verify Email (BillionVerify) 2": {
      "main": [
        [
          {
            "node": "IF deliverable 2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF deliverable 2": {
      "main": [
        [
          {
            "node": "Send Warning Email",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    }
  },
  "settings": {
    "binaryMode": "separate",
    "executionOrder": "v1"
  }
}