Google Cloud SQL API to get a list of Cloud SQL instances and generate a yaml for datadog
- kyle Hailey
- Apr 21, 2023
- 4 min read
Updated: Apr 22, 2023
TL;DR
result is on GIST at
guess I'm a grumpy old man, but computer interfaces should be easier.
Have you ever found yourself frustrated with cloud services and their interfaces? You're not alone. As a frequent user of Cloud SQL instances, I find myself constantly adding and deleting instances, leading to the tedious task of editing conf.yaml files for Datadog monitoring.
NOTE: if you use GCP or RDS APIs for automated instances creation, deletion, cloning, parameter setting etc, please share. The world needs more practical examples. What methods do you use? Github? Ansible? Puppet? Cloudformation?
Computers were meant to automate tasks like these, yet we still find ourselves stuck in outdated processes.
It's not just the conf.yaml files that cause frustration, but also APIs and tools like JQ that can be cryptic and difficult to navigate. While GCP is simpler to use than RDS, real examples are often missing, making it challenging to apply API specs to practical use cases.
In an ideal world, there would be automated conf.yaml creation and auto-discovery for Cloud SQL instances. A web interface with point-and-click options would make managing instances a breeze, with advanced options available under a separate tab. It's time for cloud services to catch up with modern technology and make our lives easier.
Situation: I have a set of Cloud SQL instances. I'm adding and deleting them as I make clones and test copies and throw them away. Problem is I have to edit the conf.yaml for Datadog to monitor the new instances and stop monitoring the old instances. Its an annoying waste of time. Computers were meant for automating this kind of work.
Datadog should make automated conf.yaml creation. Actually we should skip dealing with conf.yaml all together. There should be auto discovery. There should be a web interface with point and click to choose options, main options should only be shown with advanced options shown under and advanced tab. Similiarly I find most APIs challenging as practical example are usually sorely missing. Real examples are so much more valuable than the API spec.
Summary:, I find
APIs lacking examples (though GCP is much simpler than RDS though RDS is much more powerful)
JQ opaque and cryptic
conf.yaml antiquated
OK, to automate things
get list of instances for GCP via API
parse out the instance name and the public and private IP ( I connect DATADOG with internal IP because it stays stable, I use public IP in the name so its easy to connect from anywhere quickly if I need to copy the IP and connect to one of the databases)
Generate the the conf.yaml for datadog
( you need to set up gcloud on your host first and get your_project id from the cloud sql dashboard)
PID="your_project"
$ curl -s -H \
"Authorization: Bearer $(gcloud auth print-access-token)" \
"https://sqladmin.googleapis.com/sql/v1beta4/projects/${PID}/instances"
{
"items": [
{
"kind": "sql#instance",
"state": "RUNNABLE",
"databaseVersion": "POSTGRES_13",
"settings": {
"authorizedGaeApplications": [],
"tier": "XXXXX",
"kind": "sql#settings",
"availabilityType": "ZONAL",
"pricingPlan": "PER_USE",
"replicationType": "SYNCHRONOUS",
"activationPolicy": "ALWAYS",
"ipConfiguration": {
"privateNetwork": "projects/XXXXXX/global/networks/default",
"authorizedNetworks": [
{
"value": "0.0.0.0/0",
"name": "change this ",
"kind": "sql#aclEntry"
}
]
...Output is not exactly friendly
It's more friendly in a JSON viewer like https://codebeautify.org/json-navigator-online
You can pull out a list of instances like
$ cat db.json | jq '.items[] |
.name as $name |
.ipAddresses[] |
select(.type == "PRIVATE") |
{name: $name, ipAddress: .ipAddress}'
# results
{
"name": "mydb",
"ipAddress": "10.X.X.X"
}
{
"name": "mydb",
"ipAddress": "10.X.X.X"
}Can pull out both the PRIMARY and PRIVATE
cat /tmp/db.json | jq '.items[] |
{ name: .name,
ipAddresses:
[ .ipAddresses[] |
select(.type == "PRIVATE" or .type == "PRIMARY")|
.ipAddress
] |
select(length > 0)}'Now I can build a conf.yaml for Datadog.
Here is my template
entry=$( cat << EOF
- host: DB_PRIVATE_IP
username: $USER
password: $PW
reported_hostname: DB_NAME-DB_PUBLIC_IP
collect_activity_metrics: true
collect_bloat_metrics: false
dbm: true
dbname: $DB
relations:
- relation_regex: .*
batch_max_concurrent_send: 100
samples:
batch_max_concurrent_send: 100
metrics:
batch_max_concurrent_send: 100
database_monitoring:
metrics:
batch_max_concurrent_send: 100
EOF
) Need the header for the conf.yaml
for i in 1; do
cat << EOF
init_config:
instances:
EOF
done > /tmp/conf.yamlNow put it all together
# set these values
USER=
PW=
DB=
DEBUG=1
# Debug function to print a message
debug() {
if [[ $DEBUG == 1 ]]; then
echo "# $1 (debug) "
# echo -e "$1 "
fi
}
# dummy entry for testing
entry=$( cat << EOF
- host: DB_PRIVATE_IP
reported_hostname: DB_NAME_DB_PUBLIC_IP
EOF
)
entry=$( cat << EOF
- host: DB_PRIVATE_IP
username: $USER
password: $PW
reported_hostname: DB_NAME-DB_PUBLIC_IP
collect_activity_metrics: true
collect_bloat_metrics: false
dbm: true
dbname: $DB
relations:
- relation_regex: .*
batch_max_concurrent_send: 100
samples:
batch_max_concurrent_send: 100
metrics:
batch_max_concurrent_send: 100
database_monitoring:
metrics:
batch_max_concurrent_send: 100
EOF
)
cmd=$( cat << EOF
cat db.json | jq '.items[] |
{name: .name, ipAddresses:
[.ipAddresses[] |
select(.type == "PRIVATE" or .type == "PRIMARY") |
.ipAddress] |
select(length > 0)} |
[.name] + (if .ipAddresses |
length == 1 then [.ipAddresses[0], .ipAddresses[0]]
else .ipAddresses end) | @csv' |\
sed -e 's;\\\\;;g' | sed -e 's/""/"/g'
EOF
)
for i in 1; do
cat << EOF
init_config:
instances:
EOF
done > /tmp/conf.yaml
for mystring in `eval $cmd`; do
debug "----"
debug $mystring
debug " "
export DB_NAME=$(echo $mystring | cut -d',' -f1 | sed -e 's/"//g' )
export DB_PUBLIC_IP=$(echo $mystring | cut -d',' -f2 |sed -e 's/"//g' )
export DB_PRIVATE_IP=$(echo $mystring | cut -d',' -f3| sed -e 's/"//g' )
debug "DB_NAME $DB_NAME"
echo -e "$entry" | \
sed -e "s/DB_NAME/$DB_NAME/" | \
sed -e "s/DB_PUBLIC_IP/$DB_PUBLIC_IP/" | \
sed -e "s/DB_PRIVATE_IP/$DB_PRIVATE_IP/"
debug "----"
debug " "
done >> /tmp/conf.yaml
sudo cp /etc/datadog-agent/conf.d/postgres.d/conf.yaml /etc/datadog-agent/conf.d/postgres.d/conf.yaml.$$
sudo cp /tmp/conf.yaml /etc/datadog-agent/conf.d/postgres.d/conf.yaml
You can check out the yaml with yaml lint
Comments