More Info:

Ensure Cloud Monitoring monitors database state check.

Risk Level

Medium

Address

Operational Maturity, Performance Efficiency

Compliance Standards

CBP

Triage and Remediation

Remediation

Using Console

To remediate the misconfiguration of not monitoring the database state check in GCP using GCP console, follow the steps below:
  1. Open the GCP Console and go to the Cloud SQL instances page.
  2. Select the instance that you want to monitor and click on it.
  3. In the left navigation menu, click on Monitoring.
  4. In the Monitoring page, click on the Add Chart button.
  5. In the Add Chart page, select the metric that you want to monitor. For database state check, you can select the metric “Cloud SQL Database State Check”.
  6. Configure the chart settings according to your preferences. You can set the chart title, chart type, chart size, and other options.
  7. Click on the Save button to save the chart.
  8. Once the chart is saved, it will be displayed in the Monitoring page. You can view the chart and monitor the database state check for the selected instance.
By following these steps, you will be able to remediate the misconfiguration of not monitoring the database state check in GCP using GCP console.

To remediate the misconfiguration of not monitoring the database state check in GCP using GCP CLI, you can follow the below steps:
  1. Open the Cloud Shell in the GCP console.
  2. Run the following command to list all the databases in the project:
    gcloud sql instances list
    
  3. Note down the name of the database instance for which you want to enable monitoring for database state check.
  4. Run the following command to enable monitoring for database state check for the specified database instance:
    gcloud alpha sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_database_state_checks=on
    
    Replace [INSTANCE_NAME] with the name of the database instance you noted down in step 3.
  5. After running the above command, the monitoring for database state check will be enabled for the specified database instance.
Note: The above command will enable monitoring for database state check for all the databases in the specified instance. If you want to enable it for a specific database within the instance, you can use the --database flag followed by the name of the database.
To remediate the misconfiguration of not monitoring the database state check in GCP using Python, follow the steps below:Step 1: Install the necessary librariesYou will need to install the Google Cloud client library for Python. You can do this by running the following command in your terminal:
pip install google-cloud-monitoring
Step 2: Authenticate your applicationYou will need to authenticate your application to access the GCP API. You can do this by creating a service account and downloading the JSON key file. Then, set the environment variable GOOGLE_APPLICATION_CREDENTIALS to the path of the key file.
export GOOGLE_APPLICATION_CREDENTIALS="/path/to/keyfile.json"
Step 3: Create a metric and alert policyCreate a metric to monitor the database state check. You can do this by running the following code:
from google.cloud import monitoring_v3

client = monitoring_v3.MetricServiceClient()

project_id = 'your-project-id'
metric_type = 'custom.googleapis.com/database_state_check'

descriptor = monitoring_v3.types.MetricDescriptor()
descriptor.type = metric_type
descriptor.metric_kind = (
    monitoring_v3.enums.MetricDescriptor.MetricKind.GAUGE)
descriptor.value_type = (
    monitoring_v3.enums.MetricDescriptor.ValueType.BOOL)
descriptor.description = 'Database state check'
descriptor = client.create_metric_descriptor(
    project_id, descriptor)
Next, create an alert policy to notify you when the metric value is false. You can do this by running the following code:
client = monitoring_v3.AlertPolicyServiceClient()

project_name = f'projects/{project_id}'
metric_filter = (
    f'metric.type="{metric_type}" '
    'resource.type="global"')
condition_threshold = (
    monitoring_v3.types.AlertPolicy.Condition.MetricThreshold(
        filter=metric_filter,
        duration={
            'seconds': 300
        },
        comparison=(
            monitoring_v3.enums.ComparisonType.COMPARISON_LT),
        threshold_value=0.5,
        trigger={
            'count': 1
        },
        aggregations=[
            monitoring_v3.types.Aggregation(
                alignment_period={
                    'seconds': 60
                },
                per_series_aligner=(
                    monitoring_v3.enums.Aggregation.Aligner.ALIGN_MEAN),
                cross_series_reducer=(
                    monitoring_v3.enums.Aggregation.Reducer.REDUCE_MEAN),
            )
        ]
    )
)
policy = monitoring_v3.types.AlertPolicy()
policy.display_name = 'Database state check alert'
policy.conditions = [condition_threshold]
policy = client.create_alert_policy(project_name, policy)
Step 4: Create a monitoring checkFinally, create a monitoring check to periodically check the database state. You can do this by running the following code:
from google.cloud import monitoring_v3

client = monitoring_v3.UptimeCheckServiceClient()

project_name = f'projects/{project_id}'
check_config = monitoring_v3.types.UptimeCheckConfig(
    display_name='Database state check',
    monitored_resource=monitoring_v3.types.MonitoredResource(
        type='uptime_url',
        labels={
            'host': 'your-database-hostname',
            'port': 'your-database-port',
            'path': 'your-database-state-check-path',
            'protocol': 'your-database-protocol'
        }
    ),
    http_check=monitoring_v3.types.UptimeCheckConfig.HttpCheck(
        path='your-database-state-check-path',
        port=your-database-port,
        use_ssl=True,
        auth_info=monitoring_v3.types.UptimeCheckConfig.HttpCheck.
            BasicAuthentication(username='your-database-username',
                                 password='your-database-password')
    ),
    timeout=monitoring_v3.types.Duration(seconds=10),
    period=monitoring_v3.types.Duration(seconds=60),
    content_matchers=[
        monitoring_v3.types.UptimeCheckConfig.ContentMatcher(
            content='your-database-state-check-result',
            matcher=monitoring_v3.enums.
                UptimeCheckConfig.ContentMatcher.Matcher.CONTAINS)
    ]
)
check = client.create_uptime_check_config(project_name, check_config)
With these steps, you have successfully remediated the misconfiguration of not monitoring the database state check in GCP using Python.

Additional Reading: