Query GCP monitoring API for current postgres connections query?

Question

We can see the postgres connections in the GCP console, so we presume that it can be queried via the monitoring API. The console gives us this MQL query:

fetch cloudsql_database
| metric 'cloudsql.googleapis.com/database/postgresql/num_backends'
| filter (resource.database_id == 'my-project-id:my-database-id')
| group_by 1m, [value_num_backends_mean: mean(value.num_backends)]
| every 1m

The monitoring API https://cloud.google.com/monitoring/api/ref_v3/rest/v3/projects.timeSeries/query

says that the query parameter is required and the query is in the monitoring query language format. However, when I "Try this API" and attempt to set the query to what I got from the console above, I get an error that "Unexpected character encountered" for the | character. How can I properly format the query in a way to get the result I am looking for? I have looked through the Retrieving data with timeSeries.query documentation, and it does not mention the need to escape such characters.

Do you recieve an error when you remove those characters? If you do, make sure to edit the original post with that info — Nicholas Elkaim

Nicholas Elkaim Nicholas Elkaim · Accepted Answer · 2020-08-07T17:55:39

So MQL is more for querying within the Monitoring Metrics Explorer, and the Monitoring API uses a different kind of language for its filtering. Something along the lines of

resource.type = "the_resource_type"
resource.labels.a_label_for_the_resource_type = "label_value"

And this is what you would use for querying with the API instead of MQL.

An exa will look something like this:

metric.type = "compute.googleapis.com/instance/cpu/usage_time" AND
    metric.labels.instance_name = "my-instance-name"

https://cloud.google.com/monitoring/api/v3/filters

You may also want to look into using time series list as another possible API method, as this does not require MQL.

Query GCP monitoring API for current postgres connections query?

1 Answers