zeus-skills/ops/connectwise/connectwise-open-tickets-robust/SKILL.md

name, description

name	description
connectwise-open-tickets-robust	Use when API returns old/empty results. Robust ticket fetch.

ConnectWise Open Tickets (Robust)

This skill provides a reliable method for fetching open tickets from the ConnectWise PSA API, specifically addressing the common issue where the API returns the oldest tickets first, potentially masking active tickets in a large database.

Trigger Conditions

• User requests an "Open Tickets" report for themselves or another technician.
• Previous reports returned 0 results despite known open tickets.

Technical Specifications

• API Endpoint: https://cw.vistanetinc.com/v4_6_release/apis/3.0/service/tickets
• Authentication: Use the Authorization: Basic base64(companyid+publickey:privatekey) header and a separate clientId header.
• The "Oldest-First" Pitfall: A simple query for closedFlag=false may return 1,000 results starting from the very first tickets created in the system (many years ago), which may all be closed, leading to an empty result set for newer open tickets.

Execution Workflow

1. Initial Fetch: Attempt a query with closedFlag=false and pageSize=1000.
2. Staleness Check: If the returned tickets are all closedFlag: true or the ID range is very old (e.g., IDs < 20,000), the API is likely returning the oldest records first.
3. Robust Recovery: Pivot to an ID-based query. Fetch tickets with id > [Current_Estimated_Floor] (e.g., id > 110000 or id > 119000).
4. Refined Filtering:
   • Ensure closedFlag == false.
   • Exclude any status starting with > (e.g., >Completed).
   • Filter by the specific owner/identifier requested.
5. Formatting:
   • Generate a professional HTML table.
   • Include: ID, Summary, Status, Company, Date Entered, and SLA Status.
   • SLA Color Coding: Green for isInSla: true, Red for isInSla: false.
6. Delivery: Pipe the raw HTML email (including headers) to himalaya message send -.

Pitfalls & Lessons

• dateEntered is NULL on ticket objects in this deployment (v4_6, seen 2026-09-22) — even on single-ticket GETs. The real entry date lives in the _info block: t['_info']['dateEntered'] (also enteredBy, lastUpdated, updatedBy). Filtering/conditioning on dateEntered in list conditions silently matches nothing.
• slaStatus is a plain string (e.g. "Respond by Fri 09/11 3:10 PM UTC-07"), NOT an object with .name. hoursToSla/dateResponded may also be null. isInSla (bool) is the reliable SLA filter.
• Do NOT rely on closedFlag as the only filter if the database is large; always verify the ID range of the returned results.
• Ordering: If sort=dateEntered DESC is not supported or ignored by the API, ID-based filtering is the only reliable way to reach recent tickets.
• Himalaya Error: Avoid using files for himalaya message send if you encounter index out of bounds panics; use stdin piping instead.

Ticket Notes & Activity API (learned 2026-09-10)

• Notes endpoint: GET /service/tickets/{id}/notes. Field is dateCreated, NOT dateEntered — filtering on dateEntered silently matches nothing. orderBy=dateEntered is rejected 400 InvalidOrderBy on this endpoint; sort client-side.
• Recent activity often lives in time entries, not notes: GET /time/entries?conditions=(ticket/id = {id}) — time entries DO use dateEntered and have member.identifier and notes. Check both when asked "any recent activity".
• Contact-authored notes (email-to-ticket) have no member object — only contact. Skip them (and skip the owner's own notes) when counting other-technician activity.
• Automated "VNI Bot" notes are a member but not a technician — report them separately from human tech activity.
• A recent-activity ticket may still return 0 notes and 0 time entries yet show dateResponded/slaStatus on the ticket detail — ticket detail carries NO lastUpdate object in this deployment (v4_6).

Changing Ticket Owner via API (learned 2026-09-10)

• PATCH /service/tickets/{id} requires a JSON Patch array: [{"op": "replace", "path": "owner/id", "value": <memberId>}] — a plain {"owner": {"id": N}} body fails 400 "not in the expected array format".
• /system/members is 403 for the standard API key (no security permission). To get a member's numeric id, read any of their notes/ticket objects where member: {id, identifier, name} is embedded.
• Always verify assignments by re-reading the ticket: owner.identifier should match the target member.

Scheduling Ticket Work Blocks (learned 2026-09-10)

Used for the cert-renewal playbook: schedule expiry-minus-7-days, 1h, weekend expiries shift to preceding Friday, stagger same-day blocks by 1h.

• Endpoint: POST /schedule/entries. Link to ticket via objectId (query existing with (objectId = {id})).
• Minimal working body: {name, objectId, member:{id}, where:{id}, dateStart, dateEnd, reminder:{id}, status:{id}, type:{id}, span:{id}, hours} (all times UTC). Returns 201 with created entry.
• Known-good values: member <targetMemberId>; where Remote=4; status Firm=2; type id=4 (identifier 'S'); span id=1 ('N' normal block) or 2 ('B' auto-assign marker); reminder 15min=4.
• Auto-assign entries have NO date fields (hours 0.0) — markers only. A dated template entry (has dateStart/dateEnd) is the POST template. Query dated entries with (dateStart > [YYYY-MM-DDT00:00:00Z]).
• Duplicate check first: filter existing entries by objectId for any with dateStart before POSTing; skip if present. A ticket can legitimately need TWO blocks when its summary cert (e.g. GlobalSign Apr 2027) differs from its live cert (e.g. Let's Encrypt Oct 2026) — check what the existing block is for before skipping.
• After scheduling, flip ticket status via JSON Patch: [{"op":"replace","path":"status/id","value":13}] (13 = 'Scheduled' on board 1). Verify by re-reading the ticket AND the schedule entry.
• Working script: scripts/schedule_cert_renewals.py (edit TICKETS list; PATCH_STATUS=True also flips ticket status).