ConnectWise Customer Audit n8n Workflow

Architecture

Webhook (companyName, toEmail, fromEmail, subject, startDate, endDate) → Native CW Node (Get a company) → Parallel Branches (Tickets + Time) → Merge → Code Node (HTML Report) → Send Email

Workflow Details

Property	Value
Name	ConnectWise Customer Ticket Time Audit
ID	`<workflowId>`
Trigger	Webhook (POST)
URL	`https://<n8n-host>/webhook/<webhook-path>`
Active Version	`be322bfd-5522-47e6-9198-6550940284a6`

Key Nodes

1. Webhook Trigger

Type: n8n-nodes-base.webhook (v2.1)
Method: POST
Path: <webhook-path>

Expected JSON body:

{
  "companyName": "Customer Name",
  "toEmail": "recipient@example.com",
  "fromEmail": "reports@vistanetinc.com",
  "subject": "Customer Audit Report",
  "startDate": "2025-01-01T00:00:00Z",
  "endDate": "2025-12-31T23:59:59Z"
}

All fields except companyName are optional.
Inside the Code node, access the payload as: $('Webhook').first().json.body || $('Webhook').first().json

2. Get a company

Type: @mspcopilot/n8n-nodes-connectwise.connectWisePsa
Resource: company
Operation: get
Company: __rl mode with value: "=?conditions=name like '%{{ $json.body?.companyName ?? $json.companyName }}%'"
⚠️ This uses a search condition inside a get call, which may fail if the API returns 500 Length cannot be less than zero.

3. Get many tickets

Type: connectWisePsa
Resource: ticket
Operation: getAll
Return All: true
Conditions: "=company/id={{ $json.id }}"

4. Get many time entries

Type: connectWisePsa
Resource: timeEntry
Operation: getAll
Return All: true
Conditions: "=company/id={{ $json.id }}"

5. Combine Data

Type: n8n-nodes-base.merge (v3.2)
Mode: combineByPosition

6. Generate HTML Report

Type: n8n-nodes-base.code (v2)
Mode: Must use runOnceForAllItems.
Logic: Accesses upstream nodes via $('Node Name'), aggregates actualHours, filters open/closed by status.name, and passes through email params from the webhook payload.
Hour field: actualHours (NOT hours)
Per-ticket hours: Must build a ticketHoursMap by summing actualHours from time entries grouped by ticket.id. Do NOT read ticket.actualHours directly.
Timeframe filtering: When startDate/endDate are provided, filter time entries by timeStart, then filter the ticket list to only show tickets with hours in the range. Sort by hours descending.

Return shape:

return [{ json: { 
  htmlReport, 
  totalBilledHours, 
  totalTickets, 
  openTickets, 
  closedTickets, 
  companyName,
  fromEmail,   // from webhook payload (flattened)
  toEmail,     // from webhook payload (flattened)
  subject      // from webhook payload (flattened)
} }];

7. Send an Email

Type: n8n-nodes-base.emailSend (v2.1)
fromEmail: ={{ $json.fromEmail ?? "reports@vistanetinc.com" }}
toEmail: ={{ $json.toEmail ?? "<recipient>" }}
subject: ={{ $json.subject ?? "Customer Audit Report" }}
html: ={{ $json.htmlReport }}

Critical Pitfalls

Syntax Error in Set Node: Putting multi-line JS into a Set node manual mode assignment causes invalid syntax [item 0]. Always use Code node for report generation.
Time Entries in n8n: The native connectWisePsa node for timeEntry returns actualHours, not hours. The Code node must aggregate actualHours.
Per-Ticket Hours Must Be Rolled Up: Do NOT use ticket.hours or ticket.actualHours in the report table. Build a lookup map by iterating time entries and summing actualHours per ticket.id.
Company Search Bug: The ConnectWise API /company/companies endpoint may crash with 500 Length cannot be less than zero. There is no client-side workaround — it must be resolved by ConnectWise support.
Node Name References in Code: Ensure $('Node Name') references match the exact display names of the nodes on the canvas.
Activation Required: After updating a workflow via MCP, you must call publish_workflow to activate the new version. Until published, the old version continues executing. Verify by checking versionId vs activeVersionId in get_workflow_details.
GET vs POST Webhook: When switching a webhook from GET to POST, update both the HTTP method in the Webhook node AND the $json accessor in downstream nodes ($json.query?.companyName → $json.body?.companyName). The payload shape changes from query params to JSON body.
POST Webhook Body Nesting: Inside a Code node, POST webhook data is under $('Webhook').first().json.body, not directly on $json. Use $('Webhook').first().json.body || $('Webhook').first().json to handle both cases.
Payload Fallthrough: If a Code node passes values through to a downstream node (e.g., fromEmail, toEmail, subject), the downstream expressions must reference $json.fromEmail, not $json.body.fromEmail, because the Code node flattens the payload into its output JSON.
Timeframe Filtering — Filter the Ticket List, Not Just Hours: When a timeframe is active, filter the displayed ticket list to only tickets with time entries in the range. Do not show all tickets with 0.00 hours. Sort the filtered list by hours descending.

SDK Code Reference

import { workflow, node, trigger, merge } from '@n8n/workflow-sdk';
const webhookTrigger = trigger({ type: 'n8n-nodes-base.webhook', ... });
const searchCompany = node({ type: '@mspcopilot/n8n-nodes-connectwise.connectWisePsa', ... });
const fetchTickets = node({ type: '@mspcopilot/n8n-nodes-connectwise.connectWisePsa', ... });
const fetchTime = node({ type: '@mspcopilot/n8n-nodes-connectwise.connectWisePsa', ... });
const combineData = merge({ ... });
const generateReport = node({ type: 'n8n-nodes-base.code', ... });
export default workflow('cw-audit', 'ConnectWise Customer Audit')
  .add(webhookTrigger).to(searchCompany)
  .to(fetchTickets.to(combineData.input(0)))
  .add(searchCompany).to(fetchTime.to(combineData.input(1)))
  .add(combineData).to(generateReport);

6.2 KiB Raw Permalink Blame History