Enable Monitoring for SQL Instance Configuration Changes

01 Sign in to the Google Cloud Management Console.

02 Select the GCP project that you want to examine from the console top navigation bar.

03 Navigate to Cloud Logging console available at https://console.cloud.google.com/logs.

04 In the left navigation panel, under Configure, select Logs-based Metrics to view all the log metrics available for the selected GCP project.

05 On the Logs-based Metrics page, in the User-defined metrics section, click inside the Filter box, select Filter, and paste the following filter pattern: protoPayload.methodName=cloudsql.instances.update. Press Enter to filter the existing metrics and return the ones that match the given filter. If one or more active (enabled) metrics are returned, continue the Audit process with the next step. Otherwise, if no enabled metrics are returned in the User-defined metrics list, there are no user-defined log metrics created to detect the resource configuration changes that match the filter expression, therefore, the Cloud SQL instance configuration changes are not being monitored within the selected GCP project.

06 Navigate to Alerting Policies console at https://console.cloud.google.com/monitoring/alerting/policies.

07 Click on the name of the alerting policy that you want to examine, listed in the Display Name column.

08 On the selected policy page, check the following parameters. For a compliant alerting policy, all the following parameters (i.e. status, metric name, and notification channels) must be properly configured. If one or more parameters are not properly configured, the selected alerting policy is not compliant (i.e. the policy is not monitoring for Cloud SQL configuration changes):

1. Check the alerting policy status displayed next to Policy details. If the status is not set to ENABLED, the selected policy is not enabled.
2. In the Conditions section, click on the information button (i icon) available for the policy condition, to determine the name of the logs-based metric associated with the selected alerting policy, i.e. logging/user/[log-metric-name], where [log-metric-name] is the name of the valid log metric specified at step no. 5. If the name of the valid metric is not displayed in the condition information box, the selected policy is not associated with the metric that we want.
3. Check for any notification channels listed in the Notification Channels section. If there are no valid notifications channels configured in this section, the selected policy is not properly configured.

09 Repeat steps no. 7 and 8 for each alerting policy created for the selected GCP project. If none of the policies available has the appropriate configuration, there are no Google Cloud alerting policies configured to monitor Cloud SQL instance configuration changes.

10 Repeat steps no. 2 – 9 for each GCP project deployed in your Google Cloud account.

01 Run projects list command (Windows/macOS/Linux) with custom query filters to list the ID of each project available in your Google Cloud account:

gcloud projects list
  --format="table(projectId)"

02 The command output should return the requested GCP project IDs:

PROJECT_ID
cc-frontend-app-112233
cc-big-data-app-111222
cc-prod-web-app-123123

03 Run logging metrics list command (Windows/macOS/Linux) with the ID of the GCP project that you want to examine as the identifier parameter and custom query filters to list the user defined logs-based metrics created for the selected project:

gcloud logging metrics list
  --project cc-frontend-app-112233
  --format="table(name)"

04 The command output should return the names of the requested logs-based metrics:

NAME: cc-network-route-changes

NAME: cc-firewall-rule-changes

05 Run logging metrics describe command (Windows/macOS/Linux) with the name of the logs-based metric that you want to examine as the identifier parameter and custom formatting to describe the filter defined for the selected metric:

gcloud logging metrics describe cc-network-route-changes
  --format="table(filter)"

06 The command output should return the requested logs-based metric filter:

FILTER: resource.type=gce_route AND protoPayload.methodName=beta.compute.routes.patch OR protoPayload.methodName=beta.compute.routes.insert

07 Repeat steps no. 5 and 6 for each metric created for the selected GCP project. If one or more metrics have the filter pattern listed above, continue the Audit process with the next step. Otherwise, if none of the metrics available has the required filter, there are no user-defined log metrics created to detect the GCP resource configuration changes that match the filter expression, therefore, the Cloud SQL instance configuration changes are not being monitored within the selected GCP project.

