AWS Lambda で Amazon Aurora のスロークエリログを Slack 通知する

# Time: 2023-10-22T02:57:55.655927Z
# User@Host: xxxxxxxxx[xxxxxxxxx] @ [10.13.103.170] Id: 2638113
# Query_time: 35.549734 Lock_time: 0.000164 Rows_sent: 1 Rows_examined: 15535
use xxxx;
SET timestamp=1697943475;
SELECT SLEEP(20);

def parse_slowquery_message(text: str) -> dict[str, str]:
    """Parse slow query log sent from cloudwatch."""
    pattern = re.compile(
        r"""
        User@Host:\ ([\w-]+)\[[\w-]+\]\ @.*     # user
        Id:\s* (\d+).*                          # id
        Query_time:\ ([\d\.]+).*                # query_time
        Lock_time:\ ([\d\.]+).*                 # lock_time
        Rows_sent:\ (\d+).*                     # rows_sent
        Rows_examined:\ (\d+)\n                 # rows_examined
        (.*)                                    # query
        """,
        re.S | re.X,
    ) 
    match = re.search(pattern, text)
    if not match:
        raise ValueError(f"Failed to parse text: {text}")
    return {
        "user": match[1],
        "id": match[2],
        "query_time": match[3],
        "lock_time": match[4],
        "rows_sent": match[5],
        "rows_examined": match[6],
        "query": match[7],
    }

secrets_manager_client = boto3.client(service_name="secretsmanager")
get_secret_value_response = secrets_manager_client.get_secret_value(
    SecretId=SLACK_BOT_SECRET_ID
)

    initial_comment = textwrap.dedent(
        f"""
        *New Slow Query Alert* :rotating_light:
        Author {res['user']} : {res['id']}
        Query Time: {res['query_time']}
        Lock Time: {res['lock_time']}
        Rows Sent: {res['rows_sent']}
        Rows Examined: {res['rows_examined']}
    """
    )
    try:
        result = slack_client.files_upload(
            channels=SLACK_CHANNEL_ID,
            initial_comment=initial_comment,
            content=res["query"],
            filetype="sql",
            title="New Slow Query Alert",
        )
        logger.info(result)

    except SlackApiError as e:
        logger.error(f"Error uploading file: {e}")
        raise e

variable "aws_region" {
  type = string
}

variable "aws_account_id" {
  type = string
}

variable "rds_cluster_name" {
  description = "RDS cluster to monitor slow query logs."
  type        = string
}

# Lambda の SLACK_CHANNEL_ID に渡す
variable "slack_channel_id" {
  description = "Slack channel ID to notify slow query logs."
  type        = string
}
# Slack App bot token の値
variable "slackbot_secret_value" {
  description = "Slackbot secret value stored in AWS Secrets Manager."
  type        = string  sensitive   = true
}
# Lambda の USER_NAME に渡す
variable "user_name" {
  description = "Database user name to track slow queries."
  type        = string
}

variable "lambda_s3_bucket" {
  description = "ID of the S3 bucket where the lambda zip file is stored."
  type        = string
}

variable "lambda_s3_prefix" {
  description = "Prefix of the S3 bucket where the lambda zip file is stored."
  type        = string
}

locals {
  # クラスタを指定したら自動的に RDS のスロークエリの log group 名は定まっている
  rds_slow_query_log_group_name = "/aws/rds/cluster/${var.rds_cluster_name}/slowquery"
  rds_slow_query_log_group_arn  = "arn:aws:logs:${var.aws_region}:${var.aws_account_id}:log-group:${local.rds_slow_query_log_group_name}:*"

  # Lambda の SLACK_BOT_SECRET_ID に渡す
  slackbot_secret_name          = "slowquery_slackbot_secret"
}

resource "aws_secretsmanager_secret" "slackbot_secret" {
  name = local.slackbot_secret_name
}

resource "aws_secretsmanager_secret_version" "slackbot_secret" {
  secret_id     = aws_secretsmanager_secret.slackbot_secret.id
  secret_string = var.slackbot_secret_value
}

resource "aws_iam_role" "role_for_lambda_post_slow_query_to_slack" {
  name = "role_for_lambda_post_slow_query_to_slack"

  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Effect    = "Allow"
        Action    = "sts:AssumeRole"
        Principal = { Service = "lambda.amazonaws.com" }
      }
    ]
  })
}

resource "aws_iam_role_policy" "allow_access_to_cloudwatchlogs" {
  name = "allow_access_to_cloudwatchlogs"
  role = aws_iam_role.role_for_lambda_post_slow_query_to_slack.id

  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Effect = "Allow"
        Action = [
          "logs:CreateLogGroup",
          "logs:CreateLogStream",
          "logs:PutLogEvents",
        ]
        Resource = "*"
      }
    ]
  })
}

resource "aws_iam_role_policy" "allow_access_to_secretsmanager" {
  name = "allow_access_to_secretsmanager"
  role = aws_iam_role.role_for_lambda_post_slow_query_to_slack.id

  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Effect   = "Allow"
        Action   = "secretsmanager:GetSecretValue"
        Resource = aws_secretsmanager_secret.slackbot_secret.arn
      }
    ]
  })
}

resource "aws_lambda_function" "post_slow_query_to_slack" {
  s3_bucket     = var.lambda_s3_bucket
  s3_key        = var.lambda_s3_prefix
  function_name = "post_slow_query_to_slack_function"
  role          = aws_iam_role.role_for_lambda_post_slow_query_to_slack.arn
  handler       = "flywheel.devtools.post_slow_query_to_slack.post_slow_query_to_slack.lambda_handler"
  runtime       = "python3.11"
  memory_size   = 128
  timeout       = 300

  environment {
    variables = {
      SLACK_CHANNEL_ID    = var.slack_channel_id
      SLACK_BOT_SECRET_ID = aws_secretsmanager_secret.slackbot_secret.id
      USER_NAME           = var.user_name
    }
  }
}

resource "aws_cloudwatch_log_subscription_filter" "slow_query_logfilter" {
  name            = "slow_query_logfilter"
  log_group_name  = local.rds_slow_query_log_group_name
  filter_pattern  = ""
  destination_arn = aws_lambda_function.post_slow_query_to_slack.arn
  distribution    = "ByLogStream"
}

resource "aws_lambda_permission" "post_slow_query_to_slack_allow_lambda_execution_from_cloudwatch_logs_subscription" {
  action        = "lambda:InvokeFunction"
  function_name = aws_lambda_function.post_slow_query_to_slack.function_name
  principal     = "logs.${var.aws_region}.amazonaws.com"
  source_arn    = local.rds_slow_query_log_group_arn
}

module "slow_query_slack_notification" {
  source = "./modules/slow_query_slack_notification"

  aws_region = "ap-northeast-1"
  aws_account_id = "XXXXXXXXX"
  rds_cluster_name = "cluster-xxxx"
  slack_channel_id = "XXXXXX"
  user_name = "operation-xxx"
  lambda_s3_bucket = "fw-XXXXXXXXX-bucket"
  lambda_s3_prefix = "dev/slow-query-slack-notification/lambda.zip"
}