autotask-dwh-mcp-server

Run a read-only SQL query

query

Read-onlyIdempotent

Run a read-only SQL statement against the Autotask Report Data Warehouse to get structured columns and rows for finance, contracts, projects, time, and tickets reporting.

Instructions

Execute EXACTLY ONE read-only SQL statement against the Autotask Report Data Warehouse and return its columns and rows (structuredContent plus a compact text table). The statement MUST be a single SELECT or WITH (CTE) query. The read-only guard REJECTS everything else: INSERT, UPDATE, DELETE, MERGE, DROP, ALTER, CREATE, TRUNCATE, EXEC/EXECUTE, GRANT, REVOKE, SELECT ... INTO, stored/extended procedures (sp_/xp_), and any second statement (a statement-separating semicolon). Rows are hard-capped by max_rows (ceiling 500). READ the resource warehouse://guide before writing queries - this warehouse uses non-obvious names. Key traps: there is NO wh_ticket / wh_time_entry / wh_ticket_note view; tickets AND project tasks share wh_task, where a ticket has project_id IS NULL and a project task has project_id IS NOT NULL; join hours as wh_time_item.time_item_id = wh_time_subitem.time_item_id (task_id lives only on wh_time_item); money columns (wh_posted_overall, wh_billing_item) come back as decimal strings. Almost every *_id column is a foreign key: resolve the IDs that matter to human-readable names by JOINing the matching lookup view (see the 'ID RESOLUTION' map in warehouse://guide) and return names, not raw IDs, unless the user explicitly asks for IDs. If the result comes back with truncated=true it is INCOMPLETE (more rows matched than the cap) - narrow with WHERE/GROUP BY, aggregate, or raise max_rows; do not treat the returned count as the total. Data freshness: the warehouse is a DAILY full snapshot, not a live system - values can be up to a day old; for time-critical questions call last_load and state the load time, or use the live Autotask source instead of presenting a stale figure as current. For business terms (revenue, cost, margin, open ticket, billed/worked hours, utilization, active contract/employee) use the canonical definition from the BUSINESS GLOSSARY in warehouse://guide instead of interpreting them freely, and state the definition and time window you used.

Input Schema

TableJSON Schema

Name	Required	Description	Default
`sql`	Yes	One read-only SELECT or WITH statement. Example: "SELECT TOP 10 account_id, account_name FROM wh_account WHERE is_active = 1". Tickets example: "SELECT TOP 20 task_number, account_id, task_status_id FROM wh_task WHERE project_id IS NULL". No writes, no second statement.
`max_rows`	No	Maximum rows to return; capped at the server ceiling MSSQL_MAX_ROWS (500). Example: 25. Omit to use the ceiling.

Output Schema

TableJSON Schema

Name	Required	Description
`rows`	Yes
`columns`	Yes
`row_cap`	Yes	The cap applied to this query (min of max_rows and the server ceiling).
`truncated`	Yes	True if more rows matched than were returned; the result is incomplete.
`returned_rows`	Yes	Number of rows actually returned (after the cap).
`any_cell_truncated`	Yes

Schema Changelog

Changes observed during successful MCP inspections.

First observedv0.1.1Aug 31, 2026

TDQS

A4.8/5.0

Behavior5/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint/idempotentHint true and destructiveHint false, and the description adds substantial behavioral detail: the read-only guard rejects a long list of statement types, max_rows is hard-capped at 500, truncated=true signals incomplete results, the warehouse is a daily snapshot, and money columns return as decimal strings. Nothing contradicts the annotations, and the added context is critical for correct use.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is long but every sentence carries operational weight—there is no filler. It front-loads the core purpose and constraint, then layers on traps, freshness, and glossary guidance in a logical order. This density is appropriate for the tool's complexity.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a SQL execution tool with an output schema, constraints, and many warehouse-specific pitfalls, this definition is exceptionally complete: it covers statement validation, row caps, truncation semantics, data freshness, ID resolution, and business-term definitions. An agent can use this tool correctly with minimal need for additional documentation beyond the referenced guide.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters5/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

With 100% schema coverage the baseline is 3, but the description heavily enriches both parameters: it specifies exactly which SQL constructs are allowed/rejected, explains how to respond to truncated=true (narrow query, aggregate, or raise max_rows), and warns about non-obvious ID columns and join keys. This adds meaning far beyond the schema's one-line descriptions.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description opens with a specific verb and resource: 'Execute EXACTLY ONE read-only SQL statement against the Autotask Report Data Warehouse and return its columns and rows.' It clearly distinguishes the tool from siblings like list_views and describe_view by scoping it to query execution rather than schema exploration. The read-only and single-statement constraints further pin down the purpose.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description gives clear context for use (read-only SQL against the warehouse) and explicitly names an alternative path for freshness: 'call last_load and state the load time, or use the live Autotask source instead.' It also directs the agent to read warehouse://guide before writing queries. However, it does not explicitly contrast against list_views/describe_view for schema discovery, so it stops short of a full when-not-to-use map.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Deploy Server