08 Run monitoring policies list command (Windows/macOS/Linux) with the ID of the GCP project that you want to examine as the identifier parameter, to list the name of each alerting policy created for the selected project:

gcloud alpha monitoring policies list
  --project cc-frontend-app-112233
  --format="table(name)"

09 The command output should return the names of the requested alerting policies:

NAME: projects/cc-frontend-app-112233/alertPolicies/1234567890123456789

NAME: projects/cc-frontend-app-112233/alertPolicies/1122334455667788990

10 Run monitoring policies describe command (Windows/macOS/Linux) with the name of the alerting policy that you want to examine as the identifier parameter, to describe the policy configuration in JSON format:

gcloud alpha monitoring policies describe projects/cc-frontend-app-112233/alertPolicies/1234567890123456789
  --format="json"

11 The command output should return the configuration information available for the selected policy:

{
	"alertStrategy": {
		"autoClose": "604800s"
	},
	"combiner": "OR",
	"conditions": [
		{
			"conditionThreshold": {
				"aggregations": [
					{
						"alignmentPeriod": "600s",
						"crossSeriesReducer": "REDUCE_COUNT",
						"perSeriesAligner": "ALIGN_DELTA"
					}
				],
				"comparison": "COMPARISON_GT",
				"duration": "0s",
				"filter": "metric.type=\"logging.googleapis.com/user/cc-network-route-changes\"",
				"trigger": {
					"count": 1
				}
			},
			"displayName": "logging/user/cc-network-route-changes [COUNT]",
			"name": "projects/cc-frontend-app-112233/alertPolicies/1234567890123456789/conditions/1277204009176305411"
		}
	],
	"creationRecord": {
		"mutateTime": "2024-04-29T10:48:09.866783684Z",
		"mutatedBy": "ops@domain.com"
	},
	"displayName": "cc-prod-alert-policy",
	"documentation": {
		"subject": "Configuration Change Alert!"
	},
	"enabled": false,
	"mutationRecord": {
		"mutateTime": "2024-04-29T10:36:45.043411689Z",
		"mutatedBy": "ops@domain.com"
	},
	"name": "projects/cc-frontend-app-112233/alertPolicies/1234567890123456789",
	"notificationChannels": [
		"projects/cc-frontend-app-112233/notificationChannels/12341234123412341234"
	],
	"severity": "WARNING"
}

12 Run monitoring channels describe command (Windows/macOS/Linux) with the name of the notification channel associated with the selected alerting policy as the identifier parameter, to describe the channel configuration:

gcloud alpha monitoring channels describe "projects/cc-frontend-app-112233/notificationChannels/12341234123412341234"
  --format=json

13 The command output should return the configuration information available for the selected notification channel:

{
	"displayName": "cc-email-notification-channel",
	"enabled": false,
	"labels": {
		"email_address": "user@domain.com"
	},
	"creationRecord": {
		"mutateTime": "2023-11-22T11:26:32.897136366Z"
	},
	"mutationRecords": [
		{
			"mutateTime": "2023-11-22T11:26:32.897136366Z"
		}
	],
	"name": "projects/cc-frontend-app-112233/notificationChannels/12341234123412341234",
	"type": "email"
}

14 Check the configuration information returned at steps no. 11 and 13 for the parameters listed below. For a compliant alerting policy, all the following parameters (i.e. status, metric name, and notification channels) must be properly configured. If one or more parameters are not properly configured, the selected alerting policy is not compliant:

1. Check the alerting policy status returned at step no. 11. If "enabled" is set to false, the selected policy is not enabled.
2. Check the policy condition (i.e. "conditions" block) returned at step no. 11 to determine the name of the logs-based metric associated with the selected alerting policy, i.e. metric.type=\"logging.googleapis.com/user/[log-metric-name]\", where [log-metric-name] should be the name of the valid logs-based metric. The valid metric has the following filter pattern: protoPayload.methodName=cloudsql.instances.update. If the [log-metric-name] is not the name of the valid metric, the selected policy is not associated with the metric that we want.
3. Check the notification channels returned at step no. 13 for compliance. A compliant notification channel should use a valid channel such as a working email address or phone number, with the "enabled" attribute set to true. If there are no compliant notification channels returned at this step, the selected policy is not properly configured.

