zeus-skills/ops/connectwise/connectwise-reporting/SKILL.md

name, description

name	description
connectwise-reporting	Workflows for generating and automating ConnectWise PSA ticket reports via API and email.

ConnectWise Ticket Reporting

This skill governs the extraction of ticket data from the ConnectWise PSA API and the delivery of formatted reports to technicians.

Standard Workflow

1. Authentication: Use Basic Auth (Base64 encoded companyid+publickey:privatekey) and a valid clientId.
2. Data Extraction: Use the /service/tickets endpoint with specific conditions (e.g., closedFlag=false and board/id=1) to isolate open tickets.
3. Filtering: Filter results by owner/identifier to ensure technicians only see their own tickets.
4. Formatting: Generate an Outlook-optimized HTML table with inline CSS for status-based color coding.
5. Delivery: Deliver via the Himalaya CLI using a temporary file for the email body.

Customer-Level Aggregation Workflow

When the user asks for "customers with open tickets" rather than a raw ticket list, aggregate by company.id and enrich each record with primary contact details.

Steps:

1. Query /service/tickets?conditions=board/id=1 and closedFlag=false and dateEntered>'...' with pageSize=1000.
2. Group by company.id:
   • Count open tickets per company
   • Track latest _info.dateEntered
3. Post-filter: Skip statuses starting with > (e.g., >Completed) to exclude resolved-but-not-closed tickets.
4. For each unique company, call /company/companies/{id}:
   • Extract defaultContact (id + name)
   • Extract invoiceToEmailAddress as fallback
5. Call /company/contacts/{id} for the default contact:
   • Walk communicationItems
   • Select item where type.name == 'Email' and defaultFlag == true
   • Use value as the primary contact email
6. Sort by company name. Output: Company | Open Tickets | Primary Contact | Email.

Board IDs:

• VistaNet Help Desk = 1
• VistaNet Management = 25
• Managed Service Alerts = 6

Date Range Condition:

• dateEntered>'YYYY-MM-DDTHH:MM:SSZ' (UTC) works for any time window (past year, two years, etc.).

Note: Some companies have no default contact email in CW (e.g., BJ LaRossa, Far West Rice Inc.). Display N/A gracefully.\n\n## Implementation Patterns\n- VistaNet Environment: Use the internal base URL https://cw.vistanetinc.com/v4_6_release/apis/3.0. Public endpoints like api-na.connectwise.com are not reachable from this environment.\n- The Master Script Pattern: Avoid creating individual scripts for every user. Use a single master script that accepts tech_identifier and tech_email as command-line arguments.

• Cron Execution: Because the Hermes cron scheduler may not support arguments in the script field for no_agent jobs, use launcher shell scripts (e.g., run_techname_report.sh) to call the master script with the necessary arguments.
• n8n Execution Data Extraction: When retrieving data from mcp__n8n__get_execution for workflows that process many tickets (e.g., fetching all notes), the total payload often exceeds MCP limits and causes truncation. To avoid this, use the nodeNames parameter to target only the final result nodes (e.g., Aggregate or Format Summary).
• n8n Data Parsing: Be aware that n8n node outputs for ConnectWise often follow a main: [[{json: ...}], [{json: ...}]] structure (a list of lists), requiring an extra layer of iteration when parsing in Python.
• n8n Native Node vs HTTP Request: The native @mspcopilot/n8n-nodes-connectwise.connectWisePsa node handles credential management and some query building automatically. Prefer it for standard CRUD (Get Company, Get Many Tickets, Get Many Time Entries) when the API is healthy. Fall back to raw HTTP Request only when the native node lacks a needed operation or when debugging credential issues.
• n8n Complex Report Generation: Do NOT use a Set node for multi-line JavaScript logic (e.g., building HTML reports from aggregated data). Set nodes in manual mode only support simple field mapping expressions. Use a Code node with mode: 'runOnceForAllItems' for any logic requiring variables, loops, or string concatenation.
• n8n POST Webhook Body Nesting: In n8n, POST webhooks expose the JSON body under $json.body, not directly on $json. When a Code node passes through values from a POST webhook to downstream nodes, downstream expressions must reference $json.fromEmail, not $json.body.fromEmail, because the Code node flattens its output. However, inside the Code node itself, access the payload as $('Webhook').first().json.body || $('Webhook').first().json.
• n8n Workflow: Customer Audit Pattern: To build a reusable customer audit workflow in n8n: (1) Webhook trigger accepts companyName (and optional toEmail, fromEmail, subject, startDate, endDate), (2) native connectWisePsa node searches for company by name, (3) parallel branches fetch ticket (getAll) and timeEntry (getAll) using company/id={{ $json.id }}, (4) Merge node combines results, (5) Code node generates the final HTML report using actualHours for hour aggregation and optional timeStart timeframe filtering, (6) Email node sends parameterized report. See references/n8n-customer-audit-workflow.md for the full SDK code.
• n8n Activation Requirement: After updating a workflow via MCP (update_workflow), you MUST call publish_workflow to activate the new version. Until published, the old version continues executing.
• Cross-Company Time Entry Aggregation: To find "top N companies by billed hours" for a date range, do NOT query the company endpoint. Query /time/entries?conditions=timeStart>[YYYY-MM-DDTHH:MM:SSZ] with pageSize=1000 across pages, then group by company.id in Python. Sum actualHours per company. Derive the lead tech by tracking member.name hours per company and picking whoever logged the most. The company record has territoryManager (sales/account manager) but no built-in leadTech or primaryEngineer field — the only meaningful lead tech is the person with the most billable hours. See scripts/top-companies-by-hours.py.
• No leadTech Field on Company Record: ConnectWise company records do NOT contain a field that stores the assigned lead technician. territoryManager is the account/sales manager. customFields contains phone brand, install date, etc. — none store lead tech. Do not waste time looking for it.

