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

name, description

name	description
connectwise-open-tickets-report	Generate an accurate Open Tickets report from ConnectWise PSA targeting the VistaNet Help Desk board.

ConnectWise Open Tickets Report

This skill defines the precise requirements for generating the "Open Tickets" report from the ConnectWise PSA API.

Trigger Conditions

• User requests the "Open Tickets Report".
• A cron job is scheduled to monitor open tickets.

Technical Specifications

• API Endpoint: https://cw.vistanetinc.com/v4_6_release/apis/3.0/service/tickets
• Authentication: Credentials (Company ID, Public Key, Private Key, and Client ID) must be read from ~/.secrets/connectwise at runtime.
  • Auth Header Pattern: The API typically requires Authorization: Basic base64(clientId:privateKey). Ensure the clientId is exactly as provided (check for missing characters in environment variables vs secret files) and that Company-Id is passed as a separate header.
  • Critical Reference Syntax: Use forward-slash notation for sub-references in the URL (e.g., owner/identifier='SBrown' and board/id=1). Do NOT use dot-notation (owner.identifier), as this returns a 400 Bad Request.
  • Client ID: Use the value found in connectwise_reference.md (or /home/tech/connectwise_reference.md). The API requires this as a separate header.
  • Auth String Format: Use Basic base64(companyid+publickey:privatekey) for the Authorization header. The literal + between companyid and publickey is the separator and must be present. If the public key itself begins with +, do not add an extra separator (verify the concatenated result does not produce ++).
  • Client ID Header: The API requires a clientId header (distinct from the auth string). Refer to connectwise_reference.md for the current Client ID. Ensure the secret file includes the CLIENT_ID if required by the runtime endpoint.
• Conditions Logic:
  • Authentication: Credentials (Company ID, Public Key, Private Key, and Client ID) must be read from ~/.secrets/connectwise at runtime.
    • Auth Header Pattern: The API typically requires Authorization: Basic base64(clientId:privateKey). Ensure the clientId is exactly as provided (check for missing characters in environment variables vs secret files) and that Company-Id is passed as a separate header.
    • Critical Reference Syntax: Use forward-slash notation for sub-references in the URL (e.g., owner/identifier='SBrown' and board/id=1). Do NOT use dot-notation (owner.identifier), as this returns a 400 Bad Request.
  • Standard Filter: closedFlag=false must be included to isolate open tickets.
  • Status Filter: Exclude tickets where status starts with > (e.g., >Completed).
  • status != '>Completed' (Filter in Python or URL).

Delivery & Formatting

• Himalaya CLI Syntax: The himalaya send command does not exist. Use himalaya message send and provide the raw email (including headers) via stdin or a file.
  • Critical Pitfall (Himalaya): himalaya message send frequently panics (index out of bounds) when processing raw email files. To avoid this, pipe the raw email string (including From:, To:, and Subject:) directly to the command via stdin.
  • Format: Professional formatted text table or HTML.
  • Notification: Report results to the origin chat upon completion.

Execution Workflow

1. Construct the API request using closedFlag=false and the owner/identifier condition.
2. Fetch tickets with a sufficiently large pageSize (e.g., 1000).
3. Perform post-filtering in Python for criteria that cannot be reliably expressed in the API conditions (e.g., specific status name patterns like NOT LIKE '>Completed').
4. For SLA evaluation, inspect isInSla, respondMinutes, resPlanMinutes, resolveMinutes, and slaStatus on each ticket record.
5. For ticket status updates, use JSON Patch format ([{"op": "replace", "path": "/status/id", "value": <id>}]) against PATCH /service/tickets/{id}.
6. Format the output into a clean table containing: Ticket ID, Summary, Status, Company, and Date Entered.

Ticket Updates

• JSON Patch Format Required: CW v3 API uses RFC 6902 JSON Patch for PATCH requests. Do NOT send a flat JSON body.
  • Correct: [{"op": "replace", "path": "/status/id", "value": 425}]
  • Wrong: {"status": {"id": 425}} (returns 400)
• Status IDs are Board-Scoped: The same status name may have different IDs on different boards. Always look up the status on the ticket's actual board (/service/boards/{boardId}/statuses) before patching.
• Self-Healed Status: On the "Managed Service Alerts" board (ID 6), status "Self-Healed" = ID 425. Use this for bulk-closing stale RMM alerts.

SLA Evaluation

Key ticket fields for SLA analysis:

• isInSla (bool): Whether the ticket is currently within SLA
• slaStatus (string): Human-readable SLA text (e.g., "Respond by Tue 04/21 3:37 PM UTC-07")
• respondMinutes (int): Minutes since ticket entered "respond" SLA window
• resPlanMinutes (int): Minutes since ticket entered "resplan" SLA window
• resolveMinutes (int): Minutes since ticket entered "resolve" SLA window
• escalationLevel (int): Escalation tier (higher = more critical)
• priority.level: Critical/High/Medium/Low
• severity and impact: String values (Critical/High/Medium/Low)

See references/sla_evaluation.md for a full SLA scoring recipe.

RMM Alert Noise Pattern