15 Repeat steps no. 10 – 14 for each alerting policy available for the selected GCP project. If none of the verified policies has the appropriate configuration, there are no Google Cloud alerting policies configured to monitor Cloud SQL instance configuration changes.

16 Repeat steps no. 3 – 15 for each GCP project deployed in your Google Cloud account.

01 Sign in to the Google Cloud Management Console.

02 Select the GCP project that you want to access from the console top navigation bar.

03 Navigate to Cloud Logging console available at https://console.cloud.google.com/logs.

04 In the left navigation panel, under Configure, select Logs-based Metrics to access all the log metrics available for the selected GCP project.

05 On the Logs-based Metrics page, in the User-defined metrics section, click inside the Filter box, select Filter, paste the following filter pattern: protoPayload.methodName=cloudsql.instances.update, and press Enter. This is to ensure that there is no such filter pattern already available.

06 Choose Create metric next to User-defined metrics to create a new log metric based on the filter pattern specified at the previous step.

07 On the Create log-based metric setup page, perform the following actions:

1. For Metric Type choose Counter.
2. For Details, provide a unique name for the new log metric in the Log-based metric name text box, enter a short description that reflects the metric purpose in the Description box, and type 1 in the Units text box for the number of log entries matching the given filter pattern.
3. For Filter selection, ensure that Select log scope is set to Project logs, and paste the following filter pattern: protoPayload.methodName=cloudsql.instances.update into the Build filter box.
4. (Optional) For Labels, use the + Add label button to attach one or more tags to the new log metric. Select Done to close the panel.
5. Choose Create metric to create your new log metric. If the request is successful, the following confirmation message should be displayed: Your log metric was successfully created. Data should be available soon..

08 In the left navigation panel, under Configure, select again Logs-based Metrics.

09 Choose the newly created log metric listed in the User-defined metrics section and make sure that this metric is marked as enabled. If the metric is enabled (i.e. the green checkmark icon is displayed), click on the 3-dot button to open the options menu, and select Create alert from metric to create an alerting policy using the new log metric.

10 An alerting policy condition must be created first. When the condition is met, it triggers an alerting policy violation. On the Create alerting policy setup page, perform the following actions:

1. For New condition, provide the following information:
   1. For Policy configuration mode choose Builder.
   2. Ensure that the correct metric is available in the Select a metric text box. Select a metric should be pre-populated with the identifier of the metric filter configured at the previous steps.
   3. For Transform data, configure the Rolling window to adjust the time the signal is calculated for (e.g. 10 minutes), set the Rolling window function to delta and the Time series aggregation to count. (Optional) To combine time series by label value, click inside the Time series group by configuration box and make a selection from the menu. The menu is constructed dynamically based on the time series you selected.
   4. Choose NEXT to continue the setup process.
2. For Configure trigger, perform the following operations:
   1. For Condition Types choose Threshold.
   2. For Alert trigger select Any time series violates.
   3. For Threshold position select Above threshold.
   4. For Threshold value use 0. A threshold of 0 (zero) will ensure that a notification alert is triggered for every SQL database server configuration change made within the selected GCP project.
   5. Provide a unique name for your alerting policy condition in the Condition name text box.
   6. Choose NEXT to continue the setup.
