spanner-query-count

Count Google Cloud Spanner queries by type and status within specified time ranges to monitor database performance and identify issues.

Input Schema

TableJSON Schema

Name	Required	Description	Default
`instanceId`	No	Spanner instance ID (optional, if not provided will show all instances)
`databaseId`	No	Spanner database ID (optional, if not provided will show all databases)
`queryType`	No	Type of queries to count (ALL, READ, QUERY)	ALL
`status`	No	Status of queries to count (ALL, OK, ERROR)	ALL
`startTime`	No	Start time for the query (e.g., "1h", "2d", "30m")	1h
`endTime`	No	End time for the query (defaults to now)
`alignmentPeriod`	No	Alignment period for aggregating data points (e.g., "60s", "5m", "1h")	60s

Implementation Reference

src/services/spanner/query-count.ts:68-215 (handler)

The asynchronous handler function implementing the core logic of the 'spanner-query-count' tool. It queries Google Cloud Monitoring API for Spanner query count metrics based on provided filters (instance, database, query type, status, time range), aggregates the data, and formats it into a Markdown report with tables.

async ({ instanceId, databaseId, queryType, status, startTime, endTime, alignmentPeriod }, context) => {
  try {
    const projectId = await getProjectId();
    const client = new MetricServiceClient({
      projectId: process.env.GOOGLE_CLOUD_PROJECT
    });
    
    // Parse time range
    const start = parseRelativeTime(startTime);
    const end = endTime ? parseRelativeTime(endTime) : new Date();
    
    // Build filter for the metric
    let filter = 'metric.type = "spanner.googleapis.com/query_count"';
    
    // Add resource filters if specified
    if (instanceId) {
      filter += ` AND resource.labels.instance_id = "${instanceId}"`;
    }
    
    // Add metric label filters
    if (databaseId) {
      filter += ` AND metric.labels.database = "${databaseId}"`;
    }
    
    if (queryType !== 'ALL') {
      filter += ` AND metric.labels.query_type = "${queryType.toLowerCase()}"`;
    }
    
    if (status !== 'ALL') {
      filter += ` AND metric.labels.status = "${status.toLowerCase()}"`;
    }
    
    // Parse alignment period (e.g., "60s" -> 60 seconds)
    const match = alignmentPeriod.match(/^(\d+)([smhd])$/);
    if (!match) {
      throw new GcpMcpError(
        'Invalid alignment period format. Use format like "60s", "5m", "1h".',
        'INVALID_ARGUMENT',
        400
      );
    }
    
    const value = parseInt(match[1]);
    const unit = match[2];
    let seconds = value;
    
    switch (unit) {
      case 'm': // minutes
        seconds = value * 60;
        break;
      case 'h': // hours
        seconds = value * 60 * 60;
        break;
      case 'd': // days
        seconds = value * 60 * 60 * 24;
        break;
    }
    
    // Build the request
    const request = {
      name: `projects/${projectId}`,
      filter,
      interval: {
        startTime: {
          seconds: Math.floor(start.getTime() / 1000),
          nanos: 0
        },
        endTime: {
          seconds: Math.floor(end.getTime() / 1000),
          nanos: 0
        }
      },
      aggregation: {
        alignmentPeriod: {
          seconds
        },
        perSeriesAligner: 'ALIGN_SUM',
        crossSeriesReducer: 'REDUCE_SUM'
      }
    };
    
    // Execute the request
    const timeSeriesData = await client.listTimeSeries(request as any);
    const timeSeries = timeSeriesData[0];
    
    if (!timeSeries || timeSeries.length === 0) {
      return {
        content: [{
          type: 'text',
          text: `# Spanner Query Count\n\nProject: ${projectId}\n${instanceId ? `\nInstance: ${instanceId}` : ''}\n${databaseId ? `\nDatabase: ${databaseId}` : ''}\n\nQuery Type: ${queryType}\nStatus: ${status}\nTime Range: ${start.toISOString()} to ${end.toISOString()}\nAlignment Period: ${alignmentPeriod}\n\nNo query count data found for the specified parameters.`
        }]
      };
    }
    
    // Format the results
    let markdown = `# Spanner Query Count\n\nProject: ${projectId}\n${instanceId ? `\nInstance: ${instanceId}` : ''}\n${databaseId ? `\nDatabase: ${databaseId}` : ''}\n\nQuery Type: ${queryType}\nStatus: ${status}\nTime Range: ${start.toISOString()} to ${end.toISOString()}\nAlignment Period: ${alignmentPeriod}\n\n`;
    
    // Create a table for each time series
    for (const series of timeSeries) {
      const seriesData = series as unknown as TimeSeriesData;
      
      // Extract labels for the table header
      const instanceName = seriesData.resource.labels.instance_id || 'unknown';
      const databaseName = seriesData.metric.labels?.database || 'all';
      const queryTypeValue = seriesData.metric.labels?.query_type || 'all';
      const statusValue = seriesData.metric.labels?.status || 'all';
      const optimizerVersion = seriesData.metric.labels?.optimizer_version || 'unknown';
      
      markdown += `## Instance: ${instanceName}, Database: ${databaseName}\n`;
      markdown += `Query Type: ${queryTypeValue}, Status: ${statusValue}, Optimizer Version: ${optimizerVersion}\n\n`;
      
      // Table header
      markdown += '| Timestamp | Query Count |\n';
      markdown += '|-----------|------------|\n';
      
      // Table rows
      if (seriesData.points && seriesData.points.length > 0) {
        // Sort points by time (oldest first)
        const sortedPoints = [...seriesData.points].sort((a, b) => {
          const aTime = Number(a.interval.startTime.seconds);
          const bTime = Number(b.interval.startTime.seconds);
          return aTime - bTime;
        });
        
        for (const point of sortedPoints) {
          const timestamp = new Date(Number(point.interval.endTime.seconds) * 1000).toISOString();
          const count = point.value.int64Value || '0';
          
          markdown += `| ${timestamp} | ${count} |\n`;
        }
      } else {
        markdown += '| No data | 0 |\n';
      }
      
      markdown += '\n';
    }
    
    return {
      content: [{
        type: 'text',
        text: markdown
      }]
    };
  } catch (error: any) {
    console.error('Error in spanner-query-count tool:', error);
    throw error;
  }
}