Pitfalls & Lessons Learned

"Length cannot be less than zero" — Server-Side API Crash

A 500 Internal Server Error with the message "Length cannot be less than zero. Parameter name: length" is a known, low-level .NET Substring() exception in ConnectWise's own code.

• It is not your fault. The error is triggered by a malformed record on the server side (e.g., a string slice operation receiving -1 from a failed character search).
• It is not bypassable client-side. Removing pageSize, conditions, or any parameter does not fix it — even the barest /service/tickets request fails.
• It is likely data-dependent. One corrupted ticket in the dataset causes the API to crash when generating the response list.
• Workaround: Report to ConnectWise support. For urgent access, query for a specific known-valid Ticket ID instead of the general list endpoint.
• Reference: Stack Overflow and ConnectWise support articles confirm this is a recurring server-side bug.

Auth String Format

The Basic Auth string must be exactly: companyid+publickey:privatekey with a literal + between company ID and public key.

• Correct: VNI+<PUBLIC_KEY>:<PRIVATE_KEY>
• Incorrect: VNI++<PUBLIC_KEY>:<PRIVATE_KEY> (double +)

Client ID Requirement

If you receive a 401 Unauthorized with the message "Incorrect or disabled client ID", it means the clientId header is missing or invalid. This is a separate requirement from Basic Auth. Check references/raw-api-authentication.md for the current valid clientId.

Ticket Updates (PUT vs PATCH)

The /service/tickets/{id} endpoint does not support application/json-patch+json. To update a ticket (e.g., changing the owner), you must:

1. Fetch the current ticket object via GET.
2. Modify the desired fields in the JSON object.
3. Perform a full PUT request with Content-Type: application/json.

Credential File Format

The ConnectWise credential file at ~/.secrets/connectwise is a multi-line .env file, not a raw auth string.

• File contents: CW_COMPANY_ID=VNI\nCW_PUBLIC_KEY=+rtDj...\nCW_PRIVATE_KEY=HcCOK...
• WRONG: Base64-encoding the entire file text as the Authorization header.
• RIGHT: Parse the key=value pairs, construct companyid+publickey:privatekey from the extracted values, then Base64-encode that string. The public key already starts with +, so concatenation produces VNI+rtDj... — do not add a second plus.

Time Entries Endpoint & Field

• WRONG endpoint: /service/time returns 403 Forbidden.
• RIGHT endpoint: /time/entries (e.g., GET /time/entries?conditions=company/id={id}&pageSize=1000).
• WRONG hour field: hours is None or missing.
• RIGHT hour field: actualHours.
• DateTime condition syntax: Use square brackets for date values in query conditions: dateEntered>[2025-08-11T02:47:43Z]. Quoted strings ('...') cause a 400 ApiFindCondition error expecting a datetime value.

Schedule Entries Mirror Bug

The /schedule/entries endpoint has a severe data-mirroring bug that affects both general list queries and ticket-linked queries.

Observed behavior (confirmed in this session):

• GET /schedule/entries?conditions=member/id=<id> returns entries from 2011–2012, regardless of date filters.
• Adding date range conditions (e.g., dateStart>[2026-08-01T00:00:00Z]) does not help; the endpoint either returns the same stale 2011 data or an empty result.
• Querying via objectId=<ticket_id> also fails to return current data — it yielded empty results for 2026 ranges in testing.
• The pageSize parameter has no effect on the date filter behavior.

Confirmed workarounds (none fully reliable):

• There is no known client-side workaround for retrieving current/future schedule entries via the REST API.
• The bug is server-side and data-dependent. The API appears to be returning a static mirror of ancient historical entries.
• For urgent calendar access, use the ConnectWise web UI directly or export via the reporting module.

What NOT to try:

• Do not assume a ticket-linked objectId query will return current data — it does not in practice.
• Do not rely on dateStart/dateEnd conditions on /schedule/entries to filter to current dates.

agreementrecap Date-Window Off-By-One (n8n)

/finance/agreementrecap returns 0 rows with a < [{{ $today.plus(30,'Days') }}]) upper bound when agreements bill on the same day of next month (e.g. all monthly recaps due Nov 1 queried on Oct 1 = 31 days out, excluded by < Oct 31). Syntax is fine — each clause validates individually; lastInvoiceAmount is a string field so the quoted '0.00' literal is correct (numeric literal there → 400 ApiFindCondition).

