halopsa_build_query
Construct SQL queries for HaloPSA data with proper syntax. Build SELECT queries with WHERE conditions, ORDER BY, and LIMIT clauses without writing raw SQL code.
Instructions
Build a basic SQL query for HaloPSA with a helper that ensures proper syntax. Useful for constructing simple SELECT queries with WHERE conditions, ORDER BY, and LIMIT clauses without writing raw SQL.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| tableName | Yes | Table to query from (e.g., FAULTS, USERS, SITE) | |
| columns | No | Columns to select (optional, defaults to all). Example: ["Faultid", "Symptom", "Status"] | |
| conditions | No | WHERE conditions as key-value pairs. Example: {"Status": 1, "Priority": 3} | |
| orderBy | No | Column to order results by. Example: "datereported DESC" | |
| limit | No | Maximum number of rows to return. Example: 10 |
Implementation Reference
- src/index.ts:461-514 (handler)Handler for 'halopsa_build_query' tool. Parses input parameters (tableName, columns, conditions, orderBy, limit) and constructs a SQL SELECT query string with proper escaping for strings and NULL handling. Returns the generated query and input components as JSON.
case 'halopsa_build_query': { const { tableName, columns, conditions, orderBy, limit } = args as any; if (!tableName) { throw new Error('Table name is required'); } let query = 'SELECT '; if (limit && typeof limit === 'number') { query += `TOP ${limit} `; } if (columns && Array.isArray(columns) && columns.length > 0) { query += columns.join(', '); } else { query += '*'; } query += ` FROM ${tableName}`; if (conditions && typeof conditions === 'object' && Object.keys(conditions).length > 0) { const whereClauses = Object.entries(conditions).map(([key, value]) => { if (typeof value === 'string') { const escapedValue = value.replace(/'/g, "''"); return `${key} = '${escapedValue}'`; } else if (value === null) { return `${key} IS NULL`; } else { return `${key} = ${value}`; } }); query += ' WHERE ' + whereClauses.join(' AND '); } if (orderBy) { query += ` ORDER BY ${orderBy}`; } return { content: [{ type: 'text', text: JSON.stringify({ generatedQuery: query, components: { table: tableName, columns: columns || ['*'], conditions: conditions || {}, orderBy: orderBy || 'none', limit: limit || 'none' } }, null, 2) }] }; } - src/index.ts:107-137 (schema)Tool schema definition including name, description, and inputSchema with properties for tableName (required), columns (array), conditions (object), orderBy (string), limit (number).
{ name: 'halopsa_build_query', description: 'Build a basic SQL query for HaloPSA with a helper that ensures proper syntax. Useful for constructing simple SELECT queries with WHERE conditions, ORDER BY, and LIMIT clauses without writing raw SQL.', inputSchema: { type: 'object', properties: { tableName: { type: 'string', description: 'Table to query from (e.g., FAULTS, USERS, SITE)' }, columns: { type: 'array', items: { type: 'string' }, description: 'Columns to select (optional, defaults to all). Example: ["Faultid", "Symptom", "Status"]' }, conditions: { type: 'object', description: 'WHERE conditions as key-value pairs. Example: {"Status": 1, "Priority": 3}' }, orderBy: { type: 'string', description: 'Column to order results by. Example: "datereported DESC"' }, limit: { type: 'number', description: 'Maximum number of rows to return. Example: 10' } }, required: ['tableName'] } }, - src/index.ts:42-276 (registration)The tool is registered in the tools array used by ListToolsRequestHandler to advertise available tools.
const tools: Tool[] = [ { name: 'halopsa_list_tables', description: 'List all available tables in the HaloPSA database by querying sys.tables. Returns a complete list of all tables that can be queried. Use this to discover what data is available before writing queries.', inputSchema: { type: 'object', properties: { filter: { type: 'string', description: 'Optional filter to search for specific tables. Example: "fault", "user", "ticket"' } } } }, { name: 'halopsa_list_columns', description: 'List columns for a specific table in the HaloPSA database using information_schema.columns. Returns detailed column information including data types, max length, and nullable status.', inputSchema: { type: 'object', properties: { tableName: { type: 'string', description: 'Table name to get columns for. Example: FAULTS, USERS, SITE' }, columnFilter: { type: 'string', description: 'Optional filter to search for specific column names. Example: "id", "name", "date"' } }, required: ['tableName'] } }, { name: 'halopsa_query', description: 'Execute a SQL query against HaloPSA reporting API. Use this to retrieve data from any HaloPSA table including tickets (FAULTS), users (USERS), sites (SITE), actions (ACTIONS), request types (REQUESTTYPE), and more. Returns the full report response with data rows, column metadata, and available filters.', inputSchema: { type: 'object', properties: { sql: { type: 'string', description: 'SQL query to execute against HaloPSA database. Supports standard SQL syntax including SELECT, JOIN, WHERE, ORDER BY, GROUP BY, etc. Example: SELECT * FROM FAULTS WHERE Status = 1' }, loadReportOnly: { type: 'boolean', description: 'Whether to load report data only (default: true)', default: true } }, required: ['sql'] } }, { name: 'halopsa_table_info', description: 'Get detailed information about a specific HaloPSA table including all columns, data types, nullable fields, and relationship suggestions. Use this to understand table structure before writing queries.', inputSchema: { type: 'object', properties: { tableName: { type: 'string', description: 'Name of the table to inspect. Example: FAULTS, USERS, SITE, ACTIONS, REQUESTTYPE' } }, required: ['tableName'] } }, { name: 'halopsa_build_query', description: 'Build a basic SQL query for HaloPSA with a helper that ensures proper syntax. Useful for constructing simple SELECT queries with WHERE conditions, ORDER BY, and LIMIT clauses without writing raw SQL.', inputSchema: { type: 'object', properties: { tableName: { type: 'string', description: 'Table to query from (e.g., FAULTS, USERS, SITE)' }, columns: { type: 'array', items: { type: 'string' }, description: 'Columns to select (optional, defaults to all). Example: ["Faultid", "Symptom", "Status"]' }, conditions: { type: 'object', description: 'WHERE conditions as key-value pairs. Example: {"Status": 1, "Priority": 3}' }, orderBy: { type: 'string', description: 'Column to order results by. Example: "datereported DESC"' }, limit: { type: 'number', description: 'Maximum number of rows to return. Example: 10' } }, required: ['tableName'] } }, { name: 'halopsa_list_api_endpoints', description: 'List all API endpoints with their paths, methods, and summaries. Use this first to discover available endpoints, then use halopsa_get_api_endpoint_details for full details. Supports pagination.', inputSchema: { type: 'object', properties: { category: { type: 'string', description: 'Optional category filter (e.g., "Tickets", "Actions", "Clients", "Sites")' }, limit: { type: 'number', description: 'Maximum number of endpoints to return (default: 100)', default: 100 }, skip: { type: 'number', description: 'Number of endpoints to skip for pagination (default: 0)', default: 0 } } } }, { name: 'halopsa_get_api_endpoint_details', description: 'Get complete details for specific API endpoints including parameters, request/response schemas, and examples. Use after finding endpoints with halopsa_list_api_endpoints.', inputSchema: { type: 'object', properties: { pathPattern: { type: 'string', description: 'Path pattern to match endpoints (e.g., "ticket", "action", "client", "agent")' }, summaryOnly: { type: 'boolean', description: 'Return only basic endpoint information (path, methods, summary) without detailed schemas - ideal for quick API exploration', default: false }, includeSchemas: { type: 'boolean', description: 'Include detailed request/response schemas (default: true, set to false to significantly reduce response size)', default: true }, maxEndpoints: { type: 'number', description: 'Maximum number of endpoints to return (default: 10, max: 50) - helps manage response size', default: 10 }, includeExamples: { type: 'boolean', description: 'Include request/response examples (default: false to keep responses smaller)', default: false } }, required: ['pathPattern'] } }, { name: 'halopsa_search_api_endpoints', description: 'Search for API endpoints by keywords. Returns matching endpoints with basic info. Use halopsa_get_api_endpoint_details for full details of specific endpoints. Supports pagination.', inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'Search query to find endpoints (searches in paths, summaries, descriptions, and tags)' }, limit: { type: 'number', description: 'Maximum number of results to return (default: 50)', default: 50 }, skip: { type: 'number', description: 'Number of results to skip for pagination (default: 0)', default: 0 } }, required: ['query'] } }, { name: 'halopsa_get_api_schemas', description: 'Get API schemas/models from the swagger definition. Shows the structure of request/response objects used by the API endpoints. Supports pagination.', inputSchema: { type: 'object', properties: { schemaPattern: { type: 'string', description: 'Optional pattern to filter schemas by name (e.g., "Ticket", "Action", "Client")' }, limit: { type: 'number', description: 'Maximum number of schemas to return (default: 50)', default: 50 }, skip: { type: 'number', description: 'Number of matching schemas to skip for pagination (default: 0)', default: 0 }, listNames: { type: 'boolean', description: 'Include list of all matching schema names (default: false, auto-included if ≤20 matches)', default: false } } } }, { name: 'halopsa_api_call', description: 'Make authenticated API calls to any HaloPSA endpoint. Use this after finding the right endpoint with schema tools.', inputSchema: { type: 'object', properties: { path: { type: 'string', description: 'API endpoint path (e.g., "/api/Ticket", "/api/Actions")' }, method: { type: 'string', enum: ['GET', 'POST', 'PUT', 'PATCH', 'DELETE'], description: 'HTTP method to use', default: 'GET' }, body: { type: 'object', description: 'Request body data for POST/PUT/PATCH requests' }, queryParams: { type: 'object', description: 'URL query parameters as key-value pairs', additionalProperties: { type: 'string' } } }, required: ['path'] } } ];