Question 254909 in PostgreSQL Question posted in
The official documentation can be found here.

Alerts in Google Cloud for custom queries on postgresql database

I am trying to create an alert in Google Cloud Monitoring that runs a custom count query on a Cloud SQL database (postgresql) and returns the result.
Eg.: SELECT count(*) FROM your_table WHERE [field] IS NULL OR LTRIM(RTRIM([field])) = ”

The main goal is to then create a threshold in the alert that will activate it when it is passed.

I don’t see any built-in option in GCP that will allow such sql query. Is there any alternative solution to this? (using a third party, cloud functions or mql query?)

Tags:

2

Answers


  1. 0

    You can set up an alert policy in Google Cloud Monitoring, create a log-based metric based on your desired conditions, export database logs to Cloud Logging, create thresholds for triggering alerts based on the log-based metric, and monitor particular conditions in a Google Cloud SQL database. This strategy refers to standard practices for cloud monitoring and makes use of log-based analytics.

    Login or Signup to reply.
  2. 0

    Yes, there are a few alternative solutions to creating an alert in Google Cloud Monitoring that runs a custom count query on a Cloud SQL database (PostgreSQL) and returns the result.

    Cloud Functions

    One way to do this is to use Cloud Functions. You can create a Cloud Function that runs your custom SQL query and returns the result. Then, you can create an alert in Cloud Monitoring that triggers when the result of the Cloud Function exceeds a certain threshold.

    To create a Cloud Function that runs a custom SQL query, you can use the following steps:

    1. Create a new Cloud Function project.
    2. Select the "Runtime" tab and select "Python (3.8)".
    3. Click the "Create" button.
    4. In the "Function" editor, enter a name for your function and paste the following code:
    import psycopg2

def my_function(request):
  """Runs a custom SQL query on a Cloud SQL database and returns the result."""

  # Connect to the Cloud SQL database.
  conn = psycopg2.connect(host='<YOUR_DATABASE_HOST>', database='<YOUR_DATABASE_NAME>', user='<YOUR_DATABASE_USER>', password='<YOUR_DATABASE_PASSWORD>')

  # Create a cursor.
  cur = conn.cursor()

  # Run the custom SQL query.
  cur.execute('SELECT count(*) FROM <YOUR_TABLE_NAME> WHERE <YOUR_FIELD> IS NULL OR LTRIM(RTRIM(<YOUR_FIELD>)) = ''')

  # Fetch the result of the query.
  result = cur.fetchone()[0]

  # Close the cursor and connection.
  cur.close()
  conn.close()

  # Return the result of the query.
  return result
    1. Click the "Save" button.
    2. Click the "Deploy" button.

    Once your Cloud Function is deployed, you can create an alert in Cloud Monitoring that triggers when the result of the Cloud Function exceeds a certain threshold.

    To create an alert in Cloud Monitoring, you can use the following steps:

    1. Go to the Cloud Monitoring console.
    2. Click the "Alerting" tab.
    3. Click the "Create Policy" button.
    4. In the "Condition" section, click the "Add Condition" button.
    5. Select "Custom" as the condition type.
    6. In the "Custom condition" field, enter the following MQL query:
    sum(resource.labels.function_name = "<YOUR_FUNCTION_NAME>")
    1. Click the "Add" button.
    2. In the "Trigger" section, select the "Threshold" trigger type.
    3. In the "Threshold" field, enter the threshold value.
    4. Click the "Create" button.

    Now, when the result of your Cloud Function exceeds the threshold value that you specified, an alert will be triggered.

    The best solution for you will depend on your specific needs and requirements. If you need a simple solution that is easy to set up, then using Cloud Functions is a good option. If you need a more powerful solution that offers more features, then you may want to consider using a third-party solution.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search