src/services/spanner/query-count.ts:60-67 (schema)

Zod schema defining the input parameters for the 'spanner-query-count' tool, including optional instanceId, databaseId, queryType (ALL/READ/QUERY), status (ALL/OK/ERROR), startTime, endTime, and alignmentPeriod.

  instanceId: z.string().optional().describe('Spanner instance ID (optional, if not provided will show all instances)'),
  databaseId: z.string().optional().describe('Spanner database ID (optional, if not provided will show all databases)'),
  queryType: z.enum(['ALL', 'READ', 'QUERY']).default('ALL').describe('Type of queries to count (ALL, READ, QUERY)'),
  status: z.enum(['ALL', 'OK', 'ERROR']).default('ALL').describe('Status of queries to count (ALL, OK, ERROR)'),
  startTime: z.string().default('1h').describe('Start time for the query (e.g., "1h", "2d", "30m")'),
  endTime: z.string().optional().describe('End time for the query (defaults to now)'),
  alignmentPeriod: z.string().default('60s').describe('Alignment period for aggregating data points (e.g., "60s", "5m", "1h")')
},

src/services/spanner/query-count.ts:56-217 (registration)

The registerSpannerQueryCountTool export function that calls server.tool to register the 'spanner-query-count' tool with its schema and handler on the MCP server.

