114 lines
11 KiB
Markdown
114 lines
11 KiB
Markdown
---
|
|
name: connectwise-open-tickets-report
|
|
description: 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. |