3. For Notifications and name, perform the following actions:
   1. Switch on the Use notification channel button to configure notification channels for alerts. Choose the names of the channels where you can be notified when alerting policy violations occur. As an example, this conformity rule will use email addresses as notification channels.
   2. Click inside the Notifications Channels configuration box and select the notification channels (in this case, email addresses) that you want to use for getting notification alerts. Select OK to apply the changes. To create a new notification channel, choose MANAGE NOTIFICATION CHANNELS, and add a new channel. Google Cloud recommends creating multiple notification channels for redundancy purposes.
   3. (Optional) For Notify on incident closure, choose whether or not to notify on incident closure and select a duration after which the incident will automatically close if the data is absent.
   4. (Optional) For Policy user labels, use the + ADD LABEL button to add one or more policy user labels. Policy user labels allow you to apply your own labels to alert policies for organization.
   5. (Optional) For Policy Severity Level, choose the appropriate severity level for your alerting policy. This can help you quickly prioritize issues as they arise.
   6. (Optional) Enter any documentation you would like included with the alert notification in the Documentation text box. When email notifications are sent, they'll include the information entered here.
   7. Provide a descriptive name for the new alerting policy in the Name the alert policy box.
   8. Choose NEXT to continue.
4. For Review alert, check the policy configuration details, then choose CREATE POLICY to create the new alerting policy. This policy will enable the monitoring of the Cloud SQL instance configuration changes within the selected GCP project.

11 Repeat steps no. 2 – 10 for each GCP project deployed in your Google Cloud account.

01 Run logging metrics create command (Windows/macOS/Linux) with the filter expression that detects SQL instance configuration changes as value for the --log-filter parameter, i.e. protoPayload.methodName=cloudsql.instances.update, to create the logs-based metric required to count the number of log entries that match the filter expression:

gcloud logging metrics create sql-instance-configuration-changes
  --description="Monitor SQL Database Instance Configuration Changes"
  --log-filter="protoPayload.methodName=cloudsql.instances.update"

02 If the metric is successfully created, the command output should return the name of the newly created log metric. The new metric is enabled by default:

Created [sql-instance-configuration-changes].

03 Run monitoring channels create command (Windows/macOS/Linux) to create the required notification channels. As an example, this conformity rule will use an email address as the notification channel for the alerting policy. When alerting policy violations occur, you will be notified via the email address configured at this step:

gcloud alpha monitoring channels create
  --display-name="cc-main-notification-channel"
  --description="Primary contact for GCP project notification alerts"
  --type=email
  --channel-labels=email_address="alerts@domain.com"

04 The command output should return the ID of the new notification channel:

Created notification channel [projects/cc-frontend-app-112233/notificationChannels/1234567890123456789].

05 Define the configuration for the alerting policy that you want to create and save it to a JSON document named gcp-alerting-policy.json. Replace the highlighted placeholder values such as [alerting-policy-display-name], [notification-channel-id], and [policy-condition-display-name] with your own values:

{
	"enabled": true,
	"displayName": "[alerting-policy-display-name]",
	"documentation": {
		"content": "[alerting-policy-documentation]",
		"mimeType": "text/markdown"
	},
	"notificationChannels": [
	 "[notification-channel-id]"
	],
	"combiner": "OR",
	"alertStrategy": {
		"autoClose": "604800s"
	},
	"conditions": [
		{
			"conditionThreshold": {
				"aggregations": [
					{
						"alignmentPeriod": "600s",
						"crossSeriesReducer": "REDUCE_COUNT",
						"perSeriesAligner": "ALIGN_MEAN"
					}
				],
				"comparison": "COMPARISON_GT",
				"duration": "0s",
				"filter": "resource.type = \"global\" AND metric.type = \"logging.googleapis.com/user/sql-instance-configuration-changes\"",
				"trigger": {
					"count": 1
				}
			},
			"displayName": "[policy-condition-display-name]"
		}
	]
}

06 Run monitoring policies create command (Windows/macOS/Linux) to create the Google Cloud alerting policy that sends email notifications when Cloud SQL instance configuration changes are made within the selected GCP project, based on the policy defined at the previous step (i.e. gcp-alerting-policy.json):

gcloud alpha monitoring policies create
  --policy-from-file=gcp-alerting-policy.json

07 The command output should return the ID of the new Google Cloud alerting policy:

Created alert policy [projects/cc-frontend-app-112233/alertPolicies/1234123412341234123].

08 Repeat steps no. 1 – 7 for each GCP project available within your Google Cloud account.