export function registerSpannerQueryCountTool(server: McpServer): void {
  server.tool(
    'spanner-query-count',
    {
      instanceId: z.string().optional().describe('Spanner instance ID (optional, if not provided will show all instances)'),
      databaseId: z.string().optional().describe('Spanner database ID (optional, if not provided will show all databases)'),
      queryType: z.enum(['ALL', 'READ', 'QUERY']).default('ALL').describe('Type of queries to count (ALL, READ, QUERY)'),
      status: z.enum(['ALL', 'OK', 'ERROR']).default('ALL').describe('Status of queries to count (ALL, OK, ERROR)'),
      startTime: z.string().default('1h').describe('Start time for the query (e.g., "1h", "2d", "30m")'),
      endTime: z.string().optional().describe('End time for the query (defaults to now)'),
      alignmentPeriod: z.string().default('60s').describe('Alignment period for aggregating data points (e.g., "60s", "5m", "1h")')
    },
    async ({ instanceId, databaseId, queryType, status, startTime, endTime, alignmentPeriod }, context) => {
      try {
        const projectId = await getProjectId();
        const client = new MetricServiceClient({
          projectId: process.env.GOOGLE_CLOUD_PROJECT
        });
        
        // Parse time range
        const start = parseRelativeTime(startTime);
        const end = endTime ? parseRelativeTime(endTime) : new Date();
        
        // Build filter for the metric
        let filter = 'metric.type = "spanner.googleapis.com/query_count"';
        
        // Add resource filters if specified
        if (instanceId) {
          filter += ` AND resource.labels.instance_id = "${instanceId}"`;
        }
        
        // Add metric label filters
        if (databaseId) {
          filter += ` AND metric.labels.database = "${databaseId}"`;
        }
        
        if (queryType !== 'ALL') {
          filter += ` AND metric.labels.query_type = "${queryType.toLowerCase()}"`;
        }
        
        if (status !== 'ALL') {
          filter += ` AND metric.labels.status = "${status.toLowerCase()}"`;
        }
        
        // Parse alignment period (e.g., "60s" -> 60 seconds)
        const match = alignmentPeriod.match(/^(\d+)([smhd])$/);
        if (!match) {
          throw new GcpMcpError(
            'Invalid alignment period format. Use format like "60s", "5m", "1h".',
            'INVALID_ARGUMENT',
            400
          );
        }
        
        const value = parseInt(match[1]);
        const unit = match[2];
        let seconds = value;
        
        switch (unit) {
          case 'm': // minutes
            seconds = value * 60;
            break;
          case 'h': // hours
            seconds = value * 60 * 60;
            break;
          case 'd': // days
            seconds = value * 60 * 60 * 24;
            break;
        }
        
        // Build the request
        const request = {
          name: `projects/${projectId}`,
          filter,
          interval: {
            startTime: {
              seconds: Math.floor(start.getTime() / 1000),
              nanos: 0
            },
            endTime: {
              seconds: Math.floor(end.getTime() / 1000),
              nanos: 0
            }
          },
          aggregation: {
            alignmentPeriod: {
              seconds
            },
            perSeriesAligner: 'ALIGN_SUM',
            crossSeriesReducer: 'REDUCE_SUM'
          }
        };
        
        // Execute the request
        const timeSeriesData = await client.listTimeSeries(request as any);
        const timeSeries = timeSeriesData[0];
        
        if (!timeSeries || timeSeries.length === 0) {
          return {
            content: [{
              type: 'text',
              text: `# Spanner Query Count\n\nProject: ${projectId}\n${instanceId ? `\nInstance: ${instanceId}` : ''}\n${databaseId ? `\nDatabase: ${databaseId}` : ''}\n\nQuery Type: ${queryType}\nStatus: ${status}\nTime Range: ${start.toISOString()} to ${end.toISOString()}\nAlignment Period: ${alignmentPeriod}\n\nNo query count data found for the specified parameters.`
            }]
          };
        }
        
        // Format the results
        let markdown = `# Spanner Query Count\n\nProject: ${projectId}\n${instanceId ? `\nInstance: ${instanceId}` : ''}\n${databaseId ? `\nDatabase: ${databaseId}` : ''}\n\nQuery Type: ${queryType}\nStatus: ${status}\nTime Range: ${start.toISOString()} to ${end.toISOString()}\nAlignment Period: ${alignmentPeriod}\n\n`;
        
        // Create a table for each time series
        for (const series of timeSeries) {
          const seriesData = series as unknown as TimeSeriesData;
          
          // Extract labels for the table header
          const instanceName = seriesData.resource.labels.instance_id || 'unknown';
          const databaseName = seriesData.metric.labels?.database || 'all';
          const queryTypeValue = seriesData.metric.labels?.query_type || 'all';
          const statusValue = seriesData.metric.labels?.status || 'all';
          const optimizerVersion = seriesData.metric.labels?.optimizer_version || 'unknown';
          
          markdown += `## Instance: ${instanceName}, Database: ${databaseName}\n`;
          markdown += `Query Type: ${queryTypeValue}, Status: ${statusValue}, Optimizer Version: ${optimizerVersion}\n\n`;
          
          // Table header
          markdown += '| Timestamp | Query Count |\n';
          markdown += '|-----------|------------|\n';
          
          // Table rows
          if (seriesData.points && seriesData.points.length > 0) {
            // Sort points by time (oldest first)
            const sortedPoints = [...seriesData.points].sort((a, b) => {
              const aTime = Number(a.interval.startTime.seconds);
              const bTime = Number(b.interval.startTime.seconds);
              return aTime - bTime;
            });
            
            for (const point of sortedPoints) {
              const timestamp = new Date(Number(point.interval.endTime.seconds) * 1000).toISOString();
              const count = point.value.int64Value || '0';
              
              markdown += `| ${timestamp} | ${count} |\n`;
            }
          } else {
            markdown += '| No data | 0 |\n';
          }
          
          markdown += '\n';
        }
        
        return {
          content: [{
            type: 'text',
            text: markdown
          }]
        };
      } catch (error: any) {
        console.error('Error in spanner-query-count tool:', error);
        throw error;
      }
    }
  );
}

src/index.ts:141-141 (registration)
Invocation of registerSpannerQueryCountTool(server) during main server startup to enable the tool.
```
registerSpannerQueryCountTool(server);
```

Google Cloud MCP Server