• Fix: < [{{ $today.plus(31,'Days') }}] (exclusive) or <= [{{ $today.plus(30,'Days') }}]. Verified live: original → 0 rows, fixed → 154.
• The /finance/agreementrecap/count endpoint 403s with the reporting key; pull the list with pageSize=1000 and count client-side instead.

Reference File

The endpoint reference lives at Documents/cwendpoints.json (Nextcloud) or references/api_details.md in this skill; a current OpenAPI copy also sits at /home/tech/.hermes/references/connectwise/cwendpoints.json (schemas include AgreementRecap field types).

Delivery Loops

Be cautious of the deliver: "origin" setting in cron jobs. If a script prints "Success" to stdout, Hermes will send that log as a second, plain-text message to the user. Set deliver: "local" to suppress these notifications while still allowing the script to send its own professional email.

HTML Rendering

Outlook requires strict inline CSS. Always apply styles directly to <tr> and <td> elements; avoid relying on <style> blocks in the header.

Search & Alert Patterns\nWhen searching for security incidents or "alerts" across specific customers, do not rely on a single keyword. Use a broad set of triggers: cyber, attack, hack, alert, intrusion, malware, ransom, unauthorized, vpn, ot, scada, plc, down, unexpected, security, failure.\n\nImplementation: Iterate through a list of target Company IDs and filter summaries using any of these keywords to identify potential security events in a high-noise environment.

Per-Ticket Hours from Time Entries

When a report displays per-ticket hours alongside ticket metadata (e.g., a "Recent Tickets" table with an Hours column), do NOT use ticket.hours or ticket.actualHours directly. ConnectWise tickets do not reliably carry a summed hours field.

Correct approach:

1. Fetch time entries separately via /time/entries?conditions=company/id={id}.
2. Build a lookup map in the Code node: ticketId -> sum(actualHours).
3. In the ticket loop, reference the map: ticketHoursMap[ticket.id] || 0.

Example:

const ticketHoursMap = {};
for (const entry of timeEntries) {
  const te = entry.json;
  const ticketId = te.ticket?.id;
  if (ticketId != null) {
    ticketHoursMap[ticketId] = (ticketHoursMap[ticketId] || 0) + (te.actualHours || 0);
  }
}
// In ticket row: ticketHoursMap[ticket.id] || 0

Primary Contact Email Lookup

To find a company's primary contact email in CW, do not rely on company.email — that field does not exist. The correct path is:

1. GET /company/companies/{id} → defaultContact.id
2. GET /company/contacts/{id} → communicationItems
3. Select item where type.name == 'Email' and defaultFlag == true → value

invoiceToEmailAddress on the company record is a usable fallback but often points to billing/AP, not the primary operational contact.

Inactive Customer Audit (Gap Analysis)

To identify active customers who have had no recorded time entries for a specific period (e.g., 90 days):

1. Fetch all companies with status/name='Active'.
2. Fetch all time entries since the target date using timeStart>[YYYY-MM-DDTHH:MM:SSZ].
3. Construct a set of Company IDs from the time entries.
4. Filter the active companies list to exclude those in the time entry set.
5. Sort the resulting gap list by company name for the final report.

Scheduled n8n AR Estimate Job (tallerhawk instance)

Workflow "Connectwise Estimate AR" (work n8n instance, id <workflowId>) runs weekly Thu ~18:00 UTC and appends one row per source to the n8n Data Table "Connectwise Pending AR" (id <tableId>, project <projectId>), columns Source/amount, Source ∈ {Aggreements (sic), Time, Parts} — weekly pending-AR estimate snapshots. UPDATE 2026-10-19: user enabled availableInMCP=true, so get_workflow_details now returns full JSON. Confirmed logic: cron 0 11 * * THU (instance TZ=Pacific → 18:00 UTC) + GET webhook /webhook/<webhook-path>. Sources: Aggreements = /finance/agreementrecap ACTIVE, nextInvoiceAmount<>0, nextInvoiceDate in [today, +31d exclusive), lastInvoiceAmount<>'0.00' → sum nextInvoiceAmount; Time = open tickets (closedflag=false AND status/id<>80) → per-ticket time entries → billableOption=Billable AND ticketStatus notContains Closed → sum extendedInvoiceAmount; Parts = same tickets → /procurement/products → billable → sum extPrice. All three write rows to the data table and email "Estimated Pending AR" to the recipient list. Time/Parts are open-ticket-keyed ONLY — non-ticket work is not counted. Actual billed-but-unpaid AR comes from /finance/invoices?conditions=balance > 0 (Invoice has a live balance field); filter invoice date ≥ current year — older "Closed - Mailed/Emailed" invoices carry stale residual balances (decades of residue ~$7.7M that is NOT real AR; billing handoff status is "Billed to AccountEdge").

Verification

• Verify that the API returns a list of tickets and not a dictionary with a tickets key (handle both cases in the parser).
• Confirm that the HIMALAYA_BIN path is absolute and the temporary email file is uniquely named per recipient to avoid race conditions.