• The "Managed Service Alerts" board (ID 6) auto-generates Priority 1 tickets from Sophos Central RMM.
• Typical patterns: "Firewall has not contacted Sophos Central", "Gateway 'X' is Down", "Real time protection disabled".
• These often create hundreds of unassigned P1s that are never triaged. A periodic bulk review (status "new from RMM" >7 days old → "Self-Healed") is standard maintenance.
• The single malware detection ticket (C2/Generic-A) should NEVER be auto-closed without human review.

Execution Workflow

1. Construct the API request using closedFlag=false and the owner/identifier condition.
2. Fetch tickets with a sufficiently large pageSize (e.g., 1000).
3. Perform post-filtering in Python for criteria that cannot be reliably expressed in the API conditions (e.g., specific status name patterns like NOT LIKE '>Completed').
4. For SLA evaluation, inspect isInSla, respondMinutes, resPlanMinutes, resolveMinutes, and slaStatus on each ticket record.
5. For ticket status updates, use JSON Patch format ([{"op": "replace", "path": "/status/id", "value": <id>}]) against PATCH /service/tickets/{id}.
6. Format the output into a clean table containing: Ticket ID, Summary, Status, Company, and Date Entered.

Ticket Updates

• JSON Patch Format Required: CW v3 API uses RFC 6902 JSON Patch for PATCH requests. Do NOT send a flat JSON body.
  • Correct: [{"op": "replace", "path": "/status/id", "value": 425}]
  • Wrong: {"status": {"id": 425}} (returns 400)
• Status IDs are Board-Scoped: The same status name may have different IDs on different boards. Always look up the status on the ticket's actual board (/service/boards/{boardId}/statuses) before patching.
• Self-Healed Status: On the "Managed Service Alerts" board (ID 6), status "Self-Healed" = ID 425. Use this for bulk-closing stale RMM alerts.

SLA Evaluation

Key ticket fields for SLA analysis:

• isInSla (bool): Whether the ticket is currently within SLA
• slaStatus (string): Human-readable SLA text (e.g., "Respond by Tue 04/21 3:37 PM UTC-07")
• respondMinutes (int): Minutes since ticket entered "respond" SLA window
• resPlanMinutes (int): Minutes since ticket entered "resplan" SLA window
• resolveMinutes (int): Minutes since ticket entered "resolve" SLA window
• escalationLevel (int): Escalation tier (higher = more critical)
• priority.level: Critical/High/Medium/Low
• severity and impact: String values (Critical/High/Medium/Low)

See references/sla_evaluation.md for a full SLA scoring recipe.

RMM Alert Noise Pattern

• The "Managed Service Alerts" board (ID 6) auto-generates Priority 1 tickets from Sophos Central RMM.
• Typical patterns: "Firewall has not contacted Sophos Central", "Gateway 'X' is Down", "Real time protection disabled".
• These often create hundreds of unassigned P1s that are never triaged. A periodic bulk review (status "new from RMM" >7 days old → "Self-Healed") is standard maintenance.
• The single malware detection ticket (C2/Generic-A) should NEVER be auto-closed without human review.

Pitfalls & Lessons

• URL Condition Syntax: Use forward-slash notation for sub-references in URL conditions (e.g., owner/identifier='SBrown', board/id=1) rather than dot notation. Dot notation often triggers 400 Bad Request errors.
• Date Source: Top-level dateEntered on the ticket record may be absent or unreliable. Use _info.dateEntered from the embedded _info object — this is the canonical timestamp.
• Himalaya CLI Syntax: The himalaya send command does not exist. Use himalaya message send and provide the raw email (including headers) via stdin or a file.
  • Critical Pitfall (Himalaya): himalaya message send frequently panics (index out of bounds) when processing raw email files. To avoid this, pipe the raw email string (including From:, To:, and Subject:) directly to the command via stdin.
• 500 Error "Length cannot be less than zero": If this error occurs, it typically indicates an authentication failure or an incorrectly formatted request that bypassed the 401 layer. Verify that the Authorization header follows the companyid+publickey:privatekey format and a valid clientId is present.
• Authorization Header: Ensure the Base64 string follows the companyid+publickey:privatekey format exactly, where + is the literal separator. If the public key itself begins with +, do not add an extra separator. Simple companyid:publickey:privatekey (colon between all three) will result in a 401 Unauthorized error.
• Client ID: The clientId must be passed as a separate header, not just part of the auth string.
• Pandas Dependency: The helper script scripts/generate_open_tickets.py depends on pandas. In cron/headless environments where pandas is missing, implement manual HTML table generation using standard Python lists to avoid dependency errors.
• RTF Report Extraction: When processing Nexpose/Qualys RTF reports, standard tools (striprtf, unrtf, catdoc) fail because these files contain embedded binary objects (\shp, \pict, \bin). Use strings -n 15 plus targeted grep, or a custom byte-level parser that skips destination groups. See the document-extraction-workflows skill for a reusable Python extractor.
• SMTP for Rich Email: When himalaya cannot handle HTML attachments or complex MIME, fall back to Python smtplib with email.mime.multipart. Parse credentials from ~/.config/himalaya/config